A
agahagan24
Guest
Hello,
I am building a database that will track chemical usage, which is calculated by product usage * chemical concentration in that product. Product inventory is taken monthly, and my queries find the total product usage (by starting with the beginning inventory, adding all purchases, and subtracting ending inventory), and everything was hunky dory until I realized that some companies change their product 'recipe' from time to time.
Now I have no idea how to do this. I know that the concentration change will have to be dated, and that that date will then be used as part of a criteria to determine actual chemical usage. (Is this making ANY sense?)
So basically I'm stuck and would LOVE a little push in the right direction. The trick is the criteria WHERE statement...I can't use WHERE RevisionDate < InventoryDate, because then all previous revision dates would be included. I'd need to know how many revision dates there are for each product...are you banging YOUR head now too?
If ANYONE has any ideas how I could do this...I haven't words to express my thanks...but I may have some $$.
TIA,
Agahagan
I am building a database that will track chemical usage, which is calculated by product usage * chemical concentration in that product. Product inventory is taken monthly, and my queries find the total product usage (by starting with the beginning inventory, adding all purchases, and subtracting ending inventory), and everything was hunky dory until I realized that some companies change their product 'recipe' from time to time.
Now I have no idea how to do this. I know that the concentration change will have to be dated, and that that date will then be used as part of a criteria to determine actual chemical usage. (Is this making ANY sense?)
So basically I'm stuck and would LOVE a little push in the right direction. The trick is the criteria WHERE statement...I can't use WHERE RevisionDate < InventoryDate, because then all previous revision dates would be included. I'd need to know how many revision dates there are for each product...are you banging YOUR head now too?
If ANYONE has any ideas how I could do this...I haven't words to express my thanks...but I may have some $$.
TIA,
Agahagan