#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Access Query help please!


    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!!

    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.
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    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.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    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!



    Thank you.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    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?
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    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.

Similar Threads

  1. ASP Query Access Query Different results
    By jj72uk in forum ASP Development
    Replies: 4
    Last Post: May 25th, 2011, 10:37 AM
  2. Access Query: Can you Query by a Date Range?
    By the_master68 in forum Microsoft Access Help
    Replies: 2
    Last Post: April 8th, 2011, 11:43 AM
  3. Converting Access to Access Project Query problem
    By mjliscio in forum Microsoft Access Help
    Replies: 2
    Last Post: September 20th, 2007, 01:28 PM
  4. Replies: 7
    Last Post: January 12th, 2007, 04:17 PM
  5. Query Access with multiple Query??
    By DBob in forum ASP Development
    Replies: 8
    Last Post: April 28th, 2006, 04:41 AM

IMN logo majestic logo threadwatch logo seochat tools logo