Extracting one out of multiple query advise

[Bob Quintal]

As an example, if I have the following records

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

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

Thanks

Regards

create a totals query which returns only three fields: Company name,
post code and min(date created)

You then join this query to your table in a second query.using inner
joins on all three fields. It will filter the results.

 

[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
filed. Is there a way to extract from each group of duplicated clients the
one client that has the earliest data created?

Thanks

Regards

 

[John]

As an example, if I have the following records

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

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

Thanks

Regards

 

[John]

Thanks. A few duplicate clients have the same date created value causing
problem.

like

CompanyA PostcodeA 01/01/07 ID12
CompanyA PostcodeA 01/01/07 ID23

Regards

 

[Guest]

Create an empty table with identical fields, making company name the primary
key ( excluding duplicates ).

Then make an append query, sorted by date ascending, and append to that table.

 

[Bob Quintal]

Thanks. A few duplicate clients have the same date created value
causing problem.

like

CompanyA PostcodeA 01/01/07 ID12
CompanyA PostcodeA 01/01/07 ID23

Regards

And how do you propose to determine which is the earliest?
Maybe you want to look at the smallest ID number instead of the
earliest date.

create a totals query which returns only three fields: Company
name, post code and min(date created)

You then join this query to your table in a second query.using
inner joins on all three fields. It will filter the results.