Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Totals query (Access 2000)

    I have made a totals query based on the following tables.

    Employee table:
    [Employee #]
    [Name]
    [Max hours]

    Job table:
    [Job #]
    [Employee #]
    [Task #]
    [Date]
    [Rate] = Standard/Overtime

    Task table:
    [Task #]
    [Hours]

    The query produces results like the following, using summed hour fields and a calculated Difference = Max - Standard - Overtime.

    [Name] [Max Hours] [Standard Hours] [Overtime Hours] [Difference]
    Tom | 40 | 20 | 10 | 10 |
    Harry | 30 | 10 | 15 | 5 |

    1. How can I make the query include all employees, even those with no records in the Job table, as follows?

    [Name] [Max] [Standard] [Overtime] [Difference]
    Tom | 40 | 20 | 10 | 10 |
    Dick | 30 | - | - | 30 |
    Harry | 30 | 10 | 15 | 5 |

    I tried left joining the Employee table to the Job table on the [Employee #] field and inner joining the Job table to the Task table on the [Task #] field, but this produced an "ambiguous join" error.

    2. How can I add fields to show just the number of jobs of each type (Standard/Overtime)? I tried copying the summed fields and replacing Sum with Count, but the results weren't correct.

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

    Re: Totals query (Access 2000)

    1. Left join Employee table to Job table on Employee #, and left join Job table to Task table on Task #.

    2. Add calculated columns:

    NumberOfStandard: Abs(Sum([Rate]="Standard"))

    NumberOfOvertime: Abs(Sum([Rate]="Overtime"))

    both with the Total option set to Expression.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Totals query (Access 2000)

    1. I had tried using two left joins on those fields, but the result omitted employees who had no records in the job table, whereas I need to list all employees, even those who have no records in the job table.

    2. Thanks, however my example (which is a simplification of the actual application) may have been misleading here. The [Rate] field actually contains a value, such that Null = Standard, and Not-Null = Overtime. I tried modifying your suggestion as follows:

    CountStandard: Abs(Sum(IsNull([Rate])))
    CountOvertime: Abs(Sum(Not IsNull([Rate])))

    but the first field caused a parameter prompt for the field although if I left it blank it did list some values but they were incorrect, and the second field just listed all zeros.

    3. FYI, the sum fields are as follows, using your suggestions from an earlier post.

    SumStandard: -([Hours]*(IsNull([Rate])))
    SumOvertime: [Hours]

    Although the total options for these fields are both set to Sum, sometimes the option on the first field changes by itself from Sum to Expression and the field changes to Sum(-([Hours]*(IsNull([Rate])))). Are these forms equivalent; i.e., is this just a glitch in Access?

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

    Re: Totals query (Access 2000)

    1 & 2. This is all becoming too complicated to help with without seeing the database, but you stated in another thread that you couldn't post a stripped down copy.
    3. This is normal behavior, Access stores the SQL statement for the query and there are different ways of displaying this in the query design window. These forms are equivalent.

Posting Permissions

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