CODEWITHSUNDEEP
sqlms-accessms-access-2007
Harry
Harry

Reputation: 4835

SQL / Access - Joining on multiple columns?

I've asked this question from a different perspective, but based on the answers to that question, I believe I am now closer to achieving what I need.

In summary, the problem is this: I have 3 tables, Metric, Desired_Future_State_Metric and Metric_Value_Measurement. As you may infer from the names, Metric_Value_Measurement is a series of date-bound measurements of different metrics (there will be multiple measurements for any given metric).

What I'm trying to do is show the latest measurement in a table of Desired_Future_State_Metric records. In a nutshell, this requires me to select the first record for any given Metric when the list is ordered by date. Unfortunately, the Access query builder doesn't allow me to do this, as I can't do a SELECT on a single record and sort by another column.

On this basis, I believe I need to write the SQL directly, and my attempt is below - unfortunately, this results in an error - Syntax error in query expression 'Metric.ID = Metric_Value_Measurement.Metric_ID AND (SELECT Measurement_Date FROM Metric_Value_Measurement WHERE Metric_ID = '1') = Metric_Value_Measurement.Measurement_Dat'. - which I believe is being caused because I am attempting to join a single record (the from the SELECT MAX nested query) with multiple records.

I have posted my code below, but I'm open to other suggestions - how can I join the tables to each Desired_Future_State_Metric?

My SQL:

SELECT Desired_Future_State_Metric.Desired_Future_State_ID
,Desired_Future_State_Metric.Metric_ID
,Desired_Future_State_Metric.Target_Value
,Desired_Future_State_Metric.Target_Date
,Metric.Description
,Metric.Unit
,Metric_Value_Measurement.Measurement_Date
FROM (
        (
        Metric INNER JOIN Desired_Future_State_Metric 
            ON Metric.ID = Desired_Future_State_Metric.Metric_ID
        ) INNER JOIN Metric_Unitvalue ON Metric.ID = Metric_Unitvalue.Metric_ID
)
INNER JOIN Metric_Value_Measurement ON (
    Metric.ID = Metric_Value_Measurement.Metric_ID
    AND (
        SELECT MAX(Measurement_Date)
        FROM Metric_Value_Measurement
        WHERE Metric_ID = Metric.ID
        ) = Metric_Value_Measurement.Measurement_Date
    )
GROUP BY Desired_Future_State_Metric.Desired_Future_State_ID
,Desired_Future_State_Metric.Metric_ID
,Desired_Future_State_Metric.Target_Value
,Desired_Future_State_Metric.Target_Date
,Metric.Description
,Metric.Unit
,Metric_Value_Measurement.Measurement_Date;

The structure of my tables:

Metric
----------
ID
Description

Desired_Future_State_Metric
----------
ID
Metric_ID
Desired_Future_State_ID
Target_Value
Target_Date

Metric_Value_Measurement
----------
ID
Metric_ID
Measurement_Date
Measurement_Value

EDIT: It's probably worth mentioning that I'm trying to do this join on 2 columns to avoid using a subreport (which would be very challenging in the particular report i'm running). As such, I need to join on the second column because the 'master' table - Desired_Future_State_Metric - doesn't have a direct relationship with Metric_Value_Measurement (it is related through Metric).

EDIT 2: To clarify, based on Viking's comments - I am trying to get the most recent Measurement_Value for each Desired_Future_State_Metric. Most recent in this case is defined by the date of the measurement, not the highest ID.

Upvotes: 0

Views: 1527

Answers (1)

adlee-dev
adlee-dev

Reputation: 151

EDIT - Added Metric_Value_Measurement to join expression to get Measurement_Value in query results.

What it seems like to me is that you want to connect the most recent measurement for a particular Metric_ID back to Desired_Future_State_Metric table (if that's not the case, what follows may or may not be helpful).

One way to do this is to use a subquery in the join expression for your query that selects the Metric_ID associated with the most recent Measurement_Date. That subquery would look like this:

SELECT Metric_ID, Max(Measurement_Date) As MaxDate
FROM Metric_Value_Measurement
GROUP BY Metric_ID

Your query with the subquery in the join expression would look like this:

SELECT Desired_Future_State_Metric.Desired_Future_State_ID
,Desired_Future_State_Metric.Metric_ID
,Desired_Future_State_Metric.Target_Value
,Desired_Future_State_Metric.Target_Date
,Metric.Description
,Metric.Unit
,Metric_Value_Measurement.Measurement_Value
,Metric_Value_Measurement.Measurement_Date
FROM (((Metric
INNER JOIN Desired_Future_State_Metric 
ON Metric.ID = Desired_Future_State_Metric.Metric_ID)
INNER JOIN Metric_Unitvalue ON Metric.ID = Metric_Unitvalue.Metric_ID)
INNER JOIN Metric_Value_Measurement ON Metric.ID = Metric_Value_Measurement.Metric_ID)
INNER JOIN (SELECT Metric_ID, Max(Measurement_Date) As MaxDate FROM
Metric_Value_Measurement GROUP BY Metric_ID) As S
ON Metric.ID = S.Metric_ID
ORDER BY Desired_Future_State_Metric.Desired_Future_State_ID
,Desired_Future_State_Metric.Metric_ID
,Desired_Future_State_Metric.Target_Value
,Desired_Future_State_Metric.Target_Date
,Metric.Description
,Metric.Unit
,Metric_Value_Measurement.Measurement_Date;

Some notes:

  1. I changed the GROUP BY clause in the main query expression to an ORDER BY clause. You can only use GROUP BY with aggregate functions as in the subquery S. You would have encountered another syntax error were it not for the issue with your join expression.
  2. If you think you might use the subquery above outside this query, you could save it as its own query and use it in this or other query expressions like any other Access query or table.

Upvotes: 1

Related Questions