@rpeare: Could I ask again for a little bit more clarification on posts #17 and #14?
Post #17 had a lot of ideas in it, all good stuff! I just want to be sure I am on the same page.
"It would be really helpful if you not only recorded incoming invoices but created your own so all your usage could also be in your table that stores incoming data."
Are you suggesting a sole transactions table for incoming and outgoing invoices? If so then great I'm on board! (see updated relationships below)
"If your intent is not only to have a functioning inventory control system in this database you do, indeed, have to have some method to record sales, usage for making compositions, measuring scrap AND a method of handling inventory adjustment when inventory counts happen at the end of the month/quarter."
This usage data, assuming the usage of an all inclusive "tblTransactions", would be dealt with via the forms and queries correct? Scrap and necessary additions/subtractions would also all be dealt with through the transactions table and associated forms for the various transaction types. Construct usages I would imagine could be obtained via the stored values for each construct. Am I correct? I added a stocktake table as well.
"In my suggested build [Post #14] your tblItems is storing both raw materials and constructs, the link to the tblConstructs shows which raw items feed into the constructed item. There's no reason to have raw items and constructs on different tables."
I can see the connection there certainly but I think my poor execution is preventing me from seeing how it all fits together. If the table is set up as you described in Post #14 and I needed to reference one of the items in the construct and see it's relevant info would I be able to? The lack of link back to the ItemID table makes me think I couldn't query based on the ItemID in the subtable. I fear this also might
"So for instance let's say Composition A is made of Item A and Item B and Item C in a 25%, 30%, 45% ratio. then it doesn't matter how large a batch you're making, You'd just have to know the final weight (or volume) you wanted to end up with and get the correct amounts, then record the usage in a different table."This is the part where I get lost. I thought usage was being stored in the subtable? Personally I'd rather keep track of the actual amounts as they can change rather frequently but I don't see that being a problem. I just wasn't sure about the "different table" you suggested.
Thanks again.