Sorting data in a certain way

[Blackberry]

Hi All

Wondered if you could help. I'm using SQL queries in Access 2003 and I
can't fathom the below.

My table data is defined as follows:

TERMID SHORTNAME YEAR
0 Sep 2007
0 Sep 2008
1 Jan 2008
2 Apr 2008
3 Jul 2008
1 Jan 2009
2 Apr 2009
3 Jul 2009

The TERMID relates to the shortname in that 0 = Sep, 1 = Jan, 2 = Apr and 3
= Jul.

My current query is as follows:

select distinct l.TERMID, t.SHORTNAME, l.[YEAR] from LEVELS l, TERMS t where
l.TERMID=t.TERMID ORDER BY 3,1

This is producing the above results, but I need the results to come out as
follows:

TERMID SHORTNAME YEAR
0 Sep 2007
1 Jan 2008
2 Apr 2008
3 Jul 2008
0 Sep 2008
1 Jan 2009
2 Apr 2009
3 Jul 2009

Basically the year sorts as is, but the term id / shortname needs to sort in
the calendar format of Jan, Apr, Jul & Sep for the respective year (which is
1,2,3,0 in my termids). Do you know of any way I can do this?

Thanks

 

[Jeff Boyce]

That seems like a lot of duplication...

Why store BOTH your "0" and "Sep", when it sounds like you are using "0" to
represent "Sep"?

In fact, why store three separate fields, when you could store a single
date/time value and use Format() to change how you display it in queries,
forms and reports?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Blackberry]

Hi Jeff

Thanks for the reply.

The termid is my uid, but I need to keep the term name as well because
certain users want to dictate what the terms are, eg one user might want Dec
for term 0.

Any ideas on the sort?

Thanks

That seems like a lot of duplication...

Why store BOTH your "0" and "Sep", when it sounds like you are using "0" to
represent "Sep"?

In fact, why store three separate fields, when you could store a single
date/time value and use Format() to change how you display it in queries,
forms and reports?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John W. Vinson]

Basically the year sorts as is, but the term id / shortname needs to sort in
the calendar format of Jan, Apr, Jul & Sep for the respective year (which is
1,2,3,0 in my termids). Do you know of any way I can do this?

I agree with Jeff's suggestion, but you could use

SELECT <whatever>
FROM yourtable
ORDER BY ([YEAR] - ([TERMID] = 0)), TERMID;

The odd expression will return -1 if TERMID is 0, 0 otherwise, so SEP 2007
will sort with 2008 in TERMID order.

John W. Vinson [MVP]

 

[Pieter Wijnen]

Store it as a whole date
ie

TERMID TERMSTART
0 2007-09-01

& use Format(TermStart,"mmm") to display the month abbr

HTH

Pieter

 

[John W. Vinson]

The termid is my uid, but I need to keep the term name as well because
certain users want to dictate what the terms are, eg one user might want Dec
for term 0.

Any ideas on the sort?

If you want the terms to sort chronologically, then the simplest way would be
to store the start of the term as a Date/Time field.

John W. Vinson [MVP]