Question Sorting on 2 fields...

[Adam Caramon]

Ok, odd question. There is a main database that contains a ton of demographic information on STAFF members. We have meetings where both members of the STAFF and others (who we call GUESTS) attend.

I don't have the ability to modify the STAFF database at all, I simply am connected to it through ODBC. I have full access to design/modify/whatever the database where the GUEST information is kept.

If someone wants a report of all attendees at a meeting, I currently am giving a STAFF attendance report, and then a separate GUEST attendance report.

I can get all of this information on one report, however the sorting comes out odd because it sorts on the field STAFF_NAME, which GUESTS don't have (or vice versa).

Is there any way to instruct Access to consider the text in 2 different fields as a group, and then sort alphabetically on that group?

Or if anyone has another way of accomplishing my goal (alphabetic sort of 2 different fields, keeping in mind that I can't change the 1 database at all), then that would be helpful as well.

 

[namliam]

Are you using a union query??

Select Staff_name from staff
Union
Select Guest_name from Guests
order by staff_name

should sort the entire thing by Guestname/Staffname, like so:
a Staff
b Guest
c Staff
d Staff
e Staff
F Guest