Crosstab Query Help

[tmcrouse]

I have a very large table that I am trying to crosstab and cannot quit get it right. Here is an example of the data I have. There are many more columns with facilities rate information. The largest I have is 15 columns of the rates.


Then for John Hopkins I need to do a query and thinking query by measure and where ever John Hopkins rate falls compared to others in that group they would be rated accordingly

DATA HAVE
JOHNHOPKINS	LEAVERVILLE	OXFORD	market	MEASURE
0.5255	0.4514	0.4375	GA	AWC
0.8138	0.8356	0.8505	GA	AAB 20-24
0.7211	0.7066	0.7283	GA	ADV 11-14
0.6092	0.5981	0.6256	GA	ADV 15-18
0.3317	0.3577	0.3279	GA	ADV 12-14
0.4859	0.4428	0.4995	GA	ADV 2-3
0.7719	0.7509	0.7711	GA	ADV 4-6
DATA NEED
MEASURE	market	FACILITY	RATING	RANKING
AWC	GA	JOHN HOPKINS	0.5255	1 OUT OF 3
AWC	GA	LEAVERVILLE	0.4514
AWC	GA	OXFORD	0.4375
AAB 20-24	GA	JOHN HOPKINS	0.8138	3 OUT OF 3
AAB 20-24	GA	LEAVERVILLE	0.8356
AAB 20-24	GA	OXFORD	0.8505
ADV 11-14	GA	JOHN HOPKINS	0.7211	2 OUT OF 3
ADV 11-14	GA	LEAVERVILLE	0.7066
ADV 11-14	GA	OXFORD	0.7283
ADV 15-18	GA	JOHN HOPKINS	0.6092	2 OUT OF 3
ADV 15-18	GA	LEAVERVILLE	0.5987
ADV 15-18	GA	OXFORD	0.6256
ADV 12-14	GA	JOHN HOPKINS	0.3317	2 OUT OF 3
ADV 12-14	GA	LEAVERVILLE	0.3577
ADV 12-14	GA	OXFORD	0.3279
ADV 2-3	GA	JOHN HOPKINS	0.4859	2 OUT OF 3
ADV 2-3	GA	LEAVERVILLE	0.4428
ADV 2-3	GA	OXFORD	0.4995
ADV 4-6	GA	JOHN HOPKINS	0.7719	2 OUT OF 3
ADV 4-6	GA	LEAVERVILLE	0.7509
ADV 4-6	GA	OXFORD	0.7711

[ranman256]

The DATA NEED table is how your data SHOULD have been stored. (normalized)
The DATA HAVE is what the crosstab would produce.
but since yours is backwards, youd have to make a series of append queries, to put data into a 'report' table.

[tmcrouse]

This is the way the data comes over as the data have. I will just have to manually do it I guess. Thanks

[June7]

This does not call for CROSSTAB. Data is already not normalized and de-normalizing/summarizing data is what CROSSTAB does.

A UNION query can rearrange the data into normalized structure. Then use the UNION query as source for subsequent queries. Such as a query that sorts records by the rating value.

SELECT market, measure, JOHNHOPKINS AS Rating, "JOHNHOPKINS" AS Facilty FROM tablename
UNION SELECT market, measure, LEAVERVILLE, "LEAVERVILLE" FROM tablename
UNION SELECT market, measure, OXFORD, "OXFORD" FROM tablename;

There is a limit of 50 SELECT lines. There is not query builder for UNION, must type into SQL View of query designer.

It's the ranking calculation to show "1 out of 3" that complicates this. If you have up to 15 facility columns to compare, I expect VBA custom function will be involved.

[DrGUI]

See if this does it for you.

It should handle any number of rate columns and will also calculate the ranking against the other rates.