Query to sort by season + year

[Bean Machine]

Hi All.

I am building a database that houses my grades with the intention of displaying my average grade per semester. The problem is that my field "fld_Smst". The data stored in this field goes by season first, then year, so "Fall 2018" or "Winter 2019" for example. What I would like to do is build a field in a query that essentially sorts the "fld_Smst" field to make sense, i.e., "Fall 2019" should come before "Winter 2019" and "Winter 2019" should come before "Spring 2019". I have attached an image showing how it currently sorts without a sorting field built into a query. Any help would be greatly appreciated as my brain is running on fumes after exams.

 

[XPS35]

There is a neat way and a less neat way. To start with the latter, sort by:

Right([fld_Smst],4), IIf(Left([fld_Smst],1)="S",1,IIf(Left([fld_Smst],1)="F",2,3))

A neater way requires a bit more work. Split the field into two: year and semester. Create a table in which you store the permitted values for semester (spring, autumn, winter). Also add a sort order field to that table to be able to sort the semesters correctly.

 

[MajP]

Make table
tblSemesters
--SemesterName (primary key)
--SemesterYear
--Season
--Sort

example
Fall 20018 2018 Fall 1
Spring 2019 2019 Spring 2

Now join by SemesterName to fld_Smst
(my assumption is you have many records with the same Smst value)

With that now you can sort correctly on sort. Filter by year, filter by season, etc.

 

[Pat Hartman]

I would not include school year in the lookup table. My table would be
tblSemesters
SemesterID (1,2,3, or 4)
SemesterName (Fall, Winter, Spring, Summer)

In your table, you would have the school year plus the SemesterID. Since there are only four values, as long as they are assigned as I indicated, your data will appear in logical order by school year.