Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

    Unanswered: Query to select appropriate 'recipe / formula / mixture' version for any given date

    I have this table structure.

    tblMixture
    MixtureID (Primary Key)
    MixtureName

    tblVersion
    VersionID (Primary Key)
    MixtureID (Foreign Key)
    VersionDate

    tblIngredient
    IngredientID (Primary Key)
    IngredientName

    tblMixtureIngredients
    MixtureIngredientsID (Primary Key)
    VersionID (Foreign Key)
    IngredientID (Foreign Key)
    IngredientPercent ([I]Percentage[I])

    tblBatch
    BatchID (Primary Key)
    BatchDate
    MixtureID (Foreign Key)
    MixtureAmount

    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;

    tblMixture
    MixtureID (Primary Key)
    MixtureName

    tblIngredient
    IngredientID (Primary Key)
    IngredientName

    tblMixtureIngredients
    MixtureIngredientsID (Primary Key)
    PhaseInDate (Date - Date this ingredient info was updated)
    PhaseOutDate (Date - Date this ingredient info is no longer valid)
    IngredientID (Foreign Key)
    IngredientPercent (Percentage)

    tblBatch
    BatchID (Primary Key)
    BatchDate
    MixtureID (Foreign Key)
    MixtureAmount

    Then I could use a select stament to select the record from tblMixtureIngredients where BatchDate is between PhaseInDate and PhaseOutDate. Of course the problems that I can see right now for this is
    • when entering a PhaseInDate, I have to include an PhaseOutDate which is not know and will have to some arbitraty date far out into the future
    • data validation/entry could be cumbersome (For each update, the new PhaseInDate must equal the old PhaseOutDate)
    This should work but I really dislike the design as it doesn't make too much sense to from a table design viewpoint (at least I don't think so, if I am wrong feel free to give it to me!).

    I wish I could give a good explanation as to what it is I dislike about this table design.

    As always, I appreciate any help anyone has to offer!

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    See database sample.. (Access 97 version)
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could pull a sub-select to weed out only the most recent version for a given mixture id.

    something like:

    SELECT *
    FROM tblMixture INNER JOIN tblVersion
    etc etc
    WHERE tblVersion.VersionID = (SELECT MAX(VersionID) FROM tblVersion WHERE tblVersion.MixtureID = tblMixture.MixtureID)

    Basically returns the ingrediants that have the highest numbered version for each mixture.

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

    Thumbs up Thank You!

    Thanks! I could also apply the same principle on VersionDate.

    SELECT *
    FROM tblMixture INNER JOIN tblVersion
    etc etc
    WHERE tblVersion.VersionDate = (SELECT MAX(VersionDate) FROM tblVersion WHERE tblVersion.MixtureID = tblMixture.MixtureID)

    WONDERFUL!

    THANK YOU!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yup, same concept.

    Hope it works out for you!

  6. #6
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

    Cool

    Teddy,

    Works great with some minor modifications

    Final version
    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)));

    THANK YOU SO MUCH!!
    Last edited by Cosmos75; 04-29-04 at 19:07.

  7. #7
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

    Post Correlated Subquery

    Just wanted to post more into on the solution as a follow-up.

    See here.
    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
    Also, you might want to check out this thread FAQ: What is a query? A subquery? by JasonM
    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.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •