Sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I just starting using Access 2003 version. In the database, there are the
following columns: Year, Month, Entered, Reviewed, Approved. I need to sort
by the Year (Des) first, then the month, BUT in the correct order: Jan, Feb,
Mar...etc. I keep getting the year correct but months are jumbled.
Help....I've been working on this for 8 hours.
 
Typically, you would have ONE field with a date entered. Is there a reason
you have year and month as separate fields? What are these storing? Are
they the year and month the record was "entered"? If so, you have redundant
data.

If you can store a proper date, then in your queries, reports, and forms you
could still display this date as MMM-YY format - or whatever format you
desire.

If you work with real date fields, sorting will be much easier since you can
pull out the month number (01-12) and sort by that. If you are storing a
text field (Jan, Feb, etc.) Access has no way to know that Jan comes before
Feb.

You can still accomplish your sorting even with your current data structure,
but before we go into that, let's confirm that you can't simply normalize
your data. If you can, then this will be much easier.
 
Yes, it is requested to have the "Year" as a header then list that year's
months in order by Name opposed by the month's number. Yes, the year and
month for the data, (ex. 2005 - Header, then January, April, September and
November; then the next year, 2004 header, January, April, May, September,
October and November).


Thanks
Ashelyn
 
One way to handle this is to add a table to your database that has two
fields in it - month name and the month number.

Now you can add this table to a query against your existing table and sort
by the month number. Of course, there will be problems with misspelled
month names or abbreviated month names.

Another way that might work would be to use the following calculated field
for sorting in a query

Field: DateValue( "1 " & [Month] & " " & [Year])



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Sample of Report: For the year of 2006

2006

February Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

November Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

April Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

November Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

2005

November Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

January Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

April Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX

September Location:XXX Complied by:XXX Date: 00/00/00
Reviewed by: XXX Reviewed Date: XX/XX/XX
Aprroved by: XXX
 
I just starting using Access 2003 version. In the database, there are the
following columns: Year, Month, Entered, Reviewed, Approved. I need to sort
by the Year (Des) first, then the month, BUT in the correct order: Jan, Feb,
Mar...etc. I keep getting the year correct but months are jumbled.
Help....I've been working on this for 8 hours.
Click to expand...

Don't confuse data PRESENTATION with data STORAGE. They are different
tasks with different requirements!

You can *store* the date as a Date/Time field; it will *sort*
chronologically. But you can *display* it on your Form or Report
grouped and sorted by year and month, displaying the year and month
name if you wish. It is NOT necessary to store the text string
"January" in your table, nor to expect it to sort before the text
string "February"!


John W. Vinson [MVP]
 
Thank you John,

That answered my question. I did confuse the presentation with storage.
Thank you, than you!

Ashelyn
 
Back
Top