Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Dilemma (2000)

    Attached is my stripped down database.

    Here's the scenario:

    I am trying to create a report in a database that keeps our part-time employee's hours. This database has 2 main components: (1) a table to keep the actual number of hours worked [ACTUAL] and (2) a table to keep the projected number of hours to work [REQ_HISTORY]. This report is trying to reflect the number of actual hours worked compared to the number of hours projected, categorized by a specific code.

    Employees are also assigned a CODE: 6020 = Student; 6040 = Work Study. Once a student uses up all of their work study money, they get re-requisitioned to the student code, even if their hourly rate has not changed. In addition, if the employee gets a raise they are also re-requisitioned. All of these factors lead to an additional item in the REQ_HISTORY table. The employee can have more than one item in the ACTUAL table only if their CODE has changed.

    The problem that I'm running into is for those employees who get a raise (and thus get a new requisition) but do not change CODES. When I set up my query (qryTest), they are not adding up correctly. It double counts ID_NO = 1 in the ACTUAL table. The query should read:

    CODE HOURS REQ_AMT
    6020 80 2718
    6040 10 2700

    Any help is appreciated!
    Thanks,
    Kindra
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Dilemma (2000)

    Oh! I'm sorry -- I did not mean to post that other thread. I will delete it. I hope that this post clarifies what I'm trying to do a little bit better....

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Dilemma (2000)

    You are trying to do too much in one query. Instead, split it up into three queries:

    1) A totals query based on ACTUAL only, that groups by CODE and sums HOURS. Le's name this qrySumHours.
    2) A totals query based on REQ_HISTORY only, that groups by code and calculates REQ_AMT. Let's name this qryReqAmt.
    3) A query based on qrySumHours and qryReqAmt, joined on CODE, that returns CODE from one of the two, and the sum field from each of the queries.

    If you have CODE values occurring in only one of the tables, it becomes slightly more complicated, but not by much. I have attached a version that takes that into account, using a union query as intermediary.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Dilemma (2000)

    Thank you Hans!!!

    Works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •