Subtraction confusion

[springy]

I have a table (Tblmeasures) in a one to many relationship with a personal details table (TBLPatients). Tbl measures contains the responses to a questionnaire taken at two time points (time1 data, time2 data). I want to compare the responses given at the two time points by subtracting the second score from the first score.

I want to present the information something like the below example but I am not sure what the best way to do it is – either an expression in a report or some code or query that calculates the difference and inserts the result into a new field. Either way I am stuck.

Clinic ID is primary key
Q1= question 1 etc

Clinic ID
Time1 data, Q1, Q2, Q3, Q4
Time2 data, Q1, Q2, Q3, Q4
Difference Q1, Q2, Q3, Q4

I would be really grateful for any tips you could offer.

Thanks
PB

[Stewart Ross]

Hi. This kind of analysis is what Excel is good at. It's not as easy to get an SQL-based database to do this, because you need to be able to group rows together (easy enough) then match the rows together for the two different time values and subtract the correct fields from each other (a non-trivial task in SQL). Reason why it is difficult is that SQL is based on set arithmetic, and sets have no inherent order or record position (although of course it is possible to order rows based on the values in particular columns). As a result, matching one timebound set of question results to another is more tricky than is immediately apparent and involves joining the dataset back to itself for the different timebounds.

It is no copout to say that by far the simplest approach is to export the raw data to Excel then use Excel's facilities to do the analysis.

-Stewart