CODEWITHSUNDEEP
sqlms-access
nir020
nir020

Reputation: 97

Using Min function in MS access SQL

Below is sql query that i am have created in ms access query. i am trying to use a sub-query to get the minimum outcome date for each young person but i get the error message "you tried to execute a query that does not include the specified aggregate function"

I am aware that there are other ways to produce the data I want but just wanted to know if it is possible to use sub-query to do this

I am aware that there are other ways to produce the data I want but just wanted to know if it is possible to use sub-query to do this 

SELECT A.YPID, A.min_out_date
FROM (SELECT  YP.YPID, min(Outcomes.Outcome_Date) AS [min_out_date] 
FROM YP INNER JOIN Outcomes ON YP.YPID=Outcomes.YPID)  AS A
GROUP BY A.YPID, A.min_out_date;

Upvotes: 0

Views: 50

Answers (3)

Jtpbabyy
Jtpbabyy

Reputation: 1

If you really want to use a subquery (which I don't think is necessary here), you would just group by in the subquery: 

SELECT A.YPID, A.min_out_date FROM (SELECT  YP.YPID, min(Outcomes.Outcome_Date) AS [min_out_date] FROM YP INNER JOIN Outcomes ON YP.YPID=Outcomes.YPID group by YP.YPID) AS A;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You don't need a JOIN at all:

SELECT o.YPID, MIN(o.Outcome_Date) as min_out_date
FROM Outcomes as o
GROUP BY o.YPID;

All the information needed in the result set is in Outcomes.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below -

SELECT A.YPID, min(A.min_out_date) as mindate
FROM 
(
SELECT  YP.YPID, Outcomes.Outcome_Date AS [min_out_date] 
FROM YP INNER JOIN Outcomes ON YP.YPID=Outcomes.YPID
)  AS A
GROUP BY A.YPID

Upvotes: 0

Related Questions