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

    Totalling and tabulating (Access 2000)

    (1) I have a table with data in the following format.

    [Employee][Date Available]
    Tom, July 5
    Tom, July 6
    Tom, July 7
    Dick, July 3
    Dick, July 4
    Dick, July 5
    Harry, July 1
    Harry, July 2
    Harry, July 3

    I want to produce a query for a range of dates, and from it a form and report (I can get that far!), with one line for each person and columns with indicators for each date, as follows.

    Employee Availabilities 1- 7 July
    [Name][1][2][3][4][5][6][7]
    Tom | - | - | - | - | Y | Y | Y |
    Dick | - | - | Y | Y | Y | - | - |
    Harry | Y | Y | Y | - | - | - | - |

    What is the general technique for producing a result like this?


    (2) I have another table with data in the following format.

    [Employee][Date][Hours Worked]
    Tom, 5 July, 6 hours
    Tom, 7 July, 8 hours
    Dick, 3 July, 5 hours
    Dick, 4 July, 3 hours
    Dick, 5 July, 4 hours
    Harry, 1 July, 6 hours
    Harry, 2 July, 5 hours

    I want to produce another query for a range of dates, and from it a form and report, with one line for each person and columns with totals of the quantities for each, as follows.

    Employee Hours Worked 1- 7 July
    [Name][Hours Worked]
    Tom 14 hours
    Dick 12 hours
    Harry 11 hours

    What is the general technique for totalling a series of records like this?


    (3) Finally, I want to combine these results from both tables if possible, to produce a result as follows. Note that each line contains only accumulated totals, and that there is no correlation between the dates on the records in the two tables except that they are all within the same specified range of dates.

    Employee Availabilities and Hours Worked 1-7 July
    [Name][1][2][3][4][5][6][7][Quantity]
    Tom | - | - | - | - | Y | Y | Y |14 hours
    Dick | - | - | Y | Y | Y | - | - |12 hours
    Harry | Y | Y | Y | - | - | - | - |11 hours

    Would this require advanced programming, or could Access do this fairly easily? If my query goes beyond the scope of this forum, are there recommended websites with more detailed information on this topic?

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

    Re: Totalling and tabulating (Access 2000)

    1) This is a crosstab query. The easiest way to create one is to click New in the Queries section of the Database window, and select Crosstab Query Wizard.
    In the first step, select the availability table, then click Next.
    In the second step, specify the employee field for the row headings, then click Next.
    In the third step, specify the date field for the column headings, then click Next.
    In the fourth step, specify any field for the value, and Count as function, then click Next.
    In the last step, click Finish.
    You can add criteria on the date field later on.

    2) This is a Totals query.
    Create a query based on the hours worked table.
    Add the employee field and the hours worked field.
    Select View | Totals.
    Set the Total option for the hours worked field to Sum (for the employee field, it will remain the default Group By)
    You can add date criteria.

    3) Create a new query based on the previous two, joined on employee. Add fields from both (but the employee from only one of them)

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

    Re: Totalling and tabulating (Access 2000)

    That sounds like it shouldn't be too tricky. Thanks once again, Hans. One small additional query: I could use manually entered date parameters, but if I always wanted the query to cover a standard period, such as from 7 days ago to today, how would I enter this as a fixed criterion?

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

    Re: Totalling and tabulating (Access 2000)

    To include a periode of 7 days from today:

    Between Date() And Date()+7

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

    Re: Totalling and tabulating (Access 2000)

    It sounds like what I want is Between Date () -7 And Date (), then. Thanks again, Hans.

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

    Re: Totalling and tabulating (Access 2000)

    That''s correct - sorry, I was in a hurry and didn't read your question carefully.

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

    Re: Totalling and tabulating (Access 2000)

    With the totals query example above, how can I add a second Hours Worked column, so that the first one totals all hours worked, and the second one totals only hours where a certain criterion applies (e.g., Overtime field = Y)?

    Table:

    [Employee][Date][Hours Worked][Overtime]
    Tom, 5 July, 6 hours, Y
    Tom, 7 July, 8 hours, N
    Dick, 3 July, 5 hours, N
    Dick, 4 July, 3 hours, Y
    Dick, 5 July, 4 hours, Y
    Harry, 1 July, 6 hours, N
    Harry, 2 July, 5 hours, N


    Result:

    Employee Hours Worked 1- 7 July
    [Name][Hours Worked][Overtime Hours Worked]
    Tom, 14 hours, 6 hours
    Dick, 12 hours, 7 hours
    Harry, 11 hours, 0 hours

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

    Re: Totalling and tabulating (Access 2000)

    Add a new column:

    Overtime Hours: -[Hours Worked]*([Overtime]="Y")

    with the Total option set to Sum. This calculation use the fact that ([Overtime]="Y") evaluates to either True = -1 or False = 0. The minus in front of the expression turns the -1's into +1's.

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

    Re: Totalling and tabulating (Access 2000)

    Thanks for this. I used Y/N as a criterion in the example for simplicity, but in fact the criterion will need to be whether the Overtime field is not blank. Should I therefore modify the expression to something like Overtime Hours: [Hours Worked]*([Overtime]<>"")?

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

    Re: Totalling and tabulating (Access 2000)

    Overtime Hours: -[Hours Worked]*(Not IsNull([Overtime]))

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

    Re: Totalling and tabulating (Access 2000)

    I have now created a calculated field, Difference: [Hours Worked] - [Overtime Hours Worked], but with its Total parameter set to the default "Group By", it ungroups the main grouping by employee, and no other setting seems to produce the desired result. Do I need a special setting for a calculated field in a totals query?

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

    Re: Totalling and tabulating (Access 2000)

    Don't you want to sum this expression?

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

    Re: Totalling and tabulating (Access 2000)

    Does Expression work? If not, we'll need to know more details.

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

    Re: Totalling and tabulating (Access 2000)

    If I set it to Sum, I get the error: "Subqueries cannot be used in the expression ..."

    I may have provided insufficient information in my examples, which I have made up for illustration. It may be significant that the employees and hours worked are in different tables.

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

    Re: Totalling and tabulating (Access 2000)

    As I was checking the design while drafting a reply to your last post, I managed to solve the problem, and it did involve setting this field to Expression, thanks.

Posting Permissions

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