CODEWITHSUNDEEP
ms-accesssubquery
Cobra
Cobra

Reputation: 29

I Need help in writing a multi-level ms access subquery

I am having hard time to wrap my mind around a multi-level query/sub-query. In short, I a table called REGISTER with 3 fields in it: TransactionId (PK), TransDate (DateTime), Amount (Currency). I need a single query to retrieve the TransactionId where TransDate is the Max and the Amount is the Min qualifying records. For example, if the max of qualifying record returns 5 records with today's date (any future dates is excluded), I would like to know the TransactionId of the lowest Amount within the 5 records returned.

I amble to accomplish this task with two separate query but I am sure it cab be done with a single one.

Query 1 (qryFlag):

SELECT REGISTER.TransDate, REGISTER.*
FROM REGISTER
WHERE (((REGISTER.TransDate)=(
  SELECT Max(t2.Transdate) from REGISTER t2 
  where Transdate <= Date())));

Query 2: 

SELECT REGISTER.TransactionId
FROM qryFlag INNER JOIN REGISTER ON qryFlag.TransactionId = REGISTER.TransactionId
WHERE (((qryFlag.Amount)=(SELECT Min(t2.Amount) from qryFlag t2)));

Upvotes: 0

Views: 54

Answers (2)

dbWizard
dbWizard

Reputation: 132

Try:

SELECT TOP 1 VTID.TransactionID
FROM (
SELECT TransactionID, Amount
FROM Register
WHERE TransDate = (SELECT Max(R.TransDate) FROM Register as R WHERE R.TransDate <= Date())
) as VTID
ORDER BY VTID.Amount

HTH

Dale

Upvotes: 1

forpas
forpas

Reputation: 164099

You need to GROUP BY TransDate to get the min amount and then join to the table:

SELECT r.* 
FROM REGISTER AS r INNER JOIN ( 
  SELECT TransDate, MIN(Amount) AS MinAmount 
  FROM REGISTER
  WHERE TransDate = (SELECT Max(Transdate) FROM REGISTER WHERE Transdate <= Date())
  GROUP BY TransDate
) AS g on g.TransDate = r.TransDate AND g.MinAmount = r.Amount

Upvotes: 0

Related Questions