Combine Subquery (1 Viewer)

[xdenama]

I have a Query "MPFinal" like this,

SELECT ResultMP.MP, ResultMP.Year, Sum(ResultMP.[A+]) AS [A+], Sum(ResultMP.A) AS A, Sum(ResultMP.[A-]) AS [A-], ([A]+2*[A-])/([A+]+[A]+[A-])AS GPS
FROM ResultMP
GROUP BY ResultMP.MP, ResultMP.Year;

How to combine this statement in to one query,

(SELECT top 1 Dupe.GPS FROM [MPFinal] AS Dupe
WHERE Dupe.MP = [MPFinal].MP AND Dupe.Year < [MPFinal].Year ORDER BY Dupe.MP, Dupe.Tahun DESC ) AS GPMP

Thks.

 

[Galaxiom]

First thing I would recommend is getting the special characters out of the field names. They make it confusing to read. Display names should be applied in the report or form.

 

[Uncle Gizmo]

My interpretation of your question is that you want to insert the second SQL statement into the first SQL statement at the point "AS GPMP" but I can't see that in the first SQL statement?

 

[xdenama]

My interpretation of your question is that you want to insert the second SQL statement into the first SQL statement at the point "AS GPMP" but I can't see that in the first SQL statement?

As for now, I have to make two queries to display "AS GPMP". The first query only showed the "GPS" result. From the GPS calculation, I have to make another query in order to display "AS GPMP". Is there any alternative way which only involve one query to display "AS GPMP"?
I have tried my best to insert the second SQL statement into the first SQL statement but i failed.

([A]+2*[A-])/([A+]+[A]+[A-])AS GPS

This is the first SQL statement which display the "GPS" result.

(SELECT top 1 Dupe.GPS FROM [MPFinal] AS Dupe
WHERE Dupe.MP = [MPFinal].MP AND Dupe.Year < [MPFinal].Year ORDER BY Dupe.MP, Dupe.Tahun DESC ) AS GPMP

The "GPS" result is used to display " AS GPMP" result. I hope anyone can help me solve the problem. I really appreciate for your greatest help. Thank you.