Access Query Help please!

[UK_Lionheart]

Hi all,
I am going mad with a Query problem in Access. I have a small database which tracks staff attendance against their contract. This has a main table with the staff details (key field StaffCode) which is linked to monthly tables where they can enter their start and end times and relevant jobs to assign hours against. The link is through StaffCode, so they can only add and amend their own hours.
I also have an amendment table (again, linked with StaffCode) which is for changes to their contracted hours.
Using a basic If Then, I was able to pull off a report which calculated their hours against the contract, by checkign any amedments and chaging the Expected Hours for the relevent weeks.
This was fine until, for reasons known only to the management, they changed somebodys contract twice on consecutive weeks. Now I get a multiple line on my report, which double counts the expected hours, showing this staff member as underperforming. If however, I suppress the second weeks, it gives everybody else a zero hours! Aarghhh!! :chomp:
This is my query:
IIF ([date] between [start date] and [end date],[weekly hours]+[amendment],[weekly hours]) which double counts hours as there is a change on 8th Oct and 15th Oct, so I see:
Date Start Date Expr
01/10 08/10 10
01/10 15/10 10
08/10 08/10 15
08/10 15/10 10
Which is no good!
I have tried to replace the "else" part of the statement with another IIF to make it set to zero, but if I do manage to resolve the issue with this recordset, I get zeros in all other staff members.
Any advice would be appreciated as I am tearing my hair out!
Thanks.

 

[plog]

which is linked to monthly tables

That's a scary statement. Tell me you do not have a table for August, a table for September, etc.

As for your issue, can you provide sample data from all the relevant tables along with field names? Format it like this:

TableNameHere
field1name, field2name, field3name
1/1/2012, John, 5
2/3/2012, Jim, 7

Then show what you expect this query to return base on the sample data.

 

[UK_Lionheart]

Hi Plog. Thanks for your reply.

Sorry to give you nightmares - Yes I do have a table for each month - Is this no good? I am all self taught on Access, and so a lot of the time I am a bit naive about the relationship side and pretty much think what I need and put a table in for each thing! :-(

So the monthly tables are in the format:

StaffCode Date StartTime EndTime Hours (This is a calculated field)
2 01/10/12 09:00 13:45 4.75

The Date is then added into a calculation which works out which week it is in and the expected and actual hours worked for that week.

The Amendment Table is in this format

StaffCode Hours StartDate EndDate
2 -15 01/09/12 30/11/12

So in this example, Staff Member 2 will have his contracted hours cut by 15 per week between 1st September and 30th November. This was working fine untl this happened:

StaffCode Hours StartDate EndDate
2 -15 01/09/12 07/09/12
2 -10 08/09/12 30/11/12

So the original example, the query would only pick up the change in hours if the date was between the amendment dates, and if not, use the standard contracted hours assigned to the staff member. In this case, full time - 40 hours.

In the second one, I need to separate out the weeks to give me contracted hours of

<01/09 = 40
01/09 - 07/09 = 25
08/09 - 30/11 = 30
>30/11 = 40

which I can then use as a base to calculate whether they have worked more or not.

Hope that helps!

Thank you.

 

[plog]

No that didn't help. I just wanted data that was going in and data coming out. You gave a hard to follow explanation of how to get there but you never posted what you wanted the result of your query to be.

So let's jump back to your structure issues.

1. You should not have a table for every month, you should have one table. You are already storing a date value in there so you will be able to determine what month each row is for.

2. 'Date' is a bad field name, it is a reserved word in Access and can cause issues in code and queries.

3. You shouldn't store calculated values (Hours), you should calculate them when you need them.

4. Instead of 3 fields of Date and Time ('Date', 'StartTime', 'EndTime') you should probably only have 2 fields ('StartTime', 'EndTime') which are full Date/Time fields (i.e. '1/10/12 09:00:00 AM). This will make calculating 'Hours' simpler, especially if someone works past midnight.