Crosstab

[lordrom1]

Hi, I have run a regular crosstab query, but it returns blanks where no event exists, and I simply want a list (horizontal) of data that is currently vertical.

This is the data:-
"action_id" "game_id" "status_time"
"1" "1" "4"
"2" "1" "17"
"3" "1" "41"
"6" "1" "53"
"16" "5" "3"
"17" "5" "27"
"33" "8" "84"
"36" "9" "47"
"46" "11" "51"
"47" "11" "63"
"56" "13" "12"
"59" "13" "55"


My ctb query returns all headings for status_time 1-90, so 90 cells, of which only a few have a number in.
eg for matchId 1 I get
1, 0, 0, 0, 4, 0 etc. I just want the data collapsed to show only values that exist.

I would like query to return
"game_id" "status_time", "status_time1", etc
1, 4, 17, 41, 53
5, 3, 27
8, 84
9, 47
11, 51, 63
13, 12, 55

I hope someone can help.

John

 

[JHB]

You need to create a table (like a crosstab table) on the fly by code.
First find the "game_id" which has the most "status_time", from your data is it "game_id"=1 which has 4 numbers.
So your table need to have 5 fields.
Use a for next loop to put in the different fieldnames, ("status_time", "status_time1", etc), in a string variable from where you can create the table.
After that use a recordset to put in the values in the right field, (use a loop).

 

[lordrom1]

thanks for that, but way out of my league. i wouldn't really know where to start.

i'll just write a routine in vba to do it.

thanks again.

 

[JHB]

thanks for that, but way out of my league. i wouldn't really know where to start.

Post you database (zip it because you don't have post ten post yet).

 

[lordrom1]

attached is one of the tables where i need this conversion done.

many thanks

 

[JHB]

Open the only form in the attached database, click the button and then open the table "Temp_Statustime".

 

[lordrom1]

Many many thanks JHB.

Works a little slower on the full database of 800,000 events (max count =30), but works perfectly, and speed is not the issue here.

Truly grateful.

Thanks again

 

[JHB]

You're welcome, luck with your project.
Yes it is time consuming to put 800,000 events in place by a recordset, but at moment it is the best idea I can get.