CODEWITHSUNDEEP
sqlms-access-2013
garrettbernard
garrettbernard

Reputation: 23

Joining multiple tables in Access and limiting to Top 1 result

I have three tables that need to be joined in order to get monthly inventory data in return.

Table 1: TargetInventory
Table 2: TargetValue
Table 3: TargetWeight

[TargetInventory] does not change after being added the first time.

[TargetValue] is just a small table that includes prices of various types of metal.

[TargetWeight] is updated monthly as part of our inventory process. We INSERT new data, we never UPDATE old data.

Below is the relationship between these tables. (Sorry, I don't have the reputation points to post an image. Brand new here, so hopefully this makes sense.)

(* = UniqueKey)

--TargetValue--      --TargetInventory--  --TargetWeight--
*MaterialID <===|    *TargetID <=====|    *ID
 Material       |===> MaterialID     |===> TargetID
 PricePerOunce        Length               RecordDate
 Density              Width                Weight
                      Thickness
                      DateInInventory

The TargetWeight table contains multiple records for TargetID (since a new one is added every month at inventory). That's good for me to track historical usage, but for the current inventory value, I only need the most recent TargetWeight.Weight to be returned.

I don't know how to do a CROSS APPLY from within another INNER JOIN, so I'm at a loss for how to do this (without switching to mySQL and just doing a LIMIT 1...)

I think it needs to look something like what's below, but I'm not sure how to finish the query.

SELECT
TargetInventory.TargetID AS TargetInventory_TargetID, 
TargetInventory.MaterialID AS TargetInventory_MaterialID, 
TargetInventory.Length, 
TargetInventory.Width, 
TargetInventory.Thickness, 
TargetValue.MaterialID AS TargetValue_MaterialID, 
TargetValue.PricePerOunce, 
TargetValue.Density, 
TargetWeight.ID, 
TargetWeight.TargetID AS TargetWeight_TargetID, 
TargetWeight.RecordDate, 
TargetWeight.Weight
FROM
(TargetValue 
    INNER JOIN TargetInventory 
    ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
)
CROSS APPLY (
     SELECT TOP 1 *
     FROM .....
)

Upvotes: 2

Views: 1115

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123484

The following query works for me in Access 2010. It uses an INNER JOIN on a subquery to take the place of the CROSS APPLY (which Access SQL doesn't support). It assumes that there will be no more than one [TargetWeight] record for a given (TargetID, RecordDate):

SELECT 
    TargetInventory.TargetID AS TargetInventory_TargetID, 
    TargetInventory.MaterialID AS TargetInventory_MaterialID, 
    TargetInventory.Length, 
    TargetInventory.Width, 
    TargetInventory.Thickness, 
    TargetValue.MaterialID AS TargetValue_MaterialID, 
    TargetValue.PricePerOunce, 
    TargetValue.Density, 
    LatestWeight.ID, 
    LatestWeight.TargetID AS TargetWeight_TargetID, 
    LatestWeight.RecordDate, 
    LatestWeight.Weight
FROM 
    (
        TargetValue 
        INNER JOIN 
        TargetInventory 
            ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
    ) 
    INNER JOIN 
    (
        SELECT tw.*
        FROM
            TargetWeight AS tw
            INNER JOIN
            (
                SELECT TargetID, MAX(RecordDate) AS LatestDate
                FROM TargetWeight
                GROUP BY TargetID
            ) AS latest
                ON latest.TargetID=tw.TargetID
                    AND latest.LatestDate=tw.RecordDate
    ) AS LatestWeight
        ON LatestWeight.TargetID = TargetInventory.TargetID

Alternative approach specifically for Access 2010 or later

If the above query bogs down with a large number of rows in [TargetWeight] then another possible solution for Access 2010+ would be to add a Yes/No field named [Current] to the [TargetWeight] table and use the following After Insert data macro to ensure that only the latest record for each [TargetID] is flagged as [Current]:

AfterInsert.png

Once that is done, the query would simply be

SELECT 
    TargetInventory.TargetID AS TargetInventory_TargetID, 
    TargetInventory.MaterialID AS TargetInventory_MaterialID, 
    TargetInventory.Length, 
    TargetInventory.Width, 
    TargetInventory.Thickness, 
    TargetValue.MaterialID AS TargetValue_MaterialID, 
    TargetValue.PricePerOunce, 
    TargetValue.Density, 
    TargetWeight.ID, 
    TargetWeight.TargetID AS TargetWeight_TargetID, 
    TargetWeight.RecordDate, 
    TargetWeight.Weight
FROM 
    (
        TargetValue 
        INNER JOIN 
        TargetInventory 
            ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
    ) 
    INNER JOIN 
    TargetWeight 
        ON TargetInventory.TargetID = TargetWeight.TargetID
WHERE TargetWeight.Current = True;

To maximize performance, the [TargetWeight].[TargetID] and [TargetWeight].[Current] fields should be indexed.

Upvotes: 2

Dimt
Dimt

Reputation: 2328

SELECT      TargetInventory.TargetID AS TargetInventory_TargetID, 
            TargetInventory.MaterialID AS TargetInventory_MaterialID, 
            TargetInventory.Length, 
            TargetInventory.Width, 
            TargetInventory.Thickness, 
            TargetValue.MaterialID AS TargetValue_MaterialID, 
            TargetValue.PricePerOunce, 
            TargetValue.Density,        Weight.ID, 
            Weight.TargetID AS TargetWeight_TargetID,
            Weight.RecordDate, 
            Weight.Weight
FROM        TargetInventory
INNER JOIN  TargetValue ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
CROSS APPLY (
                SELECT TOP 1 *
                FROM    TargetWeight
                WHERE   TargetID = TargetInventory.TargetID
                ORDER BY RecordDate DESC
            )   AS Weight

Upvotes: 0

Related Questions