CODEWITHSUNDEEP
sqlms-access
Gopinath
Gopinath

Reputation: 1

MS-Access query on Min(Fields) based on criteria

Please help me to construct an SQL query to find minimum amt in field AMT based on least date and Name : I want to populate field [Min amt] in an ACCESS Table which is based on least date against Name. For example name A has least date 4/9/17 and its amt is 7000 :

Name    Date         Amt        Min Date     Min Amt
A       4/12/2017    $1,000.00  4/9/2017     $7,000.00 
A       4/11/2017    $2,000.00  4/9/2017     $7,000.00 
B       4/10/2017    $3,000.00  4/10/2017    $3,000.00 
B       4/11/2017    $4,000.00  4/10/2017    $3,000.00 
C       4/10/2017    $5,000.00  4/10/2017    $5,000.00 
C       4/11/2017    $6,000.00  4/10/2017    $5,000.00 
A       4/9/2017     $7,000.00  4/9/2017     $7,000.00

Upvotes: 0

Views: 104

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

I would recommend that you not actually create those computed columns. They could easily be invalidated as soon as new data comes in, rendering them useless to you.

Instead, I would recommend just creating a report showing the earliest amounts for each name:

SELECT t1.Name, t1.Date, t1.Amt,
       t2.[Min Date],
       t2.[Min Amt]
FROM yourTable t1
INNER JOIN
(
    SELECT t1.Name, t1.Date AS [Min Date], t1.Amt AS [Min Amt]
    FROM yourTable t1
    INNER JOIN
    (
        SELECT Name, MIN(Date) AS min_date
        FROM yourTable
        GROUP BY Name
    ) t2
        ON t1.Name = t2.Name AND
           t1.Date = t2.min_date
) t2
    ON t1.Name = t2.Name

Upvotes: 1

Related Questions