Merging two queries for a report

[HB]

Our member table has fields for two names (person1, person2) I'd like to have
one report that lists all members alphabetically by both person1 and person2.
Say the Member has John Smith and Mary Doe (Persons, 1 & 2)....the report
would show both people under "Smith" (Smith, John, & Doe, Mary) and "Doe"
(Doe, Mary & Smith, John)

I have two queries now, that produce two reports (one sorting person1, the
other sorting person2) is there any way to merge this into one query and one
report?

As always, tia

HB

 

[Duane Hookom]

You might be able to use a union query. If you don't know how to create
these, check Help. If you still need help, come back with some table and
field names.

 

[HB]

Ok, will do.

Thanks again for the help last night on the codes to columns. My boss was
very happy we wouldn';t have to do it manually. I did give the site credit,
not me.

If I have problems and can't figure out with Union should I repost or
respond here?

HB

 

[Duane Hookom]

Please respond here if you can't figure out a union query.

 

[HB]

Ok, I read up on Union from the help and basicly Unioned my two querires and
got this.

SELECT MEMBER.MemberNumber, [LastName1] & " " & [FirstName1] &
IIf([isSenior1]," *S") AS Name1a, MEMBER.EquityStatus, MEMBER.CardExpireDate,
IIf([EquityStatus]="FULL","*F","") AS F1, [LastName2] & " " & [FirstName2] &
IIf([isSenior2]," *S") AS Name2a
FROM MEMBER
WHERE (((MEMBER.EquityStatus)="Full" Or (MEMBER.EquityStatus)="Current" Or
(MEMBER.EquityStatus)="NonCurrent"))
ORDER BY MEMBER.LastName1;
UNION SELECT MEMBER.MemberNumber, [LastName2] & " " & [FirstName2] &
IIf([isSenior2]," *S") AS Name2b, MEMBER.EquityStatus, MEMBER.CardExpireDate,
IIf([EquityStatus]="FULL","*F","") AS F2, [LastName1] & " " & [FirstName1] &
IIf([isSenior2]," *S") AS Name1b
FROM MEMBER
WHERE (((MEMBER.EquityStatus)="Full" Or (MEMBER.EquityStatus)="Current" Or
(MEMBER.EquityStatus)="NonCurrent") AND ((MEMBER.FirstName2) Is Not Null))
ORDER BY [Name1a];

However in datasheet view, I only see column from the first part of the
union, and I can only order by colunmns in the first part. I belive I need to
also order by Name 1b in the second part. I guess first question is why does
first part only appear in datasheet view?

thanks.

H.

 

[HB]

Despite the first reply. It seems to be working perferctly. The report seems
to be correct. Thank you.

HB

 

[HB]

Actually, datasheet view looks great, report isn't. I thought it would show
same result but it isn't. I posted in the reports forum, unless you can
supply some assistance here.

 

[HB]

Once again, pilot error, the report was still bound to old query......duh!

Thanks again for all your help Duane.

H.