Smo™ Posted February 10, 2006 Report Share Posted February 10, 2006 Hey guys, I'm in a bit of a bind here and I need some help. For those of you who know lots about Microsoft Access - there are a few queries I need some advice on.Basically my database has three tables:tbl_StaffNI Number - Primary KeyForenameSurnameBankBA NumberSort CodeIBANBICFSKS - Composite Keytbl_Staff/TipStaffID - Primary KeyTipID - Composite KeyAmounttbl_TipsMonthYear - Primary KeyAmountExpectedDayThe Relationships are as follows:What I'm trying to do is make three queries. One called 'qry_AboveExpected' which checks the value in the Amount field in the 'tbl_Tips' table, and then sees if it's above or equal to the corresponding amount in the Expected field. So for example;The query will only return the months when the amount in tips received was more than expected.The second query called 'qry_BelowExpected' would, quite obviously, do the opposite and only show the months when the tips received were less than expected.Lastly, I need another query ('qry_SetAside')which takes 20% of the number in the Amount field in the 'tbl_Tips' table and divides that 20% by 4.The problem with these I'm having is the Syntax - I just can't figure out what to put in order to get the results I want. For the first two I was thinking something like >="Expected" for the first query but it doesn't work. Any help is greatly appreciated, although I don't really expect much.Cheers guys. Quote Link to comment Share on other sites More sharing options...
Extreme_biker0 Posted February 10, 2006 Report Share Posted February 10, 2006 Have you figured out the answer using google yet? If not post back and I'll answer. Quote Link to comment Share on other sites More sharing options...
Smo™ Posted February 10, 2006 Author Report Share Posted February 10, 2006 I've tried mate but I couldn't find anything specific enough to help me. Quote Link to comment Share on other sites More sharing options...
pinky Posted February 10, 2006 Report Share Posted February 10, 2006 I can ask our database expert at work he knows his stuff Quote Link to comment Share on other sites More sharing options...
Simon Posted February 10, 2006 Report Share Posted February 10, 2006 I could tell you if it was MySQL. MSSQL is shit though. You sure it's not just something like (this is going of MySQL syntax, but i'm sure you get the picture)"WHERE tbl_Staff/Tips.amount >= tbl_Tips.expected"As for the % one, can you not do maths in a query?"SELECT tbl_tips.*, amount/100*20/4 AS setAside"That's how it'd be done in MySQL anyway, a very basic way anyway. Quote Link to comment Share on other sites More sharing options...
Smo™ Posted February 10, 2006 Author Report Share Posted February 10, 2006 Thanks for the help guys, appreciate it. However, I've had a bit to drink and I'm not feeling too hot so I won't be thinking about Access until tomorrow at least Quote Link to comment Share on other sites More sharing options...
nb88 Posted February 12, 2006 Report Share Posted February 12, 2006 Are you doing A2 ICT coursework? If so im doing that atm aswell....... boooring Quote Link to comment Share on other sites More sharing options...
Smo™ Posted February 12, 2006 Author Report Share Posted February 12, 2006 Are you doing A2 ICT coursework? If so im doing that atm aswell....... boooring Yeah and it's killing me. It's so time consuming - this is the only difficult part I've come across. The thing that does my nut in is repeating myself all the time. In Part B when you draw the form, switchboard and table designs, then screenshot the forms, switchboards and table designs then in Part C you screenshot and annotate the forms, switchboards and table designs, all in normal and design view. Oh my god it hurts to think about it.Just wish I could figure out these damn queries so I can move on and get it finished. Quote Link to comment Share on other sites More sharing options...
nb88 Posted February 12, 2006 Report Share Posted February 12, 2006 Yeah and it's killing me. It's so time consuming - this is the only difficult part I've come across. The thing that does my nut in is repeating myself all the time. In Part B when you draw the form, switchboard and table designs, then screenshot the forms, switchboards and table designs then in Part C you screenshot and annotate the forms, switchboards and table designs, all in normal and design view. Oh my god it hurts to think about it.Just wish I could figure out these damn queries so I can move on and get it finished.Thats exactly what i hate about it, access is so easy except for the odd problem, its just writing exactly what you did over and over again...mind numbingly boring stuff. Mines taking ages as i have like 6 tables....blargh, i better do some today actually. Whens yours due in? Quote Link to comment Share on other sites More sharing options...
Smo™ Posted February 12, 2006 Author Report Share Posted February 12, 2006 Thats exactly what i hate about it, access is so easy except for the odd problem, its just writing exactly what you did over and over again...mind numbingly boring stuff. Mines taking ages as i have like 6 tables....blargh, i better do some today actually. Whens yours due in?In all honesty, Part C was due in a couple of weeks ago but with the exams and death of a friend I've fallen behind - had a lot on my plate recently. I believe the whole project is due in mid March. Quote Link to comment Share on other sites More sharing options...
Smo™ Posted February 20, 2006 Author Report Share Posted February 20, 2006 Sorry for the double post, but I could really do with some help now please guys. I'm f**kered. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.