News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Need help creating a query

    Posted on WSCWaldman Comment on the AskWoody Lounge
    Viewing 1 reply thread
    • Author
      Posts
      • #351493 Reply
        WSCWaldman
        AskWoody Lounger

        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

      • #508676 Reply
        WScpod
        AskWoody Lounger

        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.

    Viewing 1 reply thread

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Need help creating a query

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.