Crosstab Query Issue

[tmcrouse]

I am trying to create a crosstab query with no values. The value is actual the value of a field. Here is an example of the fields I have: Note I have 6334 distinct rows with various measure, program and states so this is just an example. I tried Crosstab but it does not work how I need. Then tried putting the 6334 rows in Excel and pivot and that layout did not work.

Measure Program State
ADHD OutCall CA
ADHD Behavior CA
ADHD Freestand CA


ADHD OutCall WI
ADHD Behavior WI
ADHD Freestand WI
Depression Outcall CA
Depression Freestand CA
Depression Mailer CA
Depression Freestand WI
Depression Mailer WI


Layout Need

Measure CA WI
ADHD Outcall Outcall
Behavior Behavior
Freestand Freestand
Depression Outcall Freestand
Freestand Mailer
Mailer


Any thoughts?

[June7]

The output makes no sense to me. What are the rules that determine what values goes where?

For instance, why does Depression have only one line and is associated with Outcall and Freestand but not Mailer? Why is there a line that associates Freestand and Mailer? Why: Behavior Behavior; Freestand Freestand? Why is Mailer on a line by itself?

[tmcrouse]

I wished I could paste a layout that would maintain cell integrity on here. Let me try a picture paste and maybe that will help. ugh it does not let me paste a picture. ok let me try an upload of a file. Well that did not work. I am trying to paste the format. I have a table that has:

column(measure)
column(program)
column(state)

If I run a query and output in crosstab so that states all appear across the top. All 51, then measure is the row all down the far left side then I want the program grouped by my measure. The problem is crosstab wants values and the field has the name of the program. I am not sure how to post an example since the .doc with picture I pasted of example output in Excel will not work.

I have Googled all day today trying to find out how to crosstab where my value is the actual program names not numeric values. I can count the programs but that is not the output they want. Darn I wished I could attach an Excel in here. Got picture pasted

[June7]

Can attach Excel or Access or other files. Zip if larger than 500KB - 2MB zip allowed - use Windows Compression.

Can build tables in post in the Advanced editor.

Are CA and WI the only states or do you have all 50 + WDC, Puerto Rico, Guam, Virgin Islands, etc?

Out of the ordinary data manipulation can require the use of VBA and temp tables as well as queries. Review: http://forums.aspfree.com/microsoft-...ry-322123.html

and http://www.datapigtechnologies.com/f.../crosstab.html

[tmcrouse]

Yes all states the overall file is 6446. I am not familiar enough with VBA to figure it out. Right now I have just pasted it all in Excel and manually doing it. Kind of blown away that a crosstab in Excel or Access cannot handle this type of function without VBA code. I don't have time to attempt to figure out code for this as this was thrown on me with all my massive other projects and I was told drop everything do this by EOD Friday. So rather than waste my time trying to fish around for some code I am to row 539 doing it manually. Thanks

[June7]

Crosstab needs another Row Heading. Perhaps the Program can be both Row and Value criteria. This seems to work:

TRANSFORM First(Table1.Program) AS FirstOfProgram
SELECT Table1.Measure, Table1.Program
FROM Table1
GROUP BY Table1.Measure, Table1.Program
PIVOT Table1.State;

[tmcrouse]

Thanks, I will use that and that will be less manual stuff I have to do when I paste to Excel. They want it to group the programs under the measure under the state and now have the measure duplicated. Meaning if ADHD has 4 programs under it, measure shows up 1 time on the left and then the 2nd column the 4 programs under it and block it all and do this freezing of panes in Excel yada yada. But with this transform it will make it far faster than what I am doing with copy paste stuff. Thanks