Lookup values in one column from another record

[cjayjones]

I have a list of students. all students have roommates. When I print out the list to assign rooms, the list is first all the students with their roommates, then all the roommates (since they're students) with their roommates. so I basically have 2 listings for every roommate pair. I need to make that 1 listing for every roommate pair. I can't begin to figure out how to do this.

[RuralGuy]

If you are using a query, how about posting the SQL so we can look at it?

[cjayjones]

Here's the SQL statement. It's a pretty standard one, just Fullname of the students and roommates name where a roommate has been assigned and the room number. Just can't figure out how to have only one set of distinct pairs instead of each room listing with two entries with the same names in different orders.

SELECT main.ID, main.[r/mate], main.[room #], [FullName]
FROM main
WHERE (((main.[r/mate])<>"0"));

Thanks for taking a look at this.

[RuralGuy]

You should try and avoid other than alpha characters and the UnderScore in your naming. It can cause strange problems. Are these two fields main.[r/mate] and main.[room #] text or numeric fields?

[cjayjones]

You should try and avoid other than alpha characters and the UnderScore in your naming. It can cause strange problems. Are these two fields main.[r/mate] and main.[room #] text or numeric fields?

Thanks. I understand about the naming, but I had to start with a table that someone else had set up, so I'm stuck with that. Both fields are text fields.

[RuralGuy]

I take it the field is set to "0" when there is no roomate?

[RuralGuy]

This SQL statement will not work without changes by you but it shows you where I'm going.

SELECT main.ID, main.[r/mate], main.[room #], main.[FullName]
FROM main
WHERE (main.[r/mate] <> "0")
AND (main.[FullName] Not IN (SELECT main.[r/mate] FROM main))

[cjayjones]

Wow. That looks interesting. I'll try that and let you know. Thanks for sticking with this.

[cjayjones]

Well, I got excited too quickly. It doesn't work. That query didn't return any names. I just realized why. Unfortunately, all the names are in both r/mate and FullName--since they're roommates. So, if you exclude all names in Fullname if you find it in r/mate, you've excluded everyone. Boy this one is frustrating.

[RuralGuy]

Is main a table or a query because the more I look at this issue the more something does not make sense. Of course I could be all wet too.

[cjayjones]

Main is a table.

And not making sense is part of my problem since I can't seem to talk through the logic to begin to program it. Let me try that again.

Everyone is a student.
Most students have roommates.
The roommates are also listed as students.
For each student with a roommate, 1 room is assigned.

If I do a listing based on student name, I only want to see one distinct pair of students, but I see the same pair listed twice, once with the original student name in the fullname column and once with the original student name in the r/mate column.

If I do a listing by room, I get two entries per room. Again, I only want 1 listing, no matter which order the names are in. And I can't begin to figure out the logic.

[RuralGuy]

Any chance you could zip yor db and attach it to a post so we could play with it?

[Spiftacu1ar]

EDIT: Disregard. see my next post.

(quote contains what I origianlyl wrote in this post)

You mentioned this only being needed when printing. Since this is the case, you are creating a report anyways, yes?

Make your query such that it displays only the room # and FullName (not the roommate).

Sort by room, you will now see that it shows room twice, and each of the people who stays in that room. Now, in a report, make two columns, and put the room # in the header. Full name in details. Setup columns to go across then down. It should show the room number, and two roomates on the next line.

If you want them in on line, you can use a sub report that is just as above, but does not show the room number. (although still make a blank header/grouping for the room). If you view the subreport, it should show each pair of roomates on one line and then go to the next line, and repeat.

Go back to the original report, and put the subreport in the header of the room group, right after the room number. Make lables above as neccesary.


Not sure how to do this in a query though.

[Spiftacu1ar]

Never mind my last post. I found a way to do it in queries only.

You need 3 queries
The first takes the main table and selects the fields FullName and Room#
Sort ascending by room#, total First by Fullname

The second is the same, except total Last by Fullname

The third combines them. Make a relation to the Room fields, and go from there. You will need to use an iif statement to prevent repeating a name in both columns when there is no roomate.

Here are my SQL statements: (I made my main table called test, so you wil have to replace "test" with "main" everywhere. Also, my field names were slightly different. You can figure it out.

Query 1:

SELECT test.Room, First(test.FullName) AS FirstOfFullName
FROM test
GROUP BY test.Room
ORDER BY test.Room;

Query 2:

SELECT test.Room, Last(test.FullName) AS LastOfFullName
FROM test
GROUP BY test.Room
ORDER BY test.Room;

Query 3 (what you need):

SELECT DISTINCT first.Room, first.FirstOfFullName, IIf([LastOfFullName]=[FirstOfFullName],Null,[LastOfFullName]) AS OtherFullName
FROM [first] INNER JOIN [last] ON first.[Room] = last.[Room];

You can probably do this smae thing in one query using "IN" somehow, but I don't really know how to do that.

[cjayjones]

Hi, and thank you! This worked great for a final listing! But I have a wrinkle to add. The reason I wanted this list was so that I would assign them to the same room, so room number is blank. And that breaks it.

I couldn't attach the real database since that has personal information in it (the organization is using it as I develop it -- fun...), but this is a stripped out version with the same basics for this issue. I was thinking I could do some sort of default room number such as a combination of the two names, but then I run into the same problem I had originally with the combination being unique because of name order.

Never mind my last post. I found a way to do it in queries only.

You need 3 queries
The first takes the main table and selects the fields FullName and Room#
Sort ascending by room#, total First by Fullname

The second is the same, except total Last by Fullname

The third combines them. Make a relation to the Room fields, and go from there. You will need to use an iif statement to prevent repeating a name in both columns when there is no roomate.

Here are my SQL statements: (I made my main table called test, so you wil have to replace "test" with "main" everywhere. Also, my field names were slightly different. You can figure it out.

Query 1:

SELECT test.Room, First(test.FullName) AS FirstOfFullName
FROM test
GROUP BY test.Room
ORDER BY test.Room;

Query 2:

SELECT test.Room, Last(test.FullName) AS LastOfFullName
FROM test
GROUP BY test.Room
ORDER BY test.Room;

Query 3 (what you need):

SELECT DISTINCT first.Room, first.FirstOfFullName, IIf([LastOfFullName]=[FirstOfFullName],Null,[LastOfFullName]) AS OtherFullName
FROM [first] INNER JOIN [last] ON first.[Room] = last.[Room];

You can probably do this smae thing in one query using "IN" somehow, but I don't really know how to do that.