Solved Sorting Issue in Table (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 02:08
Joined
Oct 10, 2013
Messages
586
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.

1654087615941.png


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.

1654091787271.png


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

1654091972038.png



This is the query that feeds the report above.

1654092052111.png



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

plog

Banishment Pending
Local time
Today, 02:08
Joined
May 11, 2011
Messages
11,613
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:

 

jdraw

Super Moderator
Staff member
Local time
Today, 03:08
Joined
Jan 23, 2006
Messages
15,364
Reports have their own SORT routine so unlikely that the query sort is an issue.
 

Weekleyba

Registered User.
Local time
Today, 02:08
Joined
Oct 10, 2013
Messages
586
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

Banishment Pending
Local time
Today, 02:08
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Today, 02:08
Joined
Oct 10, 2013
Messages
586
I’ll work with it tomorrow and report back.
Thanks for the help.
 

Weekleyba

Registered User.
Local time
Today, 02:08
Joined
Oct 10, 2013
Messages
586
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.

1654191569671.png





1654191615834.png
 

plog

Banishment Pending
Local time
Today, 02:08
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Today, 02:08
Joined
Oct 10, 2013
Messages
586
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.
 

Users who are viewing this thread

Top Bottom