Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008

    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.

    The Form:
    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?


  2. #2
    Join Date
    Jun 2008
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts