Update Query Need Help! 1

[medreports2]

I am trying to create an update query based on parameters outlined in 1 table that correspond to values in another table.

tbl_Standards has several fields:
Category, Type, Subtype1, Subtype2, Phase, Hours

tbl_MasterDataTable has about 50 fields, including the fields in tbl_Standards.

I need to update the 'Hours' field in tbl_MasterDataTable based upon the parameters set up in tbl_Standards.

For ex:
Category= Audio
Type= CD
Subtype1= Changer
Hours= 1

What I want to do is update all of the entries in tbl_MasterDataTable that match this criteria with an update query. Does anyone have any ideas? Please let me know if you need more information!

 

[PHV]

Something like this ?
UPDATE tbl_MasterDataTable M INNER JOIN tbl_Standards S
ON M.Category=S.Category AND M.Type=S.Type AND M.Subtype1=S.Subtype1 AND Nz(M.Subtype2)=Nz(S.Subtype2)
SET M.Hours=S.Hours
WHERE M.Category='Audio' AND M.Type='CD' AND M.Subtype1='Changer'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[medreports2]

Well that would work for that particular combination right? the tbl_Standards has hundreds of combinations. I am trying to avoid creating a different query for each scenario, which is what I have been doing in the interim for a few combinations.

So ideally I would have a query that just compared the 2, and updated all records in the tbl_MasterDataTable that matched a particular combination in tbl_standards. Does that make sense?

 

[PHV]

Something like this ?
UPDATE tbl_MasterDataTable M INNER JOIN tbl_Standards S
ON M.Category=S.Category AND M.Type=S.Type AND M.Subtype1=S.Subtype1 AND Nz(M.Subtype2)=Nz(S.Subtype2)
SET M.Hours=S.Hours

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[medreports2]

THANKS FOR THE TIP, YOU GOT ME ON THE RIGHT TRACK! THANKS!