Caclulating Count of unique values

  • Thread starter Thread starter Roy Goldhammer
  • Start date Start date
R

Roy Goldhammer

Hello there

I have order table

In the order table i have clitntID and OrderID

Each client have more then one order

On the query i need to get total money of all orders and total clients.

when i do it only with Orders table and Count function i get all orders and
not all clients

is there a way to get only clients?
 
1. Create a query into both tables.

2. In the upper pane of the query design window, double-click the line
joining the 2 tables. Access gives you a dialog with 3 options. Choose the
one that says:
All clients, and any matching Orders.
Technically this is called an "outer join". More info:
http://members.iinet.net.au/~allenbrowne/casu-02.html

3. Depress the Totals button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

4. Drag ClientID from the Client table into the grid.
In the Total row, accept Group By under this field.

5. Drag OrderID from the Order table into the grid.
In the Total row, choose Count.

6. Drag the Amount field from the Order table into the grid.
In the Total row, choose Sum.

The query lists all clients, with the count of orders, and the sum of
amounts.

Note that if you add criteria to the query (such as limiting it to orders in
a date range), that may have the effect of listing only clients who had
orders in that date range. The article cited above explains that also.
 
Back
Top