I had touched on this awhile back, but based on data inconsistency, I gave it up as I didn't think it was doable.
Now I think it can be done, at least partially.
How can I design a query that takes quantities assigned to a typename/ID then update a field in another table with that quantity where typename matches?
This would involve 3 different tables. [tblContractorJob], [tblDrawingFixtureType], and [tblProduct]. [tblProduct] doesn't have any relationship to the other two and is essentially a stand alone table.
Here is the structure for each:
[tblContractorJob]
[tblDrawingFixtureType]:
[tblProduct] (it is very wide in datasheet view, so here it is in design):
So what I am trying to do is run a query where the user selects which counts they want to use, either our in house counts from [tblDrawingFixtureType], or select a contractors set of counts from [tblContractorJob] and update [tblProduct]'s [Quantity] field with those counts where their fields [TypeName] matches (I did just notice I accidentally used a [Type] in the product table and it is now fixed).
For ones it doesn't find a match, open a form showing all of those and allow the user to manually input values for those. This is so the user can quickly change entire counts for a quote.
I would assume it would be something along the lines of if [typename] from table is like [typename] from product update quantity to quantity from other table.
Now I think it can be done, at least partially.
How can I design a query that takes quantities assigned to a typename/ID then update a field in another table with that quantity where typename matches?
This would involve 3 different tables. [tblContractorJob], [tblDrawingFixtureType], and [tblProduct]. [tblProduct] doesn't have any relationship to the other two and is essentially a stand alone table.
Here is the structure for each:
[tblContractorJob]
[tblDrawingFixtureType]:
[tblProduct] (it is very wide in datasheet view, so here it is in design):
So what I am trying to do is run a query where the user selects which counts they want to use, either our in house counts from [tblDrawingFixtureType], or select a contractors set of counts from [tblContractorJob] and update [tblProduct]'s [Quantity] field with those counts where their fields [TypeName] matches (I did just notice I accidentally used a [Type] in the product table and it is now fixed).
For ones it doesn't find a match, open a form showing all of those and allow the user to manually input values for those. This is so the user can quickly change entire counts for a quote.
I would assume it would be something along the lines of if [typename] from table is like [typename] from product update quantity to quantity from other table.