- Joined
- Dec 29, 2003
- Messages
- 1,663
- Reaction score
- 13
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_Staff
NI Number - Primary Key
Forename
Surname
Bank
BA Number
Sort Code
IBAN
BIC
FSKS - Composite Key
tbl_Staff/Tip
StaffID - Primary Key
TipID - Composite Key
Amount
tbl_Tips
MonthYear - Primary Key
Amount
Expected
Day
The 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.
Basically my database has three tables:
tbl_Staff
NI Number - Primary Key
Forename
Surname
Bank
BA Number
Sort Code
IBAN
BIC
FSKS - Composite Key
tbl_Staff/Tip
StaffID - Primary Key
TipID - Composite Key
Amount
tbl_Tips
MonthYear - Primary Key
Amount
Expected
Day
The 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.

