Multiple reservations for one prospect

[Vol]

I have two seperate tables in which I pull data from. One is the "leads" table and the other is the "reservations" table. The "leads" table houses all of the prospects information: name, address, etc. The "reservations" table houses the reservation info, such as reservation date, etc...These tables are linked together using [LeadNum] and [ResNum]
One [LeadNum] may have numerous [ResNum]'s that go along with it. Each [ResNum] has a status that goes along with it. Say a prospect booked a reservation and never showed up. I would status it as "NoShow". Then we could call the prospect back and reschedule them, they show up for their reservation and I status that as a "Show". There are some of the prospects that have a last status of "NoShow". I would like to use a query to pull out only those prospects. I hope this is enough information. Any help would be greatly appreciated.
Thanks!
C. Carpenter
V0lup2us@aol.com

 

[tpearo]

Create a select query using both tables with the criteria of "NoShow" = YES. Or just put NoShow in the criteria window.

 

[Vol]

That's just the beginning of my query. Several of these prospects have more than one NoShow. The way my query stands now, It will pull up all the reservation records for each [LeadNum], which could be just one or it could be five. I need to be able to pull out only the most recent NoShow for each [LeadNum]. (Just one)
for example...

I don't want to include:
LeadNum 125489: Name: Jim Smith
(all ResNum's below correspond with this LeadNum)
ResNum 4271 Status-NoShow Date-1/12/01
ResNum 4386 Status-NoShow Date-1/18/01
ResNum 4621 Status-Show Date-1/21/01

I DO want to include:
LeadNum 462989: Name: Mark Johnson
(all ResNum's below correspond with this LeadNum)
ResNum 2169 Status-NoShow Date-12/28/00
ResNum 2348 Status-NoShow Date-1/9/01
C. Carpenter
V0lup2us@aol.com