A Query that only shows the lastest/Last record for each vendor

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
If I had a table with vendor history of each purchase they made (the table has the same customer listed numerous times under VendorName). The table has a "CreatedDate" field that puts the date the record was created in it and the table also has an AutoCount field "ID_NISTSL". So there are two ways to determine which is the last record entered into the table.

Is there a way to query (or only see) the last record (most recent) for each customer?

Thanks!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Re: Access: A Query that only shows the lastest/Last record for each vendor

I would recommend using the date field. If the AutoCount field is an Autonum field, you cannot rely on it always to be increasing (though it usually is, it cannot guarantee it).

Here is SQL code for a query that should do what you want:
Code:
SELECT Table1.*
FROM Table1
INNER JOIN
[COLOR=#0000ff](SELECT Table1.VendorName, Max(Table1.CreatedDate) AS MaxOfCreatedDate[/COLOR]
[COLOR=#0000ff]FROM Table1[/COLOR]
[COLOR=#0000ff]GROUP BY Table1.VendorName) as Lst[/COLOR]
ON Table1.VendorName = Lst.VendorName AND Table1.CreatedDate = Lst.MaxOfCreatedDate;
Just change all references of "Table1" with the name of your table.
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
Re: Access: A Query that only shows the lastest/Last record for each vendor

Thanks, Joe

Wht if I wanted to group by two fields VendorName and CommType? Is it possible to group by more than one?

And can you add Criteria?

WHERE (((tbl_NIS_TSL.VendorName) Like [Forms]![frm_NIS_TSL]![Combo227]) AND ((tbl_NIS_TSL.CommType) Like [Forms]![frm_NIS_TSL]![Combo232]) AND ((tbl_NIS_TSL.Debarred) Like [Forms]![frm_NIS_TSL]![Combo229]) AND ((tbl_NIS_TSL.Approval_Status) Like [Forms]![frm_NIS_TSL]![Combo234]))
ORDER BY tbl_NIS_TSL.VendorName, tbl_NIS_TSL.CreatedDate DESC;
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Re: Access: A Query that only shows the lastest/Last record for each vendor

Sure. You would just first add it to both the SELECT and GROUP BY clauses of the query in blue, and then add another AND to the end of the JOIN to join on this field too.

Note that instead of using an embedded query like this, you can do this in two queries, which allows you to do it all using the Query Builder.

Query 1:
This is your Aggregate Query, where you only select the fields you want to group by and the field you want to aggregate by.
You select those fields, and hit the Totals button (looks like a Sigma).
This adds a Totals row with the phrase "Group By" under all three fields.
Change "Group By" to "Max" under the CreatedDate field to return the latest date for each grouping.
So, when this is finished, you have a listing of every unique Grouping, and the latest date for each grouping.

Query 2:
If you want other fields displayed from your table other than the three fields in the previous query, simply join the original table to Query 1, joining on ALL three fields from Query 1.
Then you can elect to return any fields you want from the original table.

That is what the embedded query I originally created does. It can be done either way.
Once built, you do not have to open Query 1 at all, just Query 2.
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
Re: Access: A Query that only shows the lastest/Last record for each vendor

Thank you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Re: Access: A Query that only shows the lastest/Last record for each vendor

You are welcome.
 

Forum statistics

Threads
1,082,250
Messages
5,364,022
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top