Help with Update query between 2 tables linked by 1 field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables, Assets and Assets2, which are both exactly the same except
that Assets2 is full and Assets only has field ItemNo filled. Basically I'd
like to update Assets with the data in Assets2 based on the ItemNo

Update Assets.Serv_date, Assets.Account, Assets.purch_date *with fields*
Assets2.Serv_date, Assets2.Account, Assets2.purch_date

where Assets.itemNo = Assets2.itemNo

I was thinking of using a stored procedure but I didn't because 1)I wasn't
sure if Access has SPs and, 2) there must be an easier way than doing it
with an SP.

Thanks,
VM
 
Use the Query Builder to make an Update query updating
Assets. One to One join between ItemNo. UpdateTo would
be [Assets2]![Serv_date] etc.
Roxie Aho
-----Original Message-----
I have 2 tables, Assets and Assets2, which are both exactly the same except
that Assets2 is full and Assets only has field ItemNo filled. Basically I'd
like to update Assets with the data in Assets2 based on the ItemNo

Update Assets.Serv_date, Assets.Account,
Click to expand...
Assets.purch_date *with fields*
 
I have 2 tables, Assets and Assets2, which are both exactly the same except
that Assets2 is full and Assets only has field ItemNo filled. Basically I'd
like to update Assets with the data in Assets2 based on the ItemNo

Update Assets.Serv_date, Assets.Account, Assets.purch_date *with fields*
Assets2.Serv_date, Assets2.Account, Assets2.purch_date

where Assets.itemNo = Assets2.itemNo

I was thinking of using a stored procedure but I didn't because 1)I wasn't
sure if Access has SPs and, 2) there must be an easier way than doing it
with an SP.
Click to expand...

Correct on both counts. Access has VBA which serves many of the same
roles as SPs, but that's not needed in this case.

ItemNo must be the Primary Key of each table. Create a Query in the
query design window, and add both tables; join by ItemNo if they're
not joined automatially. Change the query to an Update query using the
Query menu item or the query type icon. On the Update To line under
each Assets field, type

[Assets2].[Serv_Date]

or the other corresponding field; be sure to include the brackets or
it will try to update the field to the text string "Assets2.Serv_Date"
instead!

Run the query with the ! icon and you'll be done.
 
Back
Top