Union without duplicates?

S

Stephanie

Hi. I have a query that brings back Contacts (and contact info) for Contacts
that meet certain criteria. I need to add Board Members into the mix. Some
of the Contacts are Board Members. I don't want to have Contacts/Board
Members on the list twice. I made a union query of sorts, but then I have
duplication. Plus I always end up with a column that is essentially bogus
info just trying to match up the columns.

So is there a way to compare the contacts in my two queries and then include
the Board Members that aren't already in the Contacts list? I'll be sure to
sing "one of these things is not like the other..." Thanks.

In case the sql helps
to get Contacts:
SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[ContactID]) AS [Animal List],
MemberStatus.MemberStatusID, MemberType.MemberTypeID, Contacts.MemberOption
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON Contacts.MemberTypeID =
MemberType.MemberTypeID
WHERE (((MemberStatus.MemberStatusID)=1) AND ((MemberType.MemberTypeID)=1)
AND ((Contacts.MemberOption)=1)) OR (((MemberStatus.MemberStatusID)=6) AND
((MemberType.MemberTypeID)=5)) OR (((MemberType.MemberTypeID)=5))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

To get Board (for Union query I would add a bogus field on to the select
statement to have the same number of columns:
SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS [Animal
List], Groups.GroupName, GroupMembers.GMemberEnd
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID =
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);
 
S

Sylvain Lafontaine

Unless you have added the option ALL, the Union statement will automatically
remove any duplicates; based on the selected columns. Of course, the
addition of a bogus column will likely block that because its value will be
different from the associated field in the other query. If this is the
case, then you will have to make the Union with only the minimum list of
fields in a subquery and then add the other columns in a global query.
Another solution would be to replace the UNION with some other statements,
for exemple an IN () or an Exists() conditions.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Stephanie said:
Hi. I have a query that brings back Contacts (and contact info) for
Contacts
that meet certain criteria. I need to add Board Members into the mix.
Some
of the Contacts are Board Members. I don't want to have Contacts/Board
Members on the list twice. I made a union query of sorts, but then I have
duplication. Plus I always end up with a column that is essentially bogus
info just trying to match up the columns.

So is there a way to compare the contacts in my two queries and then
include
the Board Members that aren't already in the Contacts list? I'll be sure
to
sing "one of these things is not like the other..." Thanks.

In case the sql helps
to get Contacts:
SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[ContactID]) AS [Animal List],
MemberStatus.MemberStatusID, MemberType.MemberTypeID,
Contacts.MemberOption
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON
Contacts.MemberTypeID =
MemberType.MemberTypeID
WHERE (((MemberStatus.MemberStatusID)=1) AND ((MemberType.MemberTypeID)=1)
AND ((Contacts.MemberOption)=1)) OR (((MemberStatus.MemberStatusID)=6) AND
((MemberType.MemberTypeID)=5)) OR (((MemberType.MemberTypeID)=5))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);

To get Board (for Union query I would add a bogus field on to the select
statement to have the same number of columns:
SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS [Member Name],
Contacts.MailingAddress1, [City1] & ", " & [StateOrProvince1] & " " &
[PostalCode1] AS Address, Contacts.HomePhone, [WorkPhone] & ("
x"+[WorkExtension]) AS WorkPhExt, Contacts.MobilePhone, Contacts.Spouse,
FldConcat("AnimalNB","Animal","ContactID",[Contacts].[ContactID]) AS
[Animal
List], Groups.GroupName, GroupMembers.GMemberEnd
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID
=
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null))
ORDER BY [LastName] & ", " & Nz([NickName],[FirstName]);
Click to expand...
 
Top