creating file for analysis

[TBA]

hi there- probably really simple, but I'd appreciate any suggestions on this.
I have data in Access 2007 which looks at changes in scores between one, two
or three points in time for each participant. I have both Access 2007 and
SPSS 16 installed- so endless possibilities for linking analysing etc.
the structure of the data set appears to be fairly simple- the primary key
is a hospital code. each score is part of a subtable where the type of visit
'initial' 'second' or 'third' is used as a variable and the 'score' follows.
when i export it i get a table like this:

patient 1, visit initial,score
patient 1, visit second, score
patient 1, visit third, score

what I want to do is create an excel table that has the data in a row like
this:
patient1, initial score, second score, third score
patient2, initial score, second score, third score

I wnat to keep the question this short- so please let me know what else you
need to explain. many thanks- Theo.

 

[John Spencer]

Looks as if you could use a crosstab query for that.

TRANSFORM First([Score]) as TheScore
SELECT [Patient]
FROM [YourTable]
GROUP BY [Patient]
PIVOT [VisitType]

Add you three fields to a query
Select Query: Crosstab from the menu
Under Patient select Group by and Row Heading
Under Score Select First and Value
Under VisitType Select Group by and Column heading

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[TBA]

thanks John- that does it perfectly!

best wishes

Theo

Looks as if you could use a crosstab query for that.

TRANSFORM First([Score]) as TheScore
SELECT [Patient]
FROM [YourTable]
GROUP BY [Patient]
PIVOT [VisitType]

Add you three fields to a query
Select Query: Crosstab from the menu
Under Patient select Group by and Row Heading
Under Score Select First and Value
Under VisitType Select Group by and Column heading

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

hi there- probably really simple, but I'd appreciate any suggestions on this.
I have data in Access 2007 which looks at changes in scores between one, two
or three points in time for each participant. I have both Access 2007 and
SPSS 16 installed- so endless possibilities for linking analysing etc.
the structure of the data set appears to be fairly simple- the primary key
is a hospital code. each score is part of a subtable where the type of visit
'initial' 'second' or 'third' is used as a variable and the 'score' follows.
when i export it i get a table like this:

patient 1, visit initial,score
patient 1, visit second, score
patient 1, visit third, score

what I want to do is create an excel table that has the data in a row like
this:
patient1, initial score, second score, third score
patient2, initial score, second score, third score

I wnat to keep the question this short- so please let me know what else you
need to explain. many thanks- Theo.