Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    qyery totals and counts (2000)

    Hi, I have a table with a list of here agents who make contacts with customers, those contact could be one of three types, say 1, 2 and 3. I need a query to total up the number of query types each agent has done. so agent 1, contacy type 1 = 5, contact type 2 = 4, contact 3 = 6. I am having trouble modelling this.

    Thanks Darren.

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

    Re: qyery totals and counts (2000)

    Create a query based on the table.
    Add the agent and contact type fields to the query grid, then add the contact type field a second time.
    Select View | Totals or click the Totals button on the toolbar.
    Set the Total option for the last column to Count. Leave it at the default Group By for the first two columns.
    This query will list the number of contacts per agent for each type below each other.

    You can also list the three counts next to each other:
    Select Query | Crosstab Query.
    Set the Crosstab option for the three columns to Row Header, Column Header and Value, respectively.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: qyery totals and counts (2000)

    Thanks Hans, saves me messing with formula in the report, one small extra that I ommited, I have a date field because we will be ordering this detail within a date range. How can I get the values returned just to show the total within the range for each agent rather than agent 1, day 1 x=2, y =4, day 2 agent 1 x =8, y =6.

    Thanks again.

    Darren.

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

    Re: qyery totals and counts (2000)

    You can insert the date field as a column in the query between the agent column and the contact type column.
    If you want only results in a certain date range, put it in the Criteria row for the new column, e.g.

    Between #01/01/2007# And #01/04/2007#

    or

    Between [Forms]![frmSelect]![txtStart] And [Forms]![frmSelect]![txtEnd]

    Leave the Total option as Group By, and if you created a crosstab query, set the Crosstab option to Row Header.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: qyery totals and counts (2000)

    Thanks Hans

Posting Permissions

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