Crosstab sorts alphanumeric not chronological 1

[kbestle]

I have created a crosstab query with columns using a date field. I have selected Date as the grouping. When opening the query it is sorted alpha numeric instead of by date. The data I will be looking at is dynamic so setting fixed columns is not an attractive solution.
TIA
Kent

 

[Isadore]

K; you should be able to control your sort order in the query grid. Is this the problem?

 

[kbestle]

Setting the sort order in the query grid makes no difference. Does it for you?

 

[Spellman]

Is the data type of the date field a string by any chance?? Mark

The key to immortality is to make a big impression in this life!!

 

[kbestle]

The data type is a date/time field.

 

[Isadore]

k: move your criteria to a grid not shown in the results, that is, set your Row, Column, Value and the create a Date "Where" field, and de-select it. This way you remove the criteria from the actually cross tab.

 

[JaneInMA]

Format your date as "yy-mm";- suddenly your dates sort by date. Go to the properties of the date make sure that it is not hard coded to look for "jan", "feb" etc. Check the sql to be certain

 

[kbestle]

Everyone keep in mind that this is a "crosstab" query. Formatting to yy-mmm does not change the sort.

 

[Isadore]

K: Not sure exactly why it is not working for you; in any case, its not a WHERE condition you're having problems with but a sorting problem. Generally speaking I just chose the ascending option in the column grid and usually it behaves. Post a bit of your code, will take a look.

 

[MichaelRed]

To sort by the date, you need an expression which will actually return the correct date ordering you desire. Adding the calculated field to the source of the XTab query provides that opportunity. If, for instance, you want to sort / group by month, you would add a field (e.g. MonNum: Month([DagteField]), include it in the report recordsource, and use it in the sorting and grouping for the report. To assure there are no gaps in a sequence of dates, you may need to construct a dummy recordset which specifically includes each date in the desired range and use it in a left join to the XTab query.

This has been covered in some detail previously:

[tab]thread701-106832
[tab]thread181-21333
[tab]thread703-503692

If these suggestions and references are insufficient, try advanced search, keywords [crosstab | date | group] all words, any date to retrieve more threads.


[tab] MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JaneInMA]

Are you sorting by month? if so sorting by yy-mmm would give "03-feb" and by yy-mm give "03-02". The first gets sorted alphanumerically, the second by the month. Any time the date is described by words rather than numbers it sorts it alphanumerically. So format the date as numbers.
Try it, I use this all the time in dynamic monthly crosstab queries.

 

[kbestle]

Isadore,

Here is the SQL for the query.

TRANSFORM Sum(tblDemandAdj.Qty) AS SumOfQty
SELECT tblDemandAdj.Vendor, tblDemandAdj.[Part Number]
FROM tblDemandAdj
WHERE (((tblDemandAdj.ConvertedDate)>Now()+84))
GROUP BY tblDemandAdj.Vendor, tblDemandAdj.[Part Number]
PIVOT Format([ConvertedDate],"mmm/yy"

Kent

 

[JaneInMA]

try this

TRANSFORM Sum(tblDemandAdj.Qty) AS SumOfQty
SELECT tblDemandAdj.Vendor, tblDemandAdj.[Part Number]
FROM tblDemandAdj
WHERE (((tblDemandAdj.ConvertedDate)>Now()+84))
GROUP BY tblDemandAdj.Vendor, tblDemandAdj.[Part Number]
PIVOT Format([ConvertedDate],"yy/mm"

that will sort by date. Your format was forcing the query to sort alphanumerically.

 

[kbestle]

Your query does in fact sort by date. However the column headiings are 03/06, 04/06 etc. How is someone to interpret this? March 6, April 6. You see my problem.
I am beginning to think I am the only person in the 10 years of Access's existence that has wanted to do this, otherwise I would have expected it to be provided for a long time ago. If you can figure out a way to display in the APR/03, May/03 format I will be a happy camper.

Thanks for your help.

 

[JaneInMA]

You have to take the data and clean it up to be more user friendly. It really isnt a big deal to take the data and just change the header row if it is in the right order. I export to excel then change the header row before graphing the results. In fact for my monthly reports I tend to drop the data over the previous data and just extend the date range by one month. It isn't pretty but it is functional and once you do it a few times you know what you are looking at and you just pretty it up.

 

[MichaelRed]

OOPS. Sorry. Bad.

Please actually check hte reference threads previously provided. They do show 'how to'. There is no reasont to do the dance with Excel - and some reason to not engage in it. Since you (Jane)appear to understand the concept of using the formatted date, why did you not go the extra step of having more than one such date field? You can (obviously?) sort by one but display a different one.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places