Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query each record in table (2000 sr1)

    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!

  2. #2
    New Lounger
    Join Date
    Apr 2002
    Location
    Columbus, Ohio, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query each record in table (2000 sr1)

    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.

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query each record in table (2000 sr1)

    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?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query each record in table (2000 sr1)

    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?
    Wendell

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query each record in table (2000 sr1)

    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!

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query each record in table (2000 sr1)

    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.
    Wendell

  7. #7
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query each record in table (2000 sr1)

    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!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query each record in table (2000 sr1)

    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)
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •