Extracting one out of multiple query advise

[John]

Hi

I have a table of clients and all clients are duplicated (by company name &
postcode) once or more in the table. Each client record has a date created
field. Is there a way to extract from each group of duplicated clients the
one client that has the earliest data created?

As an example, if I have the following records

CompanyA PostcodeA 01/01/07 ID1
CompanyA PostcodeA 01/02/07 ID2
CompanyA PostcodeA 02/02/07 ID15

CompanyB PostcodeB 01/03/07 ID8
CompanyB PostcodeB 01/04/07 ID11

then I would like the query to output the following;

CompanyA PostcodeA 01/01/07 ID1
CompanyB PostcodeB 01/03/07 ID8

Many Thanks

Regards

 

[Marshall Barton]

I have a table of clients and all clients are duplicated (by company name &
postcode) once or more in the table. Each client record has a date created
field. Is there a way to extract from each group of duplicated clients the
one client that has the earliest data created?

As an example, if I have the following records

CompanyA PostcodeA 01/01/07 ID1
CompanyA PostcodeA 01/02/07 ID2
CompanyA PostcodeA 02/02/07 ID15

CompanyB PostcodeB 01/03/07 ID8
CompanyB PostcodeB 01/04/07 ID11

then I would like the query to output the following;

CompanyA PostcodeA 01/01/07 ID1
CompanyB PostcodeB 01/03/07 ID8

SELECT T.company, T.postcodem, T.datecreated, T.idx
FROM clients As T
WHERE T.company = (SELECT Min(X.datecreated)
FROM clients As X
WHERE X.company = T.company
And X.postcode = T.postcode)

 

[John Spencer]

Whoops.
I believe Marshall meant to use T.DateCreated in the Where clause of the
main query instead of T.Company.

SELECT T.company, T.postcodem, T.datecreated, T.idx
FROM clients As T
WHERE T.DateCreated= (SELECT Min(X.datecreated)
FROM clients As X
WHERE X.company = T.company
And X.postcode = T.postcode)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

Whoops indeed.

Good catch John. Thanks.