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

At first glance it would seem that you are 'mixing apples and oranges'.

When you say'…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)…' That appears to be a 'given'.

Whether or not some companies change their recipe midstream is a whole different subject. The previous formula counts usage, regardless of 'recipe'.

So, what's the problem? What are you trying to calculate?

Well, it's actually THE subject, since the point of the database is to calculate chemical usage, not product usage. Otherwise a simple spreadsheet would suffice.

I was illustrating that if the 'recipe' didn't change, it would be exremely simple. Product usage * chemical concentration = chemical usage. But since the 'recipe' DOES change, then it gets really hairy. Pretty sure I'll have to then track purchase dates (to determine how much was purchased with each different 'recipe'), inventory dates (to determine how much was used of each different 'recipe') etc.

The problem is that I have no idea how to do this. Especially if there is more than one recipe change in a year.

I can do it on paper, but don't know how to do it via SQL or VBA (specifically finding how many changes there are, assembling a date range, and then performing calculations for products with the date ranges as criteria.)

Any and all ideas are very welcome.

ACG

I sort of remember this type of problem from past MRP experience.

It sounds like some amount of product is formulated based on a recipe and the recipe changes from time to time.

You need a "use" table with:
Product ID,
Use Date,
Amount.

Next you need a "recipe" table with:
Product ID,
chemical concentration,
start effective date,
end effective date.

For each "use", you must find the "recipe" where the Use Date is between start effective date and end effective date.

I think this can be done with a nested query but I can't specify it off the top of my head.

HTH,
RichM

Sounds to me that it might be productive to include something like "Lot Number" in your source reagent description, making it part of a compound key. So now instead of asking how much ethyl mercaptan your process uses, you ask how much of lot 761 of ethyl mercaptan your process uses. And of course, the arrival of each lot gives you a chance to change formulas to account for the difference in your supplier's formula.

So your tables now include a "fudge factor" that says that lot 761 assays out at 99.6% of active ingredient whereas lot 760 assayed out at 99.45% of ingredient. That fudge factor should allow you to adjust your computations fairly straightforwardly.

Of course, it complicates other issues, but to be honest, you didn't introduce the complexity. It was your suppliers who did that. You are just responding to it. So if this costs you time (=money) to re-do part of your database, it is just a cost of doing business. And this variation in effective "molarity / molality / whatever you list in your table" is simply a recognition of the real world in which you work.

Hope this line of thinking helps.