Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count the number of occurences (Access 2003)

    I am constructing a database that among other things tracks appointments made with our office.

    I need to create a report that says how many clients had more than two appointments in any given month but am unsure how to create a query upon which i can base the report.

    Any suggestions would be appreciated.

    Kat

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

    Re: Count the number of occurences (Access 2003)

    It depends on the tables you have, of course. I assume that you have an appointments table, with a field that identifies the client (I'll call it ClientID here), and some info about an appointment, such as the date (I'll call it AppointmentDate).

    Start by creating a query based on this table. Add the following to the query grid:

    TheYear: Year([AppointmentDate])
    TheMonth: Month([AppointmentDate]
    ClientID
    AppointmentCount: ClientID

    Select View | Totals (or click the Totals button on the toolbar).
    Change the Total option for the last column to Count (the others remain the default Group By)
    Enter >2 in the Criteria row in the last column.
    Switch to datasheet view. You'll see all clients, grouped by year and month, who had more than two appointments in a month.
    Save this query as, say, qryAppointmentCount.

    Next, create a new query based on qryAppoinmentCount.
    Add TheYear, TheMonth and ClientID to the query grid.
    Select View | Totals.
    Set the Total option for the last column to Count.
    Switch to datasheet view. You will now see the number of clients who had more than two appointments in a month.
    Save this query as, say, qryClientCount.

    Use qryClientCount as the record source for a report.

  3. #3
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count the number of occurences (Access 2003)

    That works really well, but is there a way that i could have it display the results only for one month by constructing it as a parameter query? I do not want users to have to directly enter a value into the query, if that can be helped.

    I have made this work in other tables by using "Between...And" criteria with the DateofSession.

    Kat

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

    Re: Count the number of occurences (Access 2003)

    One possibility is to put parameters on the TheYear and TheMonth fields in the first query: type [Enter Year] in the criteria line for TheYear and [Enter Month] in the criteria line for TheMonth. You can set the Total option for these columns to Where.

    Another option is to use a form where the user can specify the year and month, in text boxes, or more user-friendly in combo boxes. The criteria would then look like this (substitute the appropriate names):

    [Forms]![NameOfTheForm]![NameOfTheComboBox]

    In both cases you should remove TheYear and TheMonth from the second query.

    Yet another option is to remove TheYear and TheMonth from both queries, and to add the DateOfSession to the first query, set the Total option to Where, and put the following in the criteria line:

    Between [Start Date] And [End Date]

    or if you use a form frmSelectDate with text boxes txtStartDate and txtEndDate:

    Between [Forms]![frmSelectDate]![txtStartDate] And [Forms]![frmSelectDate]![txtEndDate]

Posting Permissions

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