alphabetical order from different fields.

[xtrmsound]

Hello,

I'm trying to build a report that will give me a list of names alphabetically from different fields inside one table.
My table contains these fields: Dorm, Room number, Name 1, Name 2, Name 3, Name 4 and name 5.

I want to build a report that will give me the names of each participant alphabetically with the Dorm and room number.


It should look like that

Dorm A, Room 1, A
Dorm C, Room 3, B
Dorm B, Room 2, C

How can I do it. I tried to google it, but I couldn't find how to do it in more than one field.

I attached an excel file that shows an example of the table.


Thank youexample.xlsx.zip

[June7]

The data is not normalized structure. Normalization would require another table for the names assigned to the dorm/room. Are you saving name or ID into the NameX fields?

tblRoomNames
DormRoomID
NameID

Otherwise, use a UNION query to rearrange the names into normalized structure. Must type or copy/paste into SQL View of query builder.

SELECT Dorm, Room, 1 AS Source, [Name 1] AS Resident FROM tablename
(the forum is not letting the rest of the UNION query to post - weird, I will try again later)

Now use that query as a source for subsequent queries.

[fredz]

I agree with June7, your data is not in a "normalized" design. You can expect to have similar difficulties as your application evolves over time and you desire new reports/features.

Nevertheless, to attack this problem, you might create 5 separate queries and then union them together.
Example:

Select Dorm, Room, Name1 From tblData
Union
Select Dorm, Room, Name2 From tblData
Union
Select Dorm, Room, Name3 From tblData
Union
Select Dorm, Room, Name4 From tblData
Union
Select Dorm, Room, Name5 From tblData

Then you can use this unionquery as your recordsource and sort by name.
(It will likely be slowwwwww).

Fred