Updating fields based on location of other fields

[desertfx41]

Good morning everybody. I am trying to subtract amounts from one out of five possible fields based on the location of another piece of data using an Access query. Here's a better explanation:

Table to Update: ProductData
Field Names-Location1, Location2, Location3, Location4, Location5, Quantity1, Quantity2, Quantity3, Quantity4, Quantity5

Table used for the update: QtyUpdate
Field Names-Location, Quantity

If a given location in the QtyUpdate.Location field falls in ProductData.Location2, then I need to subtract QtyUpdate.Quantity from the corresponding quantity field ProductData.Quantity2


My guess was to use an IIf statement something like this: IIf(([ProductData].[Location1] CONTAINS [QtyUpdate].[Location], [ProductData].[Quantity1]-[QtyUpdate].[Quantity])) Or IIf(([ProductData].[Location2] CONTAINS [QtyUpdate].[Location], [ProductData].[Quantity2] - [QtyUpdate].[Quantity].........etc.

Any help would be appreciated, thank you.

[June7]

ProductData table is not a normalized structure. Since the tables cannot be joined in query, would need to use DLookup() domain aggregate function. The conditional expression will be far too long and cumbersome for query and I expect this will require a custom VBA function.

CONTAINS is not an operator in VBA. Use = sign or InStr() or LIKE and wildcard.

Ideally, aggregate data (quantity balance) would not be saved into table. Save transaction records of product in and product out and then calculate balance when needed.

[desertfx41]

Thank you for the reply. Yes I noticed in my research this morning that CONTAINS is not an operator. I don't know any VBA and have only been working with SQL for a few months for Access queries. Keeping a transaction record would be nice. I will see what I can come up with using the condition types you mentioned.

[desertfx41]

The simple solution to this was to create five separate update queries that updated each quantity field and then a macro to run them all at once. Perhaps not the most elegant solution but it works.