Subtracting with Criteria in Report

[unclemicah]

I thought this should be a fairly simple thing to solve, but have run into a wall.
I have a subreport that give output of pre and post tests. I have the table set up so the end user can select if the test they are entering is a pre or post test and then enter the corresponding data for that particular test.
I would like to get a difference between the pre and post test results for all of the other data entry fields that correspond to them.
I have tried a dlookup to no avail and subtracting min & max does not work because they vary between data entry fields. Any ideas? Thanks.

 

[dhookom]

pre and post tests", "corresponding data", "particular test", "other data entry fields that correspond",...

Are we supposed to understand your tables/data from what you have described? Maybe you can share some sample data with actual TABLE and FIELD names as well as your desired disply of that data in a report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[unclemicah]

tblTest
Fields: Type(pre & post), Question 1, Question 2, etc. . .
*pre and post tests are in same table, just designated by "Type" field.


Desired Result
% change in averages between pre and post for each question.

Obviously it is easy to get pre and post into same query/report, but I am having trouble with the calculation for each question result.

Is that enough info? Thanks for your help.

 

[dhookom]

If you values for "Question 1" are like Yes, No, Sometimes,... how can we calculate % change?

Apparently you want to compare pre vs post which are two different records in the same table. I assume there are more than two records in the table so how are we to know which pre goes with which post?

Have you considered normalizing your table structure?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[unclemicah]

Sorry, I failed to mention that the table does contain unique id's from another client table that corresponds each person who has taken the tests. So, each id will have a pre and post. Also, the data entered into the Question fields are intigers. They are scores.

I had thought about normalization but wasn't sure that would solve my problem here. Do you think it would work to separate out the pre and post in subqueries? I was trying to keep as simple as possible for end user.

Thanks so much for your quick responses.