Best approach when 2 lists contain the same record...but you need one

[colezpapa]

I have 2 files that can contain the same client_id but have different statuses. They will come from different business areas.

List 1 List 2
------ ------
ABC 1 JKL 1
DEF G MNO 2
EFG X HIJ 2
HIJ 1 NOP X

In the scenario client HIJ comes in from 2 data sources but only 1 value gets uploaded. Since the HIJ with the 1 is the highest rated status, that record gets written to the upload file.

What is the best approach to use here. I want only 1 client record.

 

[dhookom]

First create a union query like:
[tt][blue]
== quniClientStati =====
SELECT ClientID, Status
FROM [List 1]
UNION ALL
SELECT ClientID, Status
FROM [List 2];
=========================
[/blue][/tt]

Then create a totals query from your union query:
[tt][blue]
== qgrpClientStatus =====
SELECT ClientID, Min(Status) as HighestStatus
FROM quniClientStati
GROUP BY ClientID;
=========================
[/blue][/tt]

This is a big assumption that I know what status value are highest which I don't.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[colezpapa]

Thanks Duane...

Thats the way I thought I would do it, but I wanted to validate it from somebody here.

Thank you again.