Crosstab Query- Can it add the columns

[lewis33]

Hi, apologize in advance if this has been addressed too many tabs. I couldn't see anything in the FAQ's that relate.

I would like to have a crosstab query create results where the list of state counties are the columns. Then various (about 5-6 calculated fields) fields become the rows.

I think the query wizard supplies a way sum the rows.

1. Is it possible to have multiple rows.
2. If so, how can I go about summing the columns that are produced from this.

I'd like to see:

Adams, Allen, Auglaize, Belmont, Brown Total
Claims 5 5 5 5 5 25
prof charges $6 $5 $6 $4 $6 $27 inpat charges $10 $10 $20 $15 $10 $65
Total charges $16 $15 $26 $19 $16 $72
(based on previous two rows)

Thank you- wasn't sure if I could do a combination crosstab and union query or what the best (easiest for me) option might be.

 

[dhookom]

You should be able to create a union query from crosstab results. I would do all of this in a report. There are sample crosstab reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lewis33]

Duane,
Thanks very much. I'll take a look now. Sure this will do the trick.

 

[MichaelRed]

Not as sure as Duane, but I would probably just do an aggregate query with the XTab as the source. Report fields could reasonably reference the Aggregate query for data.





MichaelRed

 

[lewis33]

Duane,
MichaelRed,

Thanks a super ton. Really-
I'm trying to avoid using an outside report generator and this gives me a lot of options.
Love the examples you had Duane. Professionally done to say the least.