Create an update query

[Bobwords]

Hello,



I'm trying to create an update query, but I'm not 100% sure how to go about doing what I need. I have a database that I am using to track inventory shipments. There are a total of 3 device types (the tables 7962, 7942 and 6921) which are tracked in the "RMA_Tracker" table. The field Mac_Address on the RMA_tracker table is how I'm determining if the device is in our out of inventory. If that field is filled, then I want it to copy the date from the RMA_tracker table to the Date field on the device tables. I'm using the "date" field on the individual device tables to determine inventory levels (no date = item in stock).



I hope the image helps, as I'm finding it harder to describe this than I thought I would.

[John_G]

If the structure of each of the three device tables is the same, i.e. the fields are the same, then you should have only one device table, not three. Device_type would be in that table, not the RMA_Tracker table. Presumably the Mac_Address is unique for each device, regardless of type?

If you can use only one devices table (instead of three) , in an update query you could join RMA_Tracker and Devices on Mac_Address, and update Date_Shipped in the devices table with the date in RMA_Tracker, where that RMA_Tracker date is not Null.


PS - Don't use "Date" as a field name - doing so can sometimes lead to problems. "Date" should be considered as "reserved word"

Post back if you can't use only one Devices table, or if you need further details .

HTH

John

[lfpm062010]

You will need 3 update queries. One for each device table.

JOIN the RMA_Tracker and each device table using field Mac_address and device_type.

[Bobwords]

If the structure of each of the three device tables is the same, i.e. the fields are the same, then you should have only one device table, not three. Device_type would be in that table, not the RMA_Tracker table. Presumably the Mac_Address is unique for each device, regardless of type?

If you can use only one devices table (instead of three) , in an update query you could join RMA_Tracker and Devices on Mac_Address, and update Date_Shipped in the devices table with the date in RMA_Tracker, where that RMA_Tracker date is not Null.


PS - Don't use "Date" as a field name - doing so can sometimes lead to problems. "Date" should be considered as "reserved word"

Post back if you can't use only one Devices table, or if you need further details .

HTH

John

Ok, I've merged the tables together (a much better method, I do have to say) but I'm not exactly sure what you mean for the update query.



I've linked these two fields in a query, but what exactly would I need to place after that? I've never built an update query before.

Thanks for the help by the way!