Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem (Access 2002)

    I'm not much of a SQL programmer so this might be very simple.
    I have a query that I need to show all the records except for the records that are in a second query.

    For Example:
    Query1 has these records-
    1
    2
    3
    4
    5
    Query2 has these records
    2
    3
    4
    I need to limit Query 1 to show only records 1 and 5.
    I was thinking I need to use a not in clause but could not figure out the syntax.

    I will appreciate any ideas.

    Thanks,
    Eric

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query problem (Access 2002)

    Here is some sql- replace table1 with the name of the table, and key with the name of the key field.

    <pre>SELECT Table1.*
    FROM Table1
    WHERE (((Table1.key) Not In (select key from query1)));
    </pre>

    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query problem (Access 2002)

    Another way to do this is:

    SELECT Table1.*
    FROM Table1 LEFT JOIN Table2 ON Table1.Venue = Table2.Venue
    WHERE (((Table2.Venue) Is Null));

    I don't know which is the most appropriate, I would like to know this, anyone? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query problem (Access 2002)

    Try using the unmatched query wizard.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query problem (Access 2002)

    Patt
    In this case I don't think it matters which way you do it.

    I just tried the "unmatched query wizard" sugestion and the query it created was identical to yours.

    I nearly always do things like this with subqueries, but it is probably just habit. Although, I have seen lots of situations on this forum where people have made some queries very complicated by not using subqueries. So I suppose I use them to make sure that I keep remembering them as an option.
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query problem (Access 2002)

    John
    Thanks for your response, I rarely use sub-queries, hence my proposed solution. I was just wondering which was the faster of the two, in which case, I will change to that method.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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