How to append same account that can have 2 different statuses

[colezpapa]

We have a nightly process that creates a text file for a mainframe job. It will be changing as there will be 2 append queries that load records into a table. The data elements are account code and status. The issue is this, each query can contain the same account and different status. If the same account appears with different status' how can I filter this in the append query. Currently the table is set to no dupes. What is the cleanest way to handle this. I don't want the lower status record overwriting the higher status record.
Thanks

 

[lespaul]

do you have a way to distinguish a "lower status" from a "higher status"?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[colezpapa]

Hi thanks for getting back,
Yes, there will be a value like a "1" or "A", or "B" where "1" is the highest status. Essentially its an approved status. So if its approved, we don't want an "in progress" status overwriting it. The other value is coming from a different area of the company...

 

[colezpapa]

I will have a table that can hold multiple values of the same account.

Account Status
T W
G 1
T 1

In this case, the "T" record with the 1 (highest status) is the record I want. How can I write filtering code in a query?