If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
design question..
I'm working on a database selling metal products. I need to add element
analysis to for quality control of purchased goods. I've created an elements table, and a product elements table, to store the baseline values. Products - ProductID, Product Elements - ElementID, Element ProductElements - ProductID, ElementID, min, max Each product will have different sets of elements, and store these min/max values, to be compared against quality of purchases. When a certain product is purchased, the purchased element data needs to also be captured, and compared to these min/max baseline values that are stored. My question is, for a product purchased, does make sense that rows of elements be created to store the actual values - say if AL15 has elements of Al, Cr, and Mg..when product AL15 purchased, can these 3 rows be automatically created and tied to the purchase, based on knowing these 3 elements needed for the product from 'Productelements" table? if this is efficient..any ideas how to create these rows based on "Productelements"? thanks! |
#2
|
|||
|
|||
design question..
Yes: it makes good sense to have a related table where there are actual
composition records for the batch of the product you just purchased. Presumably, when you buy a product, you record that in a table like this: ProductPurchID primary key ProductID what product you bought SupplierID who supplied it (if a product can have different suppliers) PurchaseDate when it arrived Quantity how much in this batch The related table will have fields like this: ProductPurchID which batch this row is for ElementID what element it contains ElementPercent what the actual percentage is. This would be interfaced with a main form and subform. You can then flag records where teh ElementPercentage is outside the min/max requirements. To automatically enter the expected rows into the subform, use the AfterInsert event procedure of the main form to execute an append query statement for the ProductID of the record you just added, and Requery the subform so the new records show up. If Execute is new, there's a basic example he http://allenbrowne.com/ser-60.html BTW, it might be a good idea to avoid field names MIN and MAX, as these are reserved names: http://allenbrowne.com/Ap****ueBadWord.html#M -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "nycdon" wrote in message ... I'm working on a database selling metal products. I need to add element analysis to for quality control of purchased goods. I've created an elements table, and a product elements table, to store the baseline values. Products - ProductID, Product Elements - ElementID, Element ProductElements - ProductID, ElementID, min, max Each product will have different sets of elements, and store these min/max values, to be compared against quality of purchases. When a certain product is purchased, the purchased element data needs to also be captured, and compared to these min/max baseline values that are stored. My question is, for a product purchased, does make sense that rows of elements be created to store the actual values - say if AL15 has elements of Al, Cr, and Mg..when product AL15 purchased, can these 3 rows be automatically created and tied to the purchase, based on knowing these 3 elements needed for the product from 'Productelements" table? if this is efficient..any ideas how to create these rows based on "Productelements"? thanks! |
Thread Tools | |
Display Modes | |
|
|