Rat's nest of a problem (1 Viewer)

[David R]

I've been trying to assign tours for an upcoming conference from the three options chosen by people, based on when they chose them. Now that the first tours are starting to fill up (maximum 40), some people should get shunted to their 2nd or 3rd choices. Having a hard time figuring out how to get Access to spit out that 'remaining' top choice, though.

I tried

SELECT IIF([TourChoice1]=10 Or [TourChoice1]=11 Or [TourChoice1]=14 Or [TourChoice1]=22,[TourChoice2],[TourChoice1]) 
AS TopChoice, tableRegistrants.RegistrationDate, tableRegistrants.TourFinal, tableRegistrants.PaymentMade, tablePeople.City, tablePeople.State
FROM tablePeople INNER JOIN tableRegistrants ON tablePeople.PersonID = tableRegistrants.RegID
WHERE (((tableRegistrants.TourFinal) Is Null))
ORDER BY IIF([TourChoice1]=10 Or [TourChoice1]=11 Or [TourChoice1]=14 Or [TourChoice1]=22,[TourChoice2],[TourChoice1]) DESC , tableRegistrants.RegistrationDate;

once I got four full tours, but then realized that it wasn't taking into account anyone whose 1st and 2nd choices were taken. There's a much larger IF statement possible here, but I'm almost certainly missing something.

Factoids: 25 tour options, 40 people max per tour, about 500 people total. They're chosen based on when they registered (early registrations get their first choice, overflow on full choices get their 2nd, etc). And no, I don't have any choice on how the registration database spits them out to me.

 

[namliam]

Should be pretty easy to do using some VBA, using queries would be a lot harder...

 

[David R]

Yeah, I eventually got a brute force method working, with visually comparing two side-by-side queries, but it wouldn't scale upward very well.