Hi,

I'm using ms access2003 (no sql backend). I have a table with 1000's of
orders for difference clients (Example Below). I need to do a query that
returns just the first order for each client so I can get the 'First Order
Date' I have tried everything I can think of which wasnt a lot in this case
so was hoping for some assisctance.

If only possible with SQL I could import into SQLServer.
Many thanks for your help.

Example
ClientNo ClientName OrderNo DateOfOrder
012423 ClientOne 29092 24/02/03
039485 ClientTwo 30459 25/02/03
012423 ClientOne 29349 20/05/03
039485 ClientTwo 30959 25/06/03
012423 ClientOne 29493 11/12/03
039485 ClientTwo 30559 25/12/03
012423 ClientOne 29094 24/03/04

I wish to return
ClientNo ClientName OrderNo DateOfOrder
012423 ClientOne 29092 24/02/03
039485 ClientTwo 30459 25/02/03

Hi Tim:

This may seem arcane, but so would the SQL.

Create queryA.

Use the table, but only 2 fields: ClientNo and DateofOrder
Click the sumation symbol (greek sigma).
Under ClientNo choose GroupBy
Under DateofOrder choose Min.

If you run this you will get the 2 records, but not all the fields.

Now create queryB
QueryB uses queryA and the table. Join QueryA and QueryB on both the
ClientNo and Dateoforder.
Output all the fields you want from the table.

This will give you the result you want.

I have done Access since version 2 and I try to avoid SQL. I consider
it ugly. In fact, when I need SQL I write an Access query and then
copy the generated sql.

Good Luck
Ira Solomon

On Sun, 29 May 2005 23:58:54 +0100, "Tim Allen"
<timallen-no-spam (AT) nospampleasebluecap (DOT) co.uk> wrote:

Wow, I cant believe it was that easy!

I can use Max to get the last order date too! I didt know what min/max
actually did so did try them.

Ira, thank you so much for your time.


"Ira Solomon" <isolomon (AT) solomonltd (DOT) com> wrote