Order By Question (1 Viewer)

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
My subform doesn't sort the way I want. It was created with much help form this forum.
To make it easier for me I attached the pertinent objects.
As you can see the subform fsubWeeklyAgenda orders by date fine, but I want to order by the Reminder field also.
You canb see this in each days list.

Any help will be appreciated,
James
 

Attachments

  • OrderBy.accdb
    768 KB · Views: 312

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,037
So this is your query ATM
Code:
SELECT tblReminders.ReminderDate, tblReminders.ReminderID, tblReminders.ReminderTypeID, tblReminders.ReminderTime, tblReminders.Reminder, (SELECT Count(ReminderID) +1 As HowMany FROM tblReminders AS Dupe WHERE Dupe.ReminderID> tblReminders.ReminderID AND Dupe.ReminderDate= tblReminders.ReminderDate) AS RankByDay
FROM tblReminders
WHERE (((tblReminders.ReminderDate)<Date()+7))
ORDER BY tblReminders.ReminderDate;

Add the reminder field to the query ORDER BY ?

TBH I seem to recall seeing this form before, and thinking I would prefer it sorted by time of reminder?
 

isladogs

MVP / VIP
Local time
Today, 09:06
Joined
Jan 14, 2017
Messages
18,186
Thought I recognised this 😃
Have you tried adding Reminder as an extra sort field in the query used as record source for the subform?

EDIT: Sorting alphabetically may mean altering the RankByDay field in the query.
I can look at it later but doing that may possibly be a bit more tricky
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,357
Hi James. Does this help?
 

Attachments

  • OrderBy.zip
    56 KB · Views: 298

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
When I sort by Reminder on my app it looks like this:

Untitled.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,037
Yes, you need to then change your conditional formatting for the date field?, so it appears in the correct place?
 

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
Yes. But when I add the sorting to Reminders it looks like the above screen shot.
Gasman, change the for the date field to what?
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,037
Look at theDBguy's upload.
 

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
It seems to be ok now. Not sure why it didn't work when I applied the Reminder sort.
Also, in my upload I deleted many of the records in tblReminders and left only reminders within the next 6 or 7 days. The
tables in the upload were imported directly (not linked).
When I opened theDBguy's upload and deleted the 2 tables and reimported them but linked them.
That's when it didn't sort properly as per my image.
So, I created a new database and linked my tables and then imported theDBguy's upload.
And it worked. Not sure what happened before but all is well now.

Thanks,
James
 

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
I spoke too soon.
It still isn't sorting properly.
It's putting Bu... before Ba...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,357
I spoke too soon.
It still isn't sorting properly.
It's putting Bu... before Ba...
Can you post the entire data set? (Or at least enough to see all possible problems.)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,357

Attachments

  • OrderBy (2).zip
    57.3 KB · Views: 153

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
Here's all the records in tblReminders. Only difference is that in my working db the tables would be linked as apposed to embedded.
The first 3 records in fsubWeekleyAgenda are scheduled for Mon Sep 27.
 

Attachments

  • OrderBy.accdb
    992 KB · Views: 209

isladogs

MVP / VIP
Local time
Today, 09:06
Joined
Jan 14, 2017
Messages
18,186
Try changing the subform properties as shown as both sorting and filtering are done via the QUERY

1632653718219.png


I just added a few test records and got this. I've made the rank by day order visible for testing

1632653780021.png


If you still get incorrect sorting then, as I mentioned back in post #3, the RankByDay function will need modifying
 

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
Didn't help.
I'm assuming you changed the schedule for my first 3 records on the subform from Mon to Sun?
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,037
Is this what you are after?

1632656508560.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,037
Did as Colin has been telling you, you need to alter the calculation for RankByDay ?

Try this as the query and make sure no sort order is on the form.

Code:
SELECT tblReminders.ReminderDate, tblReminders.ReminderID, tblReminders.ReminderTypeID, tblReminders.ReminderTime, tblReminders.Reminder, (SELECT Count(ReminderID) +1 As HowMany FROM tblReminders AS Dupe WHERE Dupe.Reminder < tblReminders.Reminder AND Dupe.ReminderDate= tblReminders.ReminderDate) AS RankByDay
FROM tblReminders
WHERE (((tblReminders.ReminderDate)<Date()+7))
ORDER BY tblReminders.ReminderDate, tblReminders.Reminder;
See if you can understand what has changed?

What was the original name of this DB?, as I seem to recall I changed the download version to be sorted by time within day, after downloading it in the original question?
 

JamesJoey

Registered User.
Local time
Today, 05:06
Joined
Dec 6, 2010
Messages
608
That seems to have done the trick.
As far as differences between the old and new, (forgive me if I don't use the proper terminology)
The WHERE clause is different.
WHERE Dupe.Reminder as opposed to WHERE Dupe.ReminderID.
AND instead of greater than/less than.
Not sure what the term for DUPE would be (Clause?).
Those are the only differences I can see without getting as head ache.

SQL was never my strong point if I have any.

But seems ok now.
 

Users who are viewing this thread

Top Bottom