simulate crosstab

[ajm]

I have been given a list of patient info relating to treatments offered. Its in the form of a new line for each treatment. Some patients have a single treatment, others have multiple treatments. All treatments are identified as either "Primary" for the first treatment, and "Secondary" for all others. I want to display the information as a single line per patient with the Primary treatment in the first column after their personal details and then each successive Secondary treatment in the columns after this. Can anyone advise?

 

[Fazza]

first add a COUNTIF formula, something like NEWFIELD =IF(identifying field = Primary", 0, COUNTIF(identifying field = "Secondary", patient ID, patient ID field from for 1 to current row))

Then you can make a CROSSTAB

TRANSFORM MAX(treatment field)
SELECT patient ID, etc
FROM table
GROUP BY patient ID, etc
PIVOT NEWFIELD

 

[ajm]

Hi Fazza,

Are you talking about crosstabbing in access?

 

[Fazza]

That COUNTIF was wrong earlier. Untested. Maybe better with COUNTIFS. Again untested
=COUNTIFS(row 1:current row patient ID, patient ID this row, row 1:current row identifying field, "Secondary")
Something like that to count successive secondary treatments per patient. I'm guessing that is fast enough for the amount of data you have.

I was not referring to cross tab in Access, though that should work too. Definition same as Excel.

 

[ajm]

Fazza,

I don't understand what you mean. I have added an extra column (CrosstabIDs )to my data. It is similar to what I think you refer to above, in that it gives the successive "Secondary" treatments a number. Formula is:

=IF(I2="Primary",0,M1+1)

where I2 contains text Primary or Secondary. Using a Pivot Table, I can now spread these CrosstabIDs across the columns, with the Primary/Secondary underneath to form a two layer column header. Problem now is that I can't get the descriptions of the treatments to display. I can count them, sum, them etc, just not list them. I think a jump into VBA is probably neceessary to shift the actual treatment descriptions underneath the headers? I should have mentioned originally that the treatment information is text descriptions.
Is this what you were thinking of with your suggestion?

 

[Fazza]

hi,

Add an extra column, yes: formula like in post#4. That gives 0 for the primary, and then successive counts, per patient, of the secondary treatments.

Pivot table is no good. Just use a query for the crosstab result. (You could use VBA if you wanted but there is no need.)

Just thought, there is probably a 255 character text limit in the crosstab.

OK?

 

[ajm]

I think I am starting to get it. convert the data to a table and import to query wizard, right?. after that, I am stuck again as i don't know how to crosstab in msquery.

 

[Fazza]

One way to create crosstab in MS Query is via the SQL button and entering text like in post #2 - just with your specific field names.