Andre
I have been reading your posts with the DSUM function as the header. I now see you attacking the same problem from another direction. Very commendable!
I would like to offer some ideas not directly related to your post. Hopefully, this will not discourage others from responding to the requested subject.
I have worked with inventory systems for nearly 20 years. Typically, the current QOH is a column in the Inventory Master table, and is maintained by an Inventory Transaction table. However, you are correct in believing the QOH is a dynamic value, based on summarizing credits and debits. In a pure relational design, this is how it SHOULD be.
However, years of practical design have agreed it is acceptable to de-normalize this for a few important reasons.
I do not want to spend the time to elaborate on this, unless you feel it would aid you in your project.
Anyway, if you already store the current QOH, you will eventually need to evaluate the PROJECTED QOH (trust me). I will send you a sample MRP/Inventory database if it will aid you in your design. It does a fair job of showing the key tables, and their relationshps.
Send me an E-mail and I will forward it to you. As for solving your immediate problem, if you do not get a quick Access response, I will send you an example of how to do it with DAO.
Regards
stevem@nuprecon.com
SteveAndEdie1@attbi.com
