Solved Sorting Issue in Table

[Weekleyba]

My database was working just fine until a week or so ago.
I have a subform that is used to provide project updates and is set to show the latest update comment, as shown below.

The subform works as it should, which provides the last update, number 8 in this case.
The table the feeds the subform, T_Update, does not appear to be correct. See below. Notice the last UpdateID (908) is located at the top of the table when it should be at the bottom after 776.

When I run a report, shown below, it shows UpdateID 776 and not 908.

This is the query that feeds the report above.

Any ideas why the table, T_Update, does not sort correctly?

 

[plog]

First. There is no order in a table. Think of a table as a bucket where you throw in records of data. All the data is just together in there. There's no first record, no last record, no 23rd and when you do grab 1 record there is no next nor prior record. The data in a table/bucket is just in there.

Now, when you pull the data out you can apply order to it. You can pull it out with a form and have it be in a specific order. You can use a query and get a specific order, same with a report. But in that table, its just in the table.

With that said, you threw one of everything at us--table, form, report, query. What's actually the issue? Discard the table, because like I said, it has no order so that is not an issue.

My guess is your ultimate issue is your query. It looks like you are running an aggregate query and don't understand what that means exactly. Here's a good place to start for that:

SQL Aggregate Functions

This tutorial introduces you to the most commonly used SQL aggregate functions including AVG, COUNT, MAX, MIN and SUM functions.

www.sqltutorial.org

 

[jdraw]

Reports have their own SORT routine so unlikely that the query sort is an issue.

 

[Weekleyba]

I may be wrong, and I probably am, but with the totals query there seems to be an issue using a long text field, aka UpdateComment.
I stripped the UpdateComment from the totals query and added it back in with a new standard query. That seemed to work but I don't know why.
Any takers on why that worked?

 

[plog]

No, and I am pretty sure your totals query is still not set up to do what you want. My guess is you want the most recent record from your data. You are not doing that.

1. You should never use "Last" in the Total section of a query. It simply should not be in Access at all because it does not function like people expect it to.

2. "Max" in the Total section doesn't operate just by itself. It's working in conjunction with all the Group By fields. Make a copy of your query and you can play around to see how it does work. In that copy remove every field in the query except UpdateDate with the Max. Run that and you will get 1 record. Then edit it again and add back the ProjectType with Group By under it. You will get more records--one for every ProjectType, keep adding more fields with Group By and your query will return more and more results.

 

[Weekleyba]

I’ll work with it tomorrow and report back.
Thanks for the help.

 

[Weekleyba]

Plog, I stripped the UpdateComment, which was totaled by Last from the query and then created another simple select query, adding the UpdateComment. Thus I'm not using Last anymore. The report then, gets it's data from Q_SearchAll_for_Report. See below.
Does this look correct? It seems to work correctly.

 

[plog]

You've never stated what you hope to accomplish. I've been throwing out guesses based on what I have seen. So, I don't know what "correct" will be.

If it works, then maybe you did it right.

 

[Weekleyba]

The problem was that the report was not showing the latest UpdateComment. So that's what was needing to be corrected.
Thanks for the assist.