Querying, then reversing the query.. (1 Viewer)

[Jonathan Kok]

OK, coming up with a title for this one was a little difficult. Almost as difficult as the problem I'm facing

Here's the basic premise. The database is used to register people for a weekend convention. Part of the registration allows the person to request a specific roommate. In an ideal world, this would be relatively simple--match the roommate to the person, and you're done!

Did I mention they're teenagers?

Here's what happens. In all of the following examples, you are given only the ID number of a person to start with (because referencing any column but the bound one in a combo box from a query seems impossible, despite what Microsoft has to say), and must dump all relevant names and ID's into a temp table.
Example 1:

ID	First	Last	RFirst	RLast
1	Matt	Brown	Luke	Wilson
5	Luke	Wilson	Matt	Brown
9	Phil	Heart	Matt	Brown

In this case, we have two people that have requested Matt Brown as their roommate. Based solely on the ID Number of any given person above I need to dump all three names into a temp table to be displayed to the user, so he can pick who will go where. Physically, the first two people would be roomed together, and the 3rd would be roomed in a single room nearby.

Example 2

ID	First	Last	RFirst	RLast
1	Matt	Brown	Luke	Wilson
5	Luke	Wilson	Matt	Brown
9	Phil	Heart	Matt	Brown
72	Craig	Johnson	Phil	Heart

Similar to the last, but we've added a fourth person that has requested the 3rd person. Doing it the manul way, we'd room the first two together, and the last two together, in rooms next to each other. Unfortunately, we may not find the last two until we're two floors away, and end up having to shuffle people all over the place to get it to work. Now, we could be jerks, and just say, 'sorry, you screwed up, your problem, not ours'...except facing a crying 14-year-old girl upset because she's not with her friends isn't exactly fun. This is why I want to find all four people immediately after rooming any one of the above.

Example 3:

ID	First	Last	RFirst	RLast
1	Matt	Brown	Luke	Wilson
5	Luke	Wilson	Phil	Heart
9	Phil	Heart	Craig	Johnson
72	Craig	Johnson	Matt	Brown

Ah, the circle of life. Person 1 requests person 2, person 2 requests person 3, 3 request 4, 4 requests 1. This is surprisingly common. And annoying. Physical results would be the same as the previous example.

So to summarize, I start with person 1. Add him to the temp table. Then find the person he requested as a roommate. Add him to the table. Then look at the roommate's requested roommate, compare it to the temp table. If the person is there (ie, a direct match), then stop. If he isn't there, then query the database. Not there? Stop. Found another hit? Add him to the table. Search again. Then, display them all in a list box allowing the user to pick and choose who goes where.

I don't hope to automate the whole thing. That's not the goal. There are a variety of other factors that go into rooming that could never be automated, and require a personal touch. All I want to do is make it easier to find duplicates to streamline the existing manual procedure.

Now, how do I do it?

 

[Len Boorman]

Re (because referencing any column but the bound one in a combo box from a query is impossible)

Believe the systax is Combo Name.Column (N) where N is the column number which starts with 0 (zero)

L

 

[Jonathan Kok]

Yes it is...except, it doesn't work; at least, not from a query. "=Forms![formname]![comboboxname].Column(1)" returns "Unidentified function 'Forms![formname]![comboname].Column' in expression".

 

[workmad3]

This could be a difficult one.

First step. Remove all single step loops into a 'paired' table. These are the people who did things right so shouldnt't be punished by not getting their desired pairing. Secondly, construct pairs where one person has requested another, but not the other way round. From here on in, you should be in first come, first serve mode (They didn't organise it right, you are trying to make the best of things) but also keep a note of who they wanted to go with, so when you assign rooms, you can keep them close to make them happy (Your friend is with someone else, but we put you with this other person who likes you, and you are next door, or something, try and make them happy). Once you have gone through this, then the people you have left won't have been requested by anybody, or they will be the last person in a closed loop like example 4, but with an odd number of people (So A-B-C-D-E-A) as even numbers will be split up correctly. You put these into single rooms, again noting who they wanted to go with.

You then have all the pairings set up, and should assign rooms. Assigning rooms before the pairs are sorted will involve having to reshuffle to keep people happy. If the number of pairs is going to be small enough, then this should be possible by hand.