Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have created a query using the unmatched query wizard. The query works fine except that in the datasheet view of the query I can not modify any of the data. I am using this query to find all records where the email address is not matching the list of email addresses I have for my agents.

    Any thoughts

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Yes queries made using the unmatched query wizard are not updateable.

    To achieve the same result with an updateable query you need a query with a subquery


    Select tableA.* from tableA where tableA.email not in (select tableB.email from tableB)
    Regards
    John



  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If using sub-queries in Access databases with large tables of data, you may find that processing is slow.
    Andrew

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Yes queries made using the unmatched query wizard are not updateable.

    To achieve the same result with an updateable query you need a query with a subquery


    Select tableA.* from tableA where tableA.email not in (select tableB.email from tableB)

    I am lost here...

    I have attached what I am working on for you to see...

    2 tables
    [master data] - This is the table holding all the records
    [agent] - this is the table I want to verify the email against.

    The query should show all records in [master data] where there is no match to an email address found in [agent]
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AKW View Post
    If using sub-queries in Access databases with large tables of data, you may find that processing is slow.

    That is where I get no choice. The main table will be holdingh a lot of records... the only good thing is that this function will only be used once a month or so... and only by me...

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is a version that works. query2.

    My original plan a "not in " subquery did not work. Not sure why.

    This one finds any records in Master data where the ID is in your first unmatched query.

    This does work and is updateable.

    Is there a reason that the master data table uses the email address to identify the agent? Standard practice is to always use the key field to identify a record in another table.

    [attachment=88919:help_Modified.zip]
    Attached Files Attached Files
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Here is a version that works. query2.

    Is there a reason that the master data table uses the email address to identify the agent?

    [attachment=88919:help_Modified.zip]

    This is the way the information is tracked in other systems and as you can see from the query that the information is sometimes wrong comming in.

Posting Permissions

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