So a Mixture is made of several Ingredients. Each ingredient is quantified as a percentage of the total weight of the mixture.
So when a “batch” is made, I can use the total amount of the mixture that was made to figure out mow much of each ingredient was used. I need to summarize the total usage for each ingredient for all mixture over several timeframes (Daily, Monthly, and 12-month period)
However, the “recipe” for a mixture may change. It is necessary to maintain a history of each recipe. Thus the reason for tblVersion. I have stored the date when the mixture change occurred in order to use it as a way to ensure that when the queries calculate ingredient usage it will automatically choose the correct version.
I am not sure how to do this correctly. I have managed to do this (at least I think I have) using three queries (qryBatch1, qryBatch2, and qryBatch3). Each query successively weeds out the wrong version.
QryBatch1 – Selects ALL batches and ALL Versions (Obviously, I won't be selecting ALL batches on the final version but it is just an test for now.)
QryBatch1 – Selects ALL batches and ALL Versions from qryBatch1 where VersionDate <= BatchDate
QryBatch1 – Selects ALL batches and Version where VersionDate = Maximum from qryBatch2
Then I have qryBatchDetails to perform the ingredient usage calculations.
I think this works but I was wondering if there was perhaps a better way?
- More efficient query?
- Different table structure?
I’ve gotten advice that rather than storing MixtureID in the Batch table, store the VersionID.
How I wish I could do that (not that I had thought of doing that, mind you. Not gonna give myself more credit than I deserve). But currently the way a Mixture is tracked is by MixtureID. This is an add-on to an existing database. It more complicated than I have made it here. The Mixture presented here is actually more of a sub-mixture. The Mixture here are chemical materials made up of several chemical compounds. The product is actually a mixture of these chemical materials. The existing database know what goes into each product (much like this structure of knowing how much of each ingredient goes into a mixture) and totals up the specified timeframe's usage for each chemical material (i.e. Mixture here).
Since there are existing queries that total usage by MixtureID I need a way to use the existing queries instead. I hope that I have not made it more confusing.
Just to put this out there, another possible table structure that has been thrown out there is this structure;
MixtureIngredientsID (Primary Key)
PhaseInDate (Date - Date this ingredient info was updated)
PhaseOutDate (Date - Date this ingredient info is no longer valid)
IngredientID (Foreign Key)
SELECT tblBatch.BatchDate, tblMixture.MixtureID, tblMixture.MixtureName, tblBatch.MixtureAmount, tblVersion.VersionID, tblVersion.VersionDate, tblMixtureIngredients.IngredientID, tblMixtureIngredients.IngredientsPercent
FROM ((tblMixture INNER JOIN tblBatch ON tblMixture.MixtureID = tblBatch.MixtureID) INNER JOIN tblVersion ON tblMixture.MixtureID = tblVersion.MixtureID) INNER JOIN tblMixtureIngredients ON tblVersion.VersionID = tblMixtureIngredients.VersionID WHERE (((tblVersion.VersionDate)=(SELECT MAX(tblVersion.VersionDate) FROM tblVersion WHERE tblVersion.MixtureID = tblMixture.MixtureID and tblVersion.VersionDate <= tblBatch.BatchDate)));
In fact, this is a correlated subquery. Since the subquery is in the WHERE clause, it is executed once for every row in the "outer" query.
This is "worse", in the sense that the subquery is being repeatedly executed. However, some problems require such subqueries (quite a lot, actually), and you might just have to live with the performance
What is a subquery?
A subquery is simply a query inside another query. Access can "nest" queries several levels deep, and although nested queries generally have comparatively high performance costs, they are sometimes preferred over having two or more seperate queries.