CODEWITHSUNDEEP
ms-accessms-access-2010
Daniel Jurishi
Daniel Jurishi

Reputation: 1

Access Select Max date with corresponding records

I am trying to create a query that looks at the most recent date and return the corresponding values. Using the max function works only if I have the hose part no and max inspection columns and nothing else. Once I add in the scrap amount columns it duplicates the hose part no column. I only want one single record per hose part no.

Below is an example of what is going on:

Hose Part No Max Inspection Date scrapamt1 scrapamt2 scrapamt3 scrapamt4

enter image description here

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 5032

Answers (2)

AHeyne
AHeyne

Reputation: 3455

If I understand you correct, this is what you want:

Select * From Table1 Inner Join
(Select Table1.[Hose Part No], Max(Table1.[Max Inspection Date]) As [MaxOfMax Inspection Date] From Table1 Group By Table1.[Hose Part No]) As MaxValues
On Table1.[Hose Part No] = MaxValues.[Hose Part No] And Table1.[Max Inspection Date] = MaxValues.[MaxOfMax Inspection Date]

A subquery (named MaxValues) is used to determine the maximum Max Inspection Date per Hose Part No and will be joined to the same table.

Because you also didn't mention the table Name, I choosed Table1.

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

This appears to be working -

tblReleaseNo: 

| Release No | Hose Part No | Release Date | Due Date   |
|------------|--------------|--------------|------------|
| 1          | Hose 1       | 01/01/2018   | 01/01/2018 |
| 2          | Hose 1       | 01/01/2018   | 01/01/2018 |
| 3          | Hose 2       | 01/01/2018   | 01/01/2018 |
| 4          | Hose 2       | 01/01/2018   | 01/01/2018 |
| 5          | Hose 3       | 01/01/2018   | 01/01/2018 |

NewScrapTble: 

| Release No | Inspect Date | scrapamt1 | reason1 | scrapamt2 |
|------------|--------------|-----------|---------|-----------|
| 1          | 01/12/2018   | 10        |         | 15        |
| 2          | 12/12/2018   | 15        |         | 18        |
| 3          | 01/07/2018   | 12        |         | 12        |
| 4          | 01/08/2018   | 14        |         | 200       |
| 5          | 01/03/2017   | 22        |         | 20        |

SQL: 

SELECT  [Hose Part No]
        , MAX([Inspect Date]) AS Inspect
        , LAST(scrapamt1) AS Amount1
        , LAST(scrapamt2) AS Amount2
FROM    (
        SELECT  [Hose Part No]
                , [Inspect Date]
                , scrapamt1
                , scrapamt2
        FROM    tblReleaseNo INNER JOIN NewScrapTble ON 
                    tblReleaseNo.[Release No] = NewScrapTble.[Release No]
        )
GROUP BY [Hose Part No]

Final Table: 

| Hose Part No | Inspect    | Amount1 | Amount2 |
|--------------|------------|---------|---------|
| Hose 1       | 12/12/2018 | 15      | 18      |
| Hose 2       | 01/08/2018 | 14      | 200     |
| Hose 3       | 01/03/2017 | 22      | 20      |

Note: I'm not 100% sure that LAST is correct, it is pulling the correct amounts but not sure if it will trip up on some records?

Upvotes: 0

Related Questions