Query each record in table (2000 sr1)

[reepc]

OK, bare with me on this one, it is simple and I can't remember how to do it! I have a query that I want to run for each record in a table. Basically I have a table that has names and dates and I am cross-referencing a table with dates, I want to find out which dates each person is missing. Any ideas? I have run something similar but I can't get it to work this time (my brain has retired for the weekend) Thanks!

[aardvark]

Cynthia: Try an Unmatched query on the two tables, the table of dates being table one, the table with names and dates being table two. The Unmatched query wizard will step you thru the process and have a nice weekend.

[reepc]

I tried that but then I get one of two things: either a list of dates that no person have or I get a list of dates and the people are different. I need it to break it down by each person and then individually (but not manually) list the days that each person is missing. Suggestions?

[WendellB]

I think you have a data normalization problem. I presume you have a name and several dates in each row of the first table, and the second table contains one date in each row. Is that correct?

[reepc]

I actually have one table that has a Name field and a Date field, and one table that I created with just one field for Dates. So I have a list of Names and Dates but I want the opposite, I need Names and missing dates! Oh and by the way, I'll be interested to hear what you find out about your split db, we have two and the same thing started happening. It bottlenecked the entire network and we ended up going with a metaframe/Citrix server and even after that had to put the backend on the Citrix server as well! Thanks for the help!

[WendellB]

Hi Cynthia -
This probably seem ridiculous, but the best approach that occurs to me at the moment is to do a make table where you put the unique Name records and the Dates table in the query with no join. What that should do is give you a set of records with every date for every name. That is called a cross join, and gives all the combinations of dates and names. Once you have that table, you simply run a query against that table Left Joined to the original Name and Date table, and select only the records where the original Name is null. Hope this makes sense. And you are most welcome for the help - besides I have a soft spot for people who live in NC - we lived in Burlington for several years.

[reepc]

Thank you! I never even knew that you could do that (the cross reference query), I can definitely work with that. Thanks and hello from Greensboro!

[WendellB]

You are most welcome - I think it will solve your problem even though there may be a more elegant solution. I'm dating myself, but I used to work in Greensboro and did musical theatre with a group called the Greensboro Lyric Theater. (in the 60s)