I have an Excel sheet that handles data input and utilizes an Access database to update materials available to track what materials are used. The relational database allows me to track where materials originated (some materials are made of lots of other materials) and its going to solve a lot of issues I've had with tracking and determining sources that are causing issues. Each time material(s) are used in the front end, a project record is created in the database which includes the material used as well as other data related to the project.
I'm trying to decide the best way to track that material has been used and can think of a couple of ways though I'm not sure which is best or if there is a better way.
Method 1: I create a delimited list in a field of the material's record in which unique IDs generated from the Excel front end are listed as material is used and each unique ID can be counted towards material used, that way duplicates will be avoided when the excel files goes to update by checking its unique ID against the list. This is what I am thinking is the best solution, though would end up adding a lot of extra data to record a unique IDs to track each "1"
Method 2: A query is run against all the records created by the front end and the materials listed in each record are tallied and their total subtracted from starting amount for each material (this seems like it will become inefficient when the total number of records gets large)
I feel like there must be a better way to do this but I'm coming up blank.
Bookmarks