Re: Caclulating Count of unique values

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 15:43:14 +0800

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. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Roy Goldhammer" <roygoldh@hotmail.com> wrote in message
news:%23ixspD04EHA.3708@TK2MSFTNGP14.phx.gbl...
> 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?