need mailing list report to include husband & wife or one or the o

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work for a church. We have committees that need mailings sent out. Some of
the committees have married couples as members, some have just the husband or
just the wife, some committee members are single people. When I run a query I
can get all the members but it will print separate labels for the husband and
wife who are both members. I need them to go on one label together. If I make
a query showing "John & Jane Smith" it will print that even if only Jane is
on the committee. Is there a union SQL I can write to make it give the info I
need?
Thank you for your help.
Debra
 
One of the solutions can be as follows:

Suppose you have table "Members" with fields:
[ID],[LastName],[FirstName],[Sex],[Address]

Query1:
SELECT FirstName, LastName, Sex, Address, IIf([Sex]="male",[FirstName],Null)
AS Husband
FROM Members
WHERE (((IIf([Sex]="male",[FirstName],Null)) Is Not Null));

Query2:
SELECT FirstName, LastName, Sex, Address,
IIf([Sex]<>"male",[FirstName],Null) AS Wife
FROM Members
WHERE (((IIf([Sex]<>"male",[FirstName],Null)) Is Not Null));

Query3:
SELECT Members.Address, Members.LastName
FROM Members
GROUP BY Members.Address, Members.LastName;

Query4:
SELECT Query3.Address, Query1.Husband, Query2.Wife, "Dear " &
IIf(IsNull([Husband])=True Or IsNull([Wife])=True,[Husband] &
[Wife],[Husband] & " and " & [Wife]) AS Addressee, Query3.LastName
FROM (Query3 LEFT JOIN Query1 ON (Query3.LastName = Query1.LastName) AND
(Query3.Address = Query1.Address)) LEFT JOIN Query2 ON (Query3.Address =
Query2.Address) AND (Query3.LastName = Query2.LastName);
 
Back
Top