Finding last record by adding date/time created in report

[Cal S.]

Hello,



I am attempting to use a report to pull the last record generated in a query utilizing the date/time field that has been added to the original table. I think it's rather simple, but I'm not finding too much on it specific enough to be of too much help for this amateur. I'm hoping to stay out of VB code, it's been several years and project deadlines won't allow for even refresher courses. But, if I have to I have to.

A couple questions:
1) How do I format the report's filter to read the date/time column and pull only the latest one.
2) Should it read off of the original table itself or the query that concatenates the table entries?

Any help is appreciated, thanks!

[ranman256]

if you use KEYS, pull the largest key,
if not,
pull the data with the largest date/time stamp.... (sort desc)

[Cal S.]

I understand what I have to do, my lack of knowledge lies in HOW to do it. How does one filter a report so that it shows only the latest result?

Thank you.

[orange]

Why report?
Create a query. In query SQL view

SELECT * FROM yourTableNameHere
ORDER BY  yourDateTimeFieldNameGoesHere  DESC;

[Cal S.]

Why report?
Create a query. In query SQL view

SELECT * FROM yourTableNameHere
ORDER BY  yourDateTimeFieldNameGoesHere  DESC;

But I can't create a query that reads straight from my table, because the report has to read from another query that has concatenated the results of the original table.

Please, if there is a way to do it the way I've described it, show me how to do it that way.

[June7]

A TOP N parameter nested query is one way to pull 'most recent'. Review http://allenbrowne.com/subquery-01.html#TopN

What do you mean by 'concatenated the results'?

[Cal S.]

I have a table that allows the user to pick two options and has an incremental autonumber field (later, I will transfer this to a UI, but for now I'm just trying to get an alpha working). The query I have thus far takes specific letters from the two options and the autonumber and concatenates the results together to form a unique code.

That all works just fine so far. However, I need to make two reports from that same query. One that will print all the records of the query, and one that will print only the most recent record in the query. I have the report that will print all worked out, since it was easier to do. However, I'm having the devil's own time of it getting any resources on how to just show the very last query record created. Do I need to create a separate query that finds the last result of the table, and then create a report from that?

As I said before, I'm really new to Access, so I really would rather stay away from SQL or VB coding unless there is absolutely no other way to do it.

[June7]

An Access query object uses SQL. Switch a query to SQL View to see the SQL statement. What you want can be done with a nested query as shown in the referenced link. Otherwise, might be possible use domain aggregate functions in a query but that gets even trickier.

[Cal S.]

An Access query object uses SQL. Switch a query to SQL View to see the SQL statement. What you want can be done with a nested query as shown in the referenced link. Otherwise, might be possible use domain aggregate functions in a query but that gets even trickier.

Ok, I think that's working, thank you!

Ran into another problem, however. Since it is an alphanumeric code, Access wants to order it alphabetically instead of numerically. Is there a way to tell Access to sort it by the number portion (last 6 characters) instead of by the first three letters? The code is formatted like this: LLL-######.

EDIT: Never mind, I think I got it. All I had to do was add "Last([concatenate expression])" and it works like a charm. Thank you for your assistance, everyone!