Query how to show a temp address over a perm address

[Lynn73]

Hope you can help!

I have an access table which lists a customers address, however, if the customer has a temporary address it will also list that on a separate line -

CustID Address Type
1234 5 The Street P
1234 12 The Street T
2345 13 The Road P
3456 12 The Avenue P

Where P = Permanent and T = Temporary

I want to set up a query to show the permanent address where there is only one address, but where the customer has a temporary address also, I want to list the temporary one instead.

Thanks

 

[pbaldy]

I'll be interested to see the different ways of accomplishing this. Here's one:

SELECT CustID, Address, Type
FROM TableName
WHERE Type = 'T'
UNION ALL
SELECT CustID, Address, Type
FROM TableName
WHERE CustID Not In(SELECT CustID
  FROM TableName
  WHERE Type = 'T')