Duplicate query with a twist

[coach32]

I have a table that I need to eliminate certain duplicates from. An example of the data is:
Account - Team
14983 - 1743
14983 - 38466
15621 - 38466
15000 - 38466
19816 - 1743
15621 - 38466
My problem is that I need to remove duplicates but in the same instance I need to keep a certain Team number. In the above sample data I have the account number 14983 that has 2 different team numbers. I want to keep the 1743 and also with the account number 15621 I just need to keep one of the 38466 records. Is there anyway I can achieve this? Any assistance would be greatly appreciated. Thank you in advance.

[June7]

The data is in two separate fields? Removing the second instance of 15621 - 38466 would be easy. Would need a unique ID field in the table, autonumber field would serve. Check this http://stackoverflow.com/questions/1...duplicate-rows

I don't understand your other requirement. What is the criteria for keeping 1743 and not 38466 associated with Account 14983? What if another Account has these two Team codes?

[coach32]

When I receive a report with 1743 & 38466 associated to the one account number, apparently the 38466 is just a type of back up to 1743 and therefore I don't need to count it (38466). There are other account numbers that have the two different Team codes. That's my quandary. I need to eliminate the 38466 when it is paired with a 1743.

[June7]

After purging the duplicates, try this to exclude the extraneous 38466 records. Are there other Team codes? If so, will be more complicated.

Query to see if team 1743 in table:
SELECT ID, Account, Team FROM Table1 WHERE Team="1743";

Use first query to help locate Account codes that have both Team codes:
SELECT ID, Account, Team FROM Table1, Query1
WHERE Table1.Account=[Query1].[Account] AND Table1.Team="38466";

Use second query as criteria for Delete or to just filter out the records:
SELECT Table1.ID, Account, Team, Query2.ID
FROM Table1 LEFT JOIN Query2 ON Table1.ID = Query2.ID
WHERE Query2.ID Is Null;