Unanswered: Non-updatable query problem for a form
I've got a small DB that's been developed for materials management duties at an electronics manufacturing business.
We're a contract manufacturer, who builds circuit boards of our customer's designs, using their Bill of Materials.
My problem is, I need to pull a new piece of data into a form, and that requires aggregation... and I'll explain why below.
(the form is built on an updatable query over a star-schema design)
That aggregation (I think) is causing the entire query to become non updatable - I need a solution!
About the query:
It's built on a star schema, the dimension tables are used to populate drop-down selections in the form, and the only table being updated is the central fact/matrix table.
The sourcing of the parts for these electronic assemblies throws an interesting twist- Essentially I have three potential part numbers for any given part that goes into these assemblies: BOM PN - the part number specified by the customer. Used PN - sometimes, purchasing has to buy an alternative part that has the same characteristics. Customer PN - Customers sometimes assign their own part number for reference, particularly when they don't require a specific vendor's part.
So, my Parts table (a dimension table) contains data on parts tht we've actually purchased: Mfg PN - which ties to Used PN - parts we've actually bought, and are therefore in our inventory system. Manufacturer - The manufacturer of that part number. Description - straight off the customer's BOM.
Right now it's nearly as simple as my Parts table - it's got controls for MfgPN, Manufacturer, and Quantity.
To pull in the Customer PN for reference, I have to pull it from the Assembly Details table, and that means I need to basically do a "select distinct", since the corresponding Mfg PN's are in there many times.
How can I include the Customer PN, so that it corresponds accurately with the existing data on the inventory management form - without making that query non-updatable?
The problem is what I need to do with this form:
I need to use the Customer PN to retrieve the associated MfgPN records.
And, I THINK that means that I have to include it in my query that the form is referencing...
Or, possibly using a form/subform, but I think it's trickier than that, because there can be more than one MfgPN associated with one Customer PN.
IF it were possible, I could use the main form to select a Customer PN, and then the subform could display all associated MfgPN instances.
...but I think the problem with that is that my outer query is what will return multiple MfgPN's.
I need to have Customer PN in the subform's query - which is the problem I'm having now.