Unique values in a query

[Dave K.]

I have a query based on two tables joined together. The data that is pulled
is as follows:

if it is a Primary or Secondary Agent (Coded as P or S)
Agency Code (Number)
Agency Name (Text)
Address (Text)
City (Text)
State (Text)
Zip (text)

I want a query to pull the Agent code, Agent Name, Address, State, and Zip
for all Primary agents and only the Seconday Agents where the address is
different from that of the primary agents address.

Thanks

 

[vanderghast]

SELECT Address, City, State, ZIP, LAST(agencyCode), LAST(AgencyName),
LAST(code)
FROM table
GROUP BY Address, City, State, ZIP
HAVING (COUNT(*)=1 AND LAST(code) = "P" )
OR COUNT(*) = 2
ORDER BY LAST(agencyCode), LAST(AgencyName), LAST(code)




Indeed, if the COUNT = 1, the address appears just once, so we have to keep
it only if its code = "P"
If COUNT = 2, "P" and "S" are the same address, so the group naturally
returns just one after the group is made.


I assumed the field with the code P or S is called 'code'.

The order by clause is optional.



Vanderghast, Access MVP

 

[vanderghast]

Note that I suspect it is unusal to have only a secondary address, and NO
primary address! If that just cannot be, or if you want the secondary
address in such case, simply remove the HAVING clause.


Vanderghast, Access MVP