Access 2007 Join Update Query

Question

Have 2 tables in Access 2007, both lists of certain tasks to be accomplished.

-Summary -Master

Each table has a different format, but the Summary table contains some Batch IDs, some of which match up with the Master table.

What I want to do is Update 'Posted Date' on the items in the Master table with the 'Entry Date' for the matching rows by Batch ID on the Summary table.

For instance, Master has batch IDs 1, 2, 3, and 4..while Summary only has batch IDs 1 and 4. I want to take the posted date from the rows that contain batch IDs 1 and 4 on Summary and update those respective 'Posted Dates' on the respective rows in the Master table.

Right now I have a query that joins the two tables together to find the matches by Batch ID (and that works fine), but I'm having trouble with the update query.

And specifically, I only want to update the 'Posted Date' if the posted date within the summary exists (as in, not null).

Any thoughts to how to do this would be very useful. Thanks!

    Sample Data:

    Master List:
    ID |    Title    |  Batch   |   Posted Date
    1      Capital       CRN01       
    2      Commercial    CMP04    
    3      Dogs          DOG07    

    Summary List:

    Reference | Entry Date  
    CRN003      4/9/2012
    CRN01       4/13/2012
    CMP04       5/3/2012
    PLM03       3/3/2012

So I'd want the Master List items that match their Batch #'s to the Summary's Reference numbers to be updated with the Entry date of those respective items back to the Master list.

After the query, the master list should look like this:

    ID | Title     |  Batch  | Posted Date
    1    Capital      CRN01     4/13/2012
    2    Commercial   CMP04     5/3/2012
    3    Dogs         DOG07                 (no change)

SOLVED:

Used a single Update Query, linked the Reference field to Batch field, then just put the Update To field as [Summary].[Posted Date]. Thought I had to use 2 queries, but one worked out :)

You can post an answer to your own question. You can even accept that answer after a certain amount of time. — Fionnuala

Max Nachamkin Max Nachamkin · Accepted Answer · 2012-06-20T15:24:08

Used a single Update Query, linked the Reference field to Batch field, then just put the Update To field as [Summary].[Posted Date]. Thought I had to use 2 queries, but one worked out :)

Access 2007 Join Update Query

1 Answers