Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2000
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help creating a query

    Lets say I have a table (Table1) with OrderID's and CustomerID's and another field (Type) which can be either S or H. I want to first limit the CustomerID's to type H, then I want to find the total number of CustomerID's per OrderID... How can I do this?

    Note that the table can have the same CustomerID listed more than once for the same OrderID (I don't want to get into the full structure of the table to explain why but it is necessary). This means that both OrderID and CustomerID needs to be grouped.... Also note that if a CustomerID is listed more than once for the same OrderID, I only want it counted once.

    So for example. I may have in my table (assume I already limited to Customers of Type H)
    OrderID CustomerID
    1 50
    1 52
    1 53
    1 50
    2 50
    2 60
    2 60
    2 60

    What I want my query results to look like is:
    OrderID Total
    1 3
    2 2

    How can I do this?

    Appreciate the help.

    Chad

  2. #2
    DanK
    Guest

    There is a way...

    I think I did something like this once. You have to play with the "Unique values" and "Unique records" properties of a query using just those two fields you mentioned and I remember having to create one query that was used in another which calculated the record count property to finally get what I wanted. It took me a while but I got what I wanted. I think you're going to need to use a nested query as well.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help creating a query

    Create one query with CustomerId, OrderId and Type fields, limited to type H. Go to the properties list for the query and set "Unique Values" to yes. Now, create another query on the last query. This will be a totals query. Add the OrdersId field twice. Leave one at "group By" and change the other to "Count". Run query.

  4. #4
    New Lounger
    Join Date
    Dec 2000
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help creating a query

    Thanks. I got it working.

    Chad

Posting Permissions

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