Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Ephrata, Washington, USA
    Thanked 0 Times in 0 Posts

    query totals (Access 2003)

    I am not very good with creating calculations in queries. I need to know how to create a calcuation in a query to tell me how many visitors for each month/year. I can figure out how to subtotal by date, but I don't want each date. Eventually, I want to create a query or report that will show each month how many visitors we had on the tours.

    Visitors TourDate
    7 5/1/2004
    5 5/5/2004
    6 6/1/2004
    7 6/1/2004
    10 6/5/2004
    8 6/9/2004
    9 6/9/2004
    16 1/1/2005

    How can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: query totals (Access 2003)

    If you want to create a report based on these data, you can let Access do most of the work for you. If you create a report using the Report Wizard, it will ask you if you want to group the data. If you specify the TourDate as field to group on, you can specify grouping it by day, week, month, quarter or year. You can also specifiy totaling options for the Visitors field.

    If you do need a query, add two simple calculated columns to the query grid:

    Y: Year([TourDate])
    M: Month([TourDate])

    plus the Visitors field. Next, select View | Totals, or click the Totals button on the toolbar (the Greek letter Sigma). Set the Total option for Visitors to Sum; the Totals option for Y and M can remain the default Group By. This query will total the number of visitors per month.

Posting Permissions

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