CODEWITHSUNDEEP
ms-access
Sao Tith
Sao Tith

Reputation: 81

MS Access : multiple queries into one table

Okay I'm still fairly new to MS Access, but have got some of the bases down. My next issue is pulling data from two different queries but still needing them to show.

Here's what I have

I have one query with the following information

|   ID Number   |   Points  |

The other query has the following

|   ID Number   |   Points over 1000    |

In this new query I need to do display the following

|   ID Number   |   Points  |   Points over 1000    |   Total Points    |

There's going to be some rows where Points over 1000 doesn't exist and needs to be empty or a 0, but I need the ID Number In Points over 1000 to match and check the ID Number in just the points column.

and in the end add them up in the Points total

I hope that makes sense?

Thanks again

Upvotes: 0

Views: 159

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5809

In theory this Query should work the way you want it to.

SELECT 
    tmpQ.ID, 
    Sum(tmpQ.Points) As ActualPoints, 
    Sum(tmpQ.PointsOver1000) As Over1000,
    [ActualPoints] + [Over1000] As TotalPoints
FROM
    (
        SELECT 
            qryA.[ID Number] As ID,
            Sum(qryA.Points) As Points,
            Sum(0) As PointsOver1000
        FROM
            qryA
        GROUP BY 
            qryA.[ID Number]
        UNION ALL
        SELECT 
            qryB.[ID Number] As ID,
            Sum(0) As Points,
            Sum(qryB.PointsOver1000) As PointsOver1000
        FROM
            qryB
        GROUP BY 
            qryB.[ID Number]
    ) As tmpQ
GROUP BY
    tmpQ.ID;

Where qryA and qryB are the two queries you have that will give you the result of two different Points.

Upvotes: 1

Related Questions