CODEWITHSUNDEEP
sqlselectdistinct
Mig.C
Mig.C

Reputation: 3

Return multiple columns with 3 distinct fields in SQL query for Access DB

I am trying to make a query that returns multiple fields, keeping the first 3 as distinct columns and returns values for the last modified date. Some of the variables in the query fields should come from more than one table and one of them has a True/False criterion too. The three 3 distinct fields are needed because the combination of these is associated with the other returning parameters.

The tables look roughly as follows...

Table a: 

 ID | Sc | Country | TechID | VarA | ... | VarX(T/F) | LastModified
  1 |  1 |    AA   |    1   |  x   | ... |     T     | 1-1-2017
  2 |  1 |    AA   |    1   |   z  | ... |     T     | 1-1-2017
  3 |  1 |    AA   |    2   |    y | ... |     T     | 1-1-2018
  4 |  1 |    AB   |    1   |   u  | ... |     T     | 1-1-2017
  5 |  2 |    AB   |    2   |   v  | ... |     T     | 1-1-2018
  6 |  3 |    AB   |    1   |   w  | ... |     F     | 1-1-2018

Table b:

TechID | TechName | Categ | Units
   1   |   Tech1  |  Cat1 | M
   2   |   Tech2  |  Cat2 | N
   3   |   Tech3  |  Cat3 | P

The idea is that the query returns something like this (when the T/F criterion is met). Where the combination of Sc-Country-Tech shows up only once, with the last modified having presedence:

Sc' | Country' |TechName'| Units | Cat | VarA... | LastModified | 
 1  |    AA    |    1    |  ...  | ... |   ...   | 1-1-2018
 1  |    AB    |    2    |  ...  | ... |   ...   | 1-1-2017
 2  |    AB    |    1    |  ...  | ... |   ...   | 1-1-2018

So far I've tried a few SQL lines to no avail. First, with Select DISTINCT but the option was too "all inclusive". 

SELECT DISTINCT a.Sc, a.Country, b.TechName, b.Units, b.Cat, a.VarA,..,a.VarX, Max(a.LastModified) AS MaxOfLastModified
FROM a INNER JOIN (b INNER JOIN a ON b.TechName = 
a.TechID) ON b.Cat = a.TechID
GROUP BY a.Sc, a.Country, b.TechName, b.Units, b.Cat, a.VarA,..,a.VarX
HAVING (((a.VarX)=True));

Also tried this but it prompts errors related to aggregate functions:

SELECT a.Sc, a.Country, b.TechID, b.Units, b.Cat, a.VarA,..,a.VarX, Max(a.LastModified) AS MaxOfLastModified
FROM a INNER JOIN (b INNER JOIN a ON b.TechName = 
a.TechID) ON b.Cat = a.TechID
GROUP BY a.Sc, a.Country, a.TechID
HAVING (((a.VarX)=True));

Any thoughts/suggestions on how to go about this?? Any pointers to previous related answers are also much appreciated.

Thanks in advance! :)

EDIT (2017.09.29):

This certainly cleared things up a bit!

I managed to get the query going with some of the fields, only when calling fields from a single table with the following:

SELECT a.Sc, a.Country, a.Tech, a.LastModified, a.VarA
FROM a INNER JOIN (SELECT Sc, Country, Tech, max(LastModified) AS lm FROM a GROUP BY Sc, Country, Tech)  AS dt ON (dt.lm=a.LastModified) AND (dt.Tech=a.Tech) AND (dt.Country=a.Country) AND (dt.Sc=a.Sc)
GROUP BY a.Sc, a.Country, a.Tech, a.LastModified, a.VarA, a.VarX
HAVING (((a.VarX)=Yes));

I'm still running into a syntax error on JOIN when trying to add fields from a lookup table using the INNER JOIN command as suggested. The code I tried looked something like:

SELECT a.Sc, a.Country, a.Tech, a.LastModified, a.VarA b.TechCategory
FROM a INNER JOIN (SELECT Sc, Country, Tech, max(LastModified) AS lm FROM a GROUP BY Sc, Country, Tech)  AS dt ON (dt.lm=a.LastModified) AND (dt.Tech=a.Tech) AND (dt.Country=a.Country) AND (dt.Sc=a.Sc)
INNER JOIN b ON Tech.Category=a.Tech
GROUP BY a.Sc, a.Country, a.Tech, b.TechCategory, a.LastModified, a.VarA, a.VarX
HAVING (((a.VarX)=Yes));

Any additional pointers are much appreciated!

Upvotes: 0

Views: 792

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

Use an aggregate query to get the maximum date for each combination of Sc, Country, and TechID, then use this as a subquery and join it back to tables a and b to get the data in your final query. Something like this:

select
    a.Sc, a.Country, b.TechName,
    b.Units, b.Category, b.Units, a.VarA, a.LastModified
from
    (Table_a as a
    inner join (
        select   Sc, Country, TechID, max(LastModified) as lm
        from     Table_a
        group by Sc, Country, TechID
        ) as dt on dt.Sc=a.Sc and dt.Country=a.Country and dt.TechID=a.TechID and dt.lm=a.LastModified)
    inner join Table_b as b on b.TechID=a.TechID

Upvotes: 1

Related Questions