Jump to content

SORTING data in ValueLists / lookup fields - [ Specifically - sorting records by


This topic is 5768 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am building a classic school database application for our Horseback riding school. My wife needs me to sort the "[color:blue]CLASS records" by days of week in a predefined (numerical) order - with each class displaying a list of students who ride on a particular day:

=====================================

Monday Class-1 Enrollments:

- Allen

- Betty

- Jan

- etc.....

Tueday Class-1 Enrollments:

- Tim

- Willie

- Sally

- etc...

Tueday Class-2 Enrollments:

- Stacey

- Sharon

- Betty

- etc...

=======================================

To accomplish this, I created a separate TABLE called "[color:blue]DAYSOFWEEK" with the following fields:

[color:green]

  • DayNumber
  • Day

(Obviously there are 7 total records in this table:

-------------------------------------------------

[color:green]Record #1: DayNum=1 Day=Monday

Record #2: DayNum=2 Day=Tuesday

Record #3: DayNum=3 Day=Wednesday

Record #4: DayNum=4 Day=Thursday

Record #5: DayNum=5 Day=Friday

Record #6: DayNum=6 Day=Saturday

Record #7: DayNum=7 Day=Sunday

My next goal is to allow the user to assign this value pair to a [color:blue]CLASS Record using a VALUELIST lookup. Thus, I created a new value list called "C[color:blue]LASSDAY".

-------------------------------------------------

VALUE LIST: "CLASSDAY"

"DAYSOFWEEK::DAY"

and

"DAYSOFWEEK::DAYNUM"

-------------------------------------------------

CLASSES RECORD:

ClassName: (field-1)

ClassDay: (field-2: Pop-Up Menu: ValueList: CLASSDAY)

-------------------------------------------------

[color:red]ISSUE: The Value list look up feature works correctly, however, I am not able to sort all the CLASS records by their repective ClassDay values.

e.g., (In this predefined numeric order):

1 Monday, 2 Tuesday, 3 Wednesday, 4 Thursday, 5 Friday, 6 Saturday, 7 Sunday

-------------------------------------------------

I tried to make this a straigtforward as possible. However, is this example too comlex to undestand without acutually seeing the database file?

Your suggestions would be most appreciated....!

Link to comment
Share on other sites

It's not clear where you're getting stuck, but you should be able to use a columnar list with a sub-summary part by DayNum and then sort by the related DayNum and ClassName.

This is a bit confusing, however:

My wife needs me to sort the "CLASS records" by days of week - with each class displaying a list of students.

A Class table should be just the information about the Class, when and where it meets, who the teacher is, what the subject is, etc. Student data should be in a separate Student table (or Enrollment table) that's related to Class. With this, the class list report would be run out of the Student (or Enrollment) table, showing sub-summary parts for both DaysOfWeek::DayNum and Class::ClassName, with the Student Name in the Body part.

Link to comment
Share on other sites

I am not too sure what your question is, either.

If you define a value list of custom values:

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

and your records have a WeekDay field (text) containing one of these values, you can sort records by custom order based on the value list. That is one of the options in the Sort dialog. No table or conversion to numbers is required.

This is suitable for a FIXED weekly schedule. If a schedule is prepared separately for each week, then you'd simply use the actual dates.

Link to comment
Share on other sites

This topic is 5768 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.