December 13th, 2012, 11:57 AM
Access Query help please!
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!!
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!
December 13th, 2012, 03:00 PM
Create a query that sums all the relavant hours first. Then use your query based on the first query instead of the tables to give you the total values you want without the duplicate records.
Without seeing your table structure or how your queries are actually set up can't really help you more than that.
December 14th, 2012, 09:10 AM
Hi there. Thanks for your reply.
This is pretty much what I was trying to do, but the problem came while calcualting the expected weekly hours based on the contract.
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!
December 14th, 2012, 09:17 AM
How are you saving the changes in contract hours, are you updating a field everytime with the new hours? or do you create a new record in a table with the new contract date?
December 14th, 2012, 09:37 AM
The changes are saved in the Amendment Table, and a query calculates the hours each time the report is run. These results are not saved to a table; just viewed directly on screen.