Union Query - SQL help please

[mmm_danone]

I've just inherited a DB off someone and am having trouble adapting a
Union Query. Unfortunately, there is duplicate data appearing which is
causing problems. I need to filter out the duplicate data with the
following rules...

p02CreditCardNumber - this field should be unique. Where there are
duplicates, I need to select which record to keep by checking the entry
in another field...

If p02AltitudeBusinessCard = "Yes" First choice, ignore all other
records
If p02AltitudeBusinessCard = "escalate t" Second choice
If p02AltitudeBusinessCard = "No" Third choice

Finally, if p02AltitudeBusinessCard fields are also the same - eg,
"yes" appears 2 or more times - then only one version is to be kept
- doesn't matter which one.

If this cannot be done with a single Union Query, I am happy to run an
append query or whatever beforehand. Any ideas much appreciated. SQL
code shown below.

Regards,

Jon


SELECT "Client Responses" as Header,
iif(p02AltitudeBusinessCard="Yes","Yes to
ABLE",iif(p02AltitudeBusinessCard="No","No to
ABLE",iif(p02AltitudeBusinessCard="Escalate t","Concierge
Desk","Unknown"))) as Subgroup, Intranet_Initial.FileDate,
Count(Intranet_Initial.FileDate) AS CountOfFileDate,
sum(YTD_PurchaseAmount) as YTD_Spend
FROM Intranet_Initial left join MASTERLIST on
Intranet_Initial.p02CreditCardNo = MASTERLIST.ACCOUNT_ID
WHERE (((Intranet_Initial.p02CreditCardNo) Not In (select Account_ID
from TEST_SUBMISSIONS)) AND
((Intranet_Initial.sessionStatus)="complete"))
GROUP BY Intranet_Initial.p02AltitudeBusinessCard,
Intranet_Initial.FileDate
UNION
SELECT "Delivery Method" as Header,
Intranet_Initial.p03CardCollectionDetails as Subgroup,
Intranet_Initial.FileDate, Count(Intranet_Initial.FileDate) AS
CountOfFileDate, sum(YTD_PurchaseAmount) as YTD_Spend
FROM Intranet_Initial LEFT JOIN MASTERLIST on
Intranet_Initial.p02CreditCardNo = MASTERLIST.ACCOUNT_ID
WHERE (((Intranet_Initial.sessionStatus)="complete") AND
((Intranet_Initial.p02CreditCardNo) Not In (select ACCOUNT_ID from
TEST_SUBMISSIONS)) AND
((Intranet_Initial.p02AltitudeBusinessCard)="Yes"))
GROUP BY Intranet_Initial.p03CardCollectionDetails,
Intranet_Initial.FileDate
UNION SELECT "Contact Route" as Header,
IIf(Left([Batch],2)="RM","Relationship Managed","Contact Centre") AS
Subgroup, Intranet_Initial.FileDate, Count(Intranet_Initial.FileDate)
AS CountOfFileDate, sum(YTD_PurchaseAmount) as YTD_Spend
FROM Intranet_Initial LEFT JOIN MASTERLIST ON
Intranet_Initial.p02CreditCardNo = MASTERLIST.ACCOUNT_ID
WHERE (((Intranet_Initial.sessionStatus)="complete") AND
((Intranet_Initial.p02CreditCardNo) Not In (select ACCOUNT_ID from
TEST_SUBMISSIONS)) AND
((Intranet_Initial.p02AltitudeBusinessCard)="Yes"))
GROUP BY IIf(Left([Batch],2)="RM","Relationship Managed","Contact
Centre"), Intranet_Initial.FileDate
ORDER BY 1, 2 DESC , 3;