CODEWITHSUNDEEP
ms-accesslimit
bullfrog97
bullfrog97

Reputation: 53

Access Query Limit From Another Query

I am trying to get a query to populate claims that are above an upper limit that is specified in another query. The claims from query 1 and the upper limit in query 2 are related by part numbers. the below pictures will give a better visual rep.

enter image description here

enter image description here

Edit:If the claim field for month is less than the upper limit then I don't want it show in the query, but each upper limit is different for each part number.

Upvotes: 0

Views: 54

Answers (1)

mazoula
mazoula

Reputation: 1321

enter image description here enter image description here

enter image description here

Here is the sql:

SELECT Claims.PartNo, Month([Claims].[ProdYM]) AS [Month], Year([Claims].[ProdYM]) AS [Year], Sum(Claims.Claims) AS SumOfClaims
FROM UpperLimits INNER JOIN Claims ON UpperLimits.PartNO = Claims.PartNo
GROUP BY Claims.PartNo, Month([Claims].[ProdYM]), Year([Claims].[ProdYM])
HAVING (((Sum(Claims.Claims))>=Max([UpperLimits].[UpperLimit])));

Result:

enter image description here this is a totals query so upper limit needs to have a summary function. Max was easiest to find but first and last will work if you find them.

Upvotes: 1

Related Questions