Query to find the latest entry in a table

mr_sd

Registered User.
Local time
Today, 19:21
Joined
May 11, 2004
Messages
24
Hi, appologies if this has already been answered but I can't find it.

I need a query to find the last/latest entry into a table by its primary key [PartyID]

Kind regards,

Rich.
 
if your table has its primary key as an incremental autonumber of some description then its gonna be the largest number in that field. So...

SELECT MAX(primarykeyfield) FROM table

But that seems to simple, so i have a feeling your primary key field is something descriptive or cant be used in the manner ive described.

The second option is to add a 'timestamp' field to your table. Access doesn't really have an actual timestamp datatype but you could just use it for time of creation purposes. Then run a similar SQL as above. This doesn't help for existing records however.

Hope the info ive given is accurate, and that its helps.
 
Where would I put this?

In the criteria of [PartyID]?

Kind Regards,

Rich.
 
My primary key is an autonumber althought there are some gaps between numbers
 
Where to find Max()

OK. Open a new query.

In your query goto View, then click on Totals.

Then go back to the query section. Drag and drop your primary key field and any other fields you want to see.

In your primary key field, click where it says "Group By". A dropdown arrow should appear. Click on it, then click on Max.

Then run your query.

That should give you your latest record when you run your query.

Hope this helps,
Gary
 
I don't think that procedure is exactly correct. Let's say you have a sales data table, with these fields:

Autonum
Name
Date
Amount

If you want to find the last entered sale of any particular person, and are going to use the autonum field MAX to do that, you must omit the Date and Amount fields from your query. Otherwise, Access finds that last of each unique entry and may very well show you all sales.

I don't know how to get it to show you the entire record in, let's say, a report... but I sure wish I did.
 
Last record

See if this works:

In the query grid, sort the field PartyID descending.
Then go to the query properties and set Top Values to 1
 
vangogh228 said:
I don't think that procedure is exactly correct. Let's say you have a sales data table, with these fields:

Autonum
Name
Date
Amount

If you want to find the last entered sale of any particular person, and are going to use the autonum field MAX to do that, you must omit the Date and Amount fields from your query. Otherwise, Access finds that last of each unique entry and may very well show you all sales.

I don't know how to get it to show you the entire record in, let's say, a report... but I sure wish I did.
If you're happier using the query grid than SQL, do it this way:
1. Create a query that contains only Automun, and turn this into a max query
2. Save this query
3. Create a new query. Add the max query you just created to the top of the grid and add your sales table.
4. Join the max query to the sales table on the Autonum field
5. Populate the query grid with whatever data fields you want from the sales table
6. Run this as a select query. It will return only one record, the one that contains the maximum Autonum value
 

Users who are viewing this thread

Back
Top Bottom