Crosstab Query with TEXT values?

[eepok]

This is almost a cross-forum post. I started a post in the reports forum to see if it were possible to sort/group the data by field values. Everyone knows this can be done row-by-row, but I was looking for a way to do it horizontally as well.


...........[quarter]="Fall".....[quarter]="Winter"........[quarter]="Spring"........[Quarter]="Summer"
2005.....Relevant Fields........Winter Stuff..................Spring stuff................Summer Stuff
|
2006
|
2007

It should look something like that where the years are values of [year] field and the seasonal quarters are values of [quarter] field.

I have been searching for something that will help me do this when I ran across information on the "crosstab" query. This, in principal is what I am looking for BUT I am not dealing with numeric data here. Is there still a away to make a crosstab query/report with text fields where there would normally be numeric data?

 

[ajetrumpet]

This is almost a cross-forum post. I started a post in the reports forum to see if it were possible to sort/group the data by field values.

...........[quarter]="Fall".....[quarter]="Winter"........[quarter]="Spring"........[Quarter]="Summer"
2005.....Relevant Fields........Winter Stuff..................Spring stuff................Summer Stuff
|
2006
|
2007

It should look something like that where the years are values of [year] field and the seasonal quarters are values of [quarter] field.

This is all fine and dandy, but how does your data look now? It's a little bit tough to figure out what you actually need, based on seeing the result, and not the starting point. Got a table, or a picture of the table, or something to post? Maybe you need the MS tranpose function. It is here.

 

[GaryPanic]

errr . spreadsheet ??

 

[eepok]

Table Fields:
Quarter (Fall, Winter, Spring)
Year (2005,2006,2007,...)
Course (Course1, Course2, Course3)
Compensation (Money1, Money2, Money3)
Professor (Prof1, Prof2, Prof3)

Display Goal

-------------------------Fall-------------------------------------Winter------------------
2005______[Professor],[Course],[Compensation]_____[Professor],[Course],[Compensation]
__________[Professor],[Course],[Compensation]_____[Professor],[Course],[Compensation]

2006______[Professor],[Course],[Compensation]_____[Professor],[Course],[Compensation]


As you can see, the column and row headers are values from 2 different fields -- just like in a CrossTab Query, but I want the fields shown to show up, not some mathematical calculation therof (which is all I can seem to get the CrossTab wizard to do).

The Transpose Function you linked is just lightyears beyond me. I have zero code experience. So, if it works, and you know it works, and you're a merciful person, you'd have to show me how to implement it for it to be any use.

 

[eepok]

errr . spreadsheet ??

You know, that's what I told my temporary supervisor, but she REALLY likes this database that's already storing all this data and she wants to be able to push a button and pump out a report as shown above instead of wasting the DB and just copying the info into a spreadsheet.

 

[GaryPanic]

well i think you are getting into the realms of union qry's on this - and i am useless at these - lose the will to live on them - you need a one of the real Gurus to sort this one - Bob , or Rural G possible Doc(to name a few) - but these guys are real busy - .

i am bailing out at this stage cos it makes my head hurt to think about it ...
Good luck
Gary

 

[GaryPanic]

hmm . do you have a sample d/b you can zip up (I am using Access 2000)
I had half a thought - that might work


it involves the properties of the report itself rather than any qry depends on your exp on report properies and getting it to sort on the report itself. rather than a qry - (i might not be explaining myself very well on this)- if you got a sample -i' ll play with it -
but not today or tomorrow - its late here - and I am on a bender (going for a drink) tomorrow night .

regards

 

[eepok]

hmm . do you have a sample d/b you can zip up (I am using Access 2000)
I had half a thought - that might work


it involves the properties of the report itself rather than any qry depends on your exp on report properies and getting it to sort on the report itself. rather than a qry - (i might not be explaining myself very well on this)- if you got a sample -i' ll play with it -
but not today or tomorrow - its late here - and I am on a bender (going for a drink) tomorrow night .

regards

I'll probably purge the data (confidential) and zip it up for ya prob in the morn tomorrow.

Bend well if I don't hear from ya before then.

 

[GaryPanic]

can you put some garbage in just so I have a data to play with ..

 

[eepok]

I would like to thank a greedy hungry crow for my solution. This morning, I was walking to work utterly frustrated that I couldn't think of an elegant solution when I saw a very happy and presumably hungry crow with a dilemma --

The crow stood between 2 worms which were coaxed out of the soil by a sprinkler-soaking. The crow would bounce over to one worm, see the other wriggling away, and bounce over to the other. It wanted both worms at the same time. So what did it do? It clawed at one and picked at one with its beak and pulled the worms together into one meal.

So I sat in my office smashing my head against my Confusion Square, waiting for a solution when I thought about how funny the crow was -- that it didn't understand that it's just smarter to combine 2 problems into one thus requiring only a single solution.

And then it hit me -- combine the [quarter] and [course] fields and reorganize the data in the database. This was I can kill 2 worms with one bird!

There's been a long-standing GUI problem where users would choose a wrong year or quarter when inputting a course assignment thus messing up TWO academic years of data. So this is my over-simplified plan:

Table:
year
prof
compensation
note
Fall Course1
Fall Course2
Fall Course3
Fall Course4
Winter...

You get the idea. Now, once all that data is repopulated, I can group by year, then professor, and get exactly what I was looking for. In addition to a clean report, I will be able to lessen user error because they will be able to enter multiple course assignments per one prof/year combo.

I'm so happy I could cry. No time to cry, though. I have about 600 records worth of data to reorganize. (not too much, but I'm the only working on it)