Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to Update Master List (Access 2002)

    I have a table with a master list of site locations and 3 other tables which must be checked to see if there any new locations. If there are any new locations I need to update the master list with the new site locations. Each of the new sites might be in one, two, or all three of the tables. The only way I can think of to do this is to create 3 unmatched records queries and have the output go to 3 temporary tables (or maybe just one), query the new table(s) against the master table and append the master table with the unmatched records. I get tired just thinking about it. Is there an easier way?

    Thanks for any help or suggestions you can give me. Location is the only field in the master table. There are many fields in the other 3 tables.

    Gary O

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Update Master List (Access 2002)

    You don't need intermediary queries or tables. The following takes longer to describe than to do.

    Create a new query in design view.
    Add the master table and one of the other tables.
    Join them on the Location field.
    Double click the line joining the tables.
    Select the option to include ALL records from the other table.
    Add the Location field from the master table.
    Specify Is Null as criteria for this field.
    Add the Location field from the other table.
    Select Query | Append Query...
    Specify the master table as target.
    Make sure that the Location field is specified in the Append To row in the column from the other table only.
    Append To must be blank in the column from the master table.
    Save this query, and run it whenever ypu need to update the master table from the other table.

    Repeat the above for the other two tables.

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Update Master List (Access 2002)

    Hans - I was not able to get it to work. I think because there can be the same location listed multiple times in the three tables that are compared to the master. As a result, I did a total query, so that I was returning only unique locations. The query notifies me that if will append 2 records to location, but it doesn't do really do anything. Any suggestions?

    Gary O

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Update Master List (Access 2002)

    If there are duplicates within one or more of the "other" tables, it is a good idea to make the query into a totals query, or to set its Unique Values property to Yes. Other than that, I see no reason why it shouldn't work. If you get a message that 2 records will be appended, that should happen, unless you get an error message.

    Is there any additional relevant information about the tables you could provide?

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Update Master List (Access 2002)

    Here is the data I am using and the append query I used to add the data to the table. I had to save it as an Access 97 DB to get it small enough to meet the maximum file size requirements.

  6. #6
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Update Master List (Access 2002)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Update Master List (Access 2002)

    You placed the field name in the 'Append to' row in the wrong column. You should be appending the value from the other table, otherwise nothing will happen.

    BTW, you can edit your own posts, so you could have added the attachment, instead of creating a new reply without text.
    Attached Images Attached Images
    • File Type: png x.PNG (3.2 KB, 0 views)

  8. #8
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Update Master List (Access 2002)

    Thanks. I knew it was something simple.

    Gary O

Posting Permissions

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