Crosstab - Subtraction Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know how to create a crosstab query that has a total column. Is there not a
way to get the difference between column headings? My example is pre and
post tests.

Pre Test Post Test Difference
Client 1 15 10 5
Client 2 20 20 0

Thanks for your help.
 
Yes. Do you have some table structures and sample data to share? Also, you
crosstab SQL view.
 
Thanks, Duane. The table structure is 1. Client table 2. Test table with
client id as relational field. In the test table is the option to choose if
it is a pre or post test type. I did consider normalizing the pre and post,
but would like to avoid that with the extra amount of work it would require
due to the quantity of tests. Here is my current SQL. This includes a total
column at this point. Hyperactivity is one section of measure in the BASC
test.

TRANSFORM Max(tblBASC.[Hyperactivity Total]) AS [MaxOfHyperactivity Total]
SELECT tblBASC.[Case #], Sum(tblBASC.[Hyperactivity Total]) AS Difference
FROM tblBASC
GROUP BY tblBASC.[Case #]
PIVOT tblBASC.[Test Type] In ("1. Pre","3. Post");

My ultimate goal is to get an X% had a reduction in their Hyperactivity
total, but some of that could be done through a report. Thanks very much for
your help.
 
Try this non-crosstab query:

SELECT tblBASC.[Case #],
Sum(Abs([Test Type]="1. Pre") * [Hyperactivity Total]) as PreSum,
Sum(Abs([Test Type]="3. Post") * [Hyperactivity Total]) as PostSum,
Sum(Abs([Test Type]="1. Pre") * [Hyperactivity Total]) - Sum(Abs([Test
Type]="3. Post") * [Hyperactivity Total]) as Difference
FROM tblBASC
WHERE tblBASC.[Test Type] In ("1. Pre","3. Post");
GROUP BY tblBASC.[Case #]



--
Duane Hookom
MS Access MVP
--

Micah said:
Thanks, Duane. The table structure is 1. Client table 2. Test table with
client id as relational field. In the test table is the option to choose
if
it is a pre or post test type. I did consider normalizing the pre and
post,
but would like to avoid that with the extra amount of work it would
require
due to the quantity of tests. Here is my current SQL. This includes a
total
column at this point. Hyperactivity is one section of measure in the BASC
test.

TRANSFORM Max(tblBASC.[Hyperactivity Total]) AS [MaxOfHyperactivity Total]
SELECT tblBASC.[Case #], Sum(tblBASC.[Hyperactivity Total]) AS Difference
FROM tblBASC
GROUP BY tblBASC.[Case #]
PIVOT tblBASC.[Test Type] In ("1. Pre","3. Post");

My ultimate goal is to get an X% had a reduction in their Hyperactivity
total, but some of that could be done through a report. Thanks very much
for
your help.


Duane Hookom said:
Yes. Do you have some table structures and sample data to share? Also,
you
crosstab SQL view.
Click to expand...
Click to expand...
 
Duane, this worked perfectly. Thank you so much for your help.


Duane Hookom said:
Try this non-crosstab query:

SELECT tblBASC.[Case #],
Sum(Abs([Test Type]="1. Pre") * [Hyperactivity Total]) as PreSum,
Sum(Abs([Test Type]="3. Post") * [Hyperactivity Total]) as PostSum,
Sum(Abs([Test Type]="1. Pre") * [Hyperactivity Total]) - Sum(Abs([Test
Type]="3. Post") * [Hyperactivity Total]) as Difference
FROM tblBASC
WHERE tblBASC.[Test Type] In ("1. Pre","3. Post");
GROUP BY tblBASC.[Case #]



--
Duane Hookom
MS Access MVP
--

Micah said:
Thanks, Duane. The table structure is 1. Client table 2. Test table with
client id as relational field. In the test table is the option to choose
if
it is a pre or post test type. I did consider normalizing the pre and
post,
but would like to avoid that with the extra amount of work it would
require
due to the quantity of tests. Here is my current SQL. This includes a
total
column at this point. Hyperactivity is one section of measure in the BASC
test.

TRANSFORM Max(tblBASC.[Hyperactivity Total]) AS [MaxOfHyperactivity Total]
SELECT tblBASC.[Case #], Sum(tblBASC.[Hyperactivity Total]) AS Difference
FROM tblBASC
GROUP BY tblBASC.[Case #]
PIVOT tblBASC.[Test Type] In ("1. Pre","3. Post");

My ultimate goal is to get an X% had a reduction in their Hyperactivity
total, but some of that could be done through a report. Thanks very much
for
your help.


Duane Hookom said:
Yes. Do you have some table structures and sample data to share? Also,
you
crosstab SQL view.

--
Duane Hookom
MS Access MVP
--

I know how to create a crosstab query that has a total column. Is there
not
a
way to get the difference between column headings? My example is pre
and
post tests.

Pre Test Post Test Difference
Client 1 15 10 5
Client 2 20 20 0

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top