MS Access - return last record?

Question

Hi! I need to return the last record in a simple table in MS Access. I have a form that's pulling certain values from the table, and I need it to get the physically last record.
Is there a function for that?

Answer 1

This is relatively simple to do and can be done one of two ways. The first way requires you to sort a table and by find the last you are finding the end of a DECENDING sort. You can then use a SELECT TOP 1 * to return the first record of the decending sort. Of course this requires sorting the table.

The second method allows you to find the last record of an unsorted table. The only requirement is that you have a single field PRIMARY KEY like an autonumber RecID field ect...


First create a summation or total query on the recID field.
Do this by adding the table in the query designer and clicking on just the recID. Then on the tool bar click on the sigma (Backwards looking bent E). This converts the query to a total and adds another line with a drop down box under the field. From the drop down box you can select from several math functions, SUN,AVG ect...
But most importantly you can select FIRST or LAST.

Select LAST and run the query . it will return the recID of the last record in the table. Save this query as Q_LastRecord_myTable

now make a second query:

This query will return all of the fields of the last record. It will use the results of the LastRecord query yourjust created to extract that record

So when you create this new query inm the designer add the table and the query LastRecord. Then creat a join between the recID fields in the table and te query and return all fields from teh table.

Run this query and you will have the last record of the table. (unsorted)

Answer 2

Finding records based on the order in which they were entered involves two things - sorting the records in natural order, and optionally, limiting the view to display only the records that were first or last. When records are sorted in natural order, the records entered first appear at the top and the records entered most recently appear at the bottom.

Answer 3

I will ask my friend who is an MS Access programmer and post the anwer if he can tell me more then I know. You might be able to use the help in MS access as I use Access for a few things from work. It might be a function or might be a macro I have to wait to find out from my techie friend and will post once I hear back from him.

Answer 4

pick * from Transtable the position pk=(pick max(Transdate), pk from TransTable); only replace the pk because the team of fields that uniquely identifies the line. sounds like it truly is client# and TransDate are your unique identifiers. Eric