Pulling Last Record

[biggcc]

I'm trying to figure out how I can pull the most recent entry in a table for each of the properties in our database. Let me give you some background. This access database is used to format reports from an SQL database - all of the tables are linked and all of my other reports work great except for the one I'm trying to create from a table that gets updated every day by way of a stored procedure that runs every night on the SQL server. In my mind this shouldn't make a difference and I should be able to select whatever data I want from this table/query based upon whatevery criteria I want. The field in the table I'm trying to use is a "date stamp" of when it was updated via the stored procedure. I want the last entry made for each property so I selected "last" as the criteria which should get me approx. 20 lines - one for each property. But when I use this I still get back multiple entries for each property. This seems like it would be easy to do but everything I try doesn't seem to work.

That's why I've turned to the experts here for help.

Thanks

 

[Moniker]

Umm, the Max() function, perhaps?

 

[The_Doc_Man]

OK, concept-check here.

There IS no last record. More precisely, there is a last record but you cannot know which one it was, with a couple of exceptions.

1. Each record got an incrementing autonumber. In which case you can select the record with the record number equal to the DMAX of that record number of that table.

2. Each record has an externally imposed unique key AND import is in the order of that key. In which case you do #1 but for the DMAX of the real key. Or the DMIN if it was sort DESC.

Otherwise, records in a table have no order. Set theory, on which SQL is based, has no order that was not imposed by the query itself. Without criteria, the last record to be imported exists but you have no way of selecting it unless case #1 or case #2 above applies.

You COULD try to open the table via a recordset in VBA and use .MoveLast on it. In the absence of a key, it would go to the (chronologically) last-entered record. But if there is ANY KEY whatsoever, particularly a prime key, or if you opened the recordset with a query that includes a sorted field, this order is not reliable.

This is an offshoot of an old programmer's rule. Access won't tell you anything you didn't tell it first. So if the order of appearance of records is important, tell Access to track the order via record numbering.

 

[Moniker]

Agreed, Doc Man, but he mentioned he was using a TimeStamp field. The Max on that field would be the most recent. He didn't mention if that's the key or not, but the Max of the date is the most recent TimeStamp written (assuming it includes a date and not just a time).

 

[biggcc]

More Info

Sorry I thought I supplied enough information but after reading it back I can see I missed some things. Moniker is right in that I'm trying to use a date/time field for pulling records and it is not the key field. There is a random id field generated for each entry and that is the key. The access query that I'm attempting to filter is generated from an SQL linked table whos entries are updated every night when the stored procedure is run. However there will be multiple entries made to the table - one for each property in the database (approx. 20) so ultimately I'd like to have a query that just shows those 20 lines instead of the 11k that is normally in the table. I did try MAX and LAST on the date/time stamp but it did not filter it correctly and I still received more information than I should have. I also tried just using yesterday's date as a filter and it did not work. Which is why I'm sort of stumped now. I can't figure out how to filter this down to what I'm looking for. Any ideas?

Thanks,
Chester