Crosstab Query 1

[Lynne41]

Sorry - me again!

I have a table comprising of results as follows:

ID Provider1 Provider2 Provider3 (up to Provider42)
1 5 4 (being rating between 1 and 6)

There are 236 ID's and 42 Providers.

My question is: How can I get the data into a "Results" table so that I can show all 42 Providers listed as rows, the ratings headings listed as columns 1 - 6 and the number of ID's so the table will look as follows:

Rating 1 2 3 4 5 6 (Rating 1 -6)

Provider1 200 1 25 5 3 2 (no of ID's)
Provider2
Provider3
....
Provider42

This is now driving me nuts so any suggestions would be really appreciated!

Lynne

 

[gol4]

2 step process here
Create a union query to put it in a table format
select id, provider1 as rating, "provider1" as provider
from your tablename
Union select select id, provider2 as rating, "provider2" as provider from your tablename
Union select id, provider3 as rating, "provider3" as provider from your tablename
ect...> for each provider

save the query then do a crosstab based on the new union query

row = provider column = rating value = count of id

good luck

could create a vba function to do this as well but not sure if you are comfortable with that

 

[Lynne41]

Thanks very much gol4. It works perfectly!

 

[uscitizen]

I have got something a little different.....


Id Date Test_A Test_B Test_C
A mm/dd/yy # # #
A mm/dd/yy # # #
A mm/dd/yy # # #
B mm/dd/yy # # #
C mm/dd/yy # # #
C mm/dd/yy # # #

The point of the above being that for any Id there will be an unequal number of visits (as represented by rows having Dates and Test_A Test_B Test_C scores).

What I'd like is to have the following:

Id Visit_1A Visit_2A Visit_3A Visit_3A Visit_4A .... Visit_400A Visit_1B Visit_2B Visit_3B Visit_3B Visit_4B .... Visit_400B Visit_1C Visit_2C Visit_3C Visit_3C Visit_4C .... Visit_400C

in a table based on the above.

We know that we're never going to know what the upper limit of visits (rows) will be associated w/ any given ID so (being generous) we'll say 400 (it could be any number). What I'm fixing to do is create a form which has a descriptive label (a column of label controls) running top to bottom on the left side with the column heading titled 'Test Name'; the first row in the 'Test Name' column would read 'Test_A', the second would read 'Test_B', the third and final one would read 'Test_C'. To the right of this, will be up to whatever the maximum number of columns we finally decide upon called 'Day 1 Results', 'Day 2 Results',....'Day 400 Results'. For 'Day 1 Results', beneath it will appear 'Visit_1A', 'Visit_1B', 'Visit_1C' controls and so forth and so on.

 

[gol4]

uscitizen,
Not 100% certain I understand what you are asking here but I think you want to create a table that consists of 3 rows and 400 columns. I am pretty sure in access that the most columns you can have are 255 so that rules out a crosstab. If I am incorrect in my assumption describe more of what you are attempting.

 

[uscitizen]

Hi,

No not a table with 3 rows and '400' columns, but a table with 1 row and '1201' columns. The three first column would be dedicated to the Id, the remaining rows would be filled in with the test scores.

My original posting here might need more explicating; the data are currently being collected using a table underlying a data entry form and the table is designed to collect the data with the following structure.

Id Cycle Day Test_Date Test_A Test_B Test_C
A 1 1 mm/dd/yy # # #
A 1 2 mm/dd/yy # # #
A 1 3 mm/dd/yy # # #
B 1 1 mm/dd/yy # # #
C 1 1 mm/dd/yy # # #
C 1 2 mm/dd/yy # # #
. . . . . . .
. . . . . . .
. . . . . . .

As you see above we have some fixed (in this case three lab tests: A, B and C - their exact names and number of lab tests in the real application would be quite different, butlways the same). We have Patient Id as another col in the table along with Cycle and Day. About these last two: Cycle and Day have maxima in any one database; so as a max the number of Cycles per Id might be 4 and the number of Days per combo of Id and Cyle (i.e. within each Cycle) might be maxed out at 28. Thing is not every Id can be counted on to give the max number of visits (each unique pairing of Cycle and Day comprises a visit). This is probably 'obvious', but 'mm/dd/yy' is a standin for some real calendar date which is entered into the table to reflect when the laboratory specimen was collected (i.e. the date the Id showed up at a clinic).

One way of getting what I want would probably involve creating an MS A2K Table which had Col 1 allocated to the Id parameter. The 2nd through whatever columns would be appear in logical clusters of 3 (because in this example there are 3 'Tests') and the column headers would be a paring of the Visit Date and Test name, e.g. '01/01/02-Test_A', etc. When viewing these data, the user would 'freeze' the leftmost column and use the horizontal scroll bar to view the lab data overtime.

I started out wanting to generate a form which could display the reorganized data structure by having a single form per Id which would allow the user to scroll across the page in order to view each Id's lab test scores on any given visit date. The form would be custom designed....using the design view, the form would be specific to a single Id (which would appear just once upon it). As I envisaged it, it would look like a matrix/table. The titles/labels of each column after the first one would be filled in with the Visit Date. Below each column having a isit Date would be a column of lab test scores collected on that Date. The left most column on the form would be titled 'Lab Test Name' and have below it (to use the names in my current example): 'Test A' followed by 'Test B' and lastly below the two above you'd have 'Test C'.


This design is after giving some more thought to the way the viewer probably wants to see those lab scores.

Hope this helps clear the air a bit.

Apologies for any typos.

 

[gol4]

I apoligize I seem to be having a hard time understanding what you are looking for here. Am I correct In assuming you want a form like this?

PatientID A
Visit date score Visit date score
testA mm/dd/yy # mm/dd/yy #
testB mm/dd/yy # mm/dd/yy #
testC mm/dd/yy # mm/dd/yy #

that scrolls scrolls to the left

 

[uscitizen]

no apologies needed.....i agree this is quite a chore to explain, a picture'd be worth its weight in gold around now.

at any rate,

PatientID A
Visit date score Visit date score
testA mm/dd/yy # mm/dd/yy #
testB mm/dd/yy # mm/dd/yy #
testC mm/dd/yy # mm/dd/yy #

in your example above, each column labeled 'Visit Date' would be for the same visit date. so in your left most column, e.g. the date might be for 11/11/02 and in the next one the date might be for 01/11/03. when i last posted, i didn't think it'd be necessary to have columns with the same date repeated (over three rows) so i envisaged having 'score' attached to a 'visit date': the leftmost score would read 'score-11/11/02', the next one would read 'score-01/11/03'.