To crosstab or not to crosstab ... That is the question

[Tom]

Please, save me from myself! I am trying to accomplish a simple(?) query.
I am hoping your fresh eyes can help simplify what I seem to be
overcomplicating.

Here's what I am looking for ... a report that looks similar to the
following

Name Producer Commissions 2002 Commissions 2003 Variance
XYZ Bob $500.00
0 -100%
ABC Jan $500.00
$350.00 -30%
---------------

The problem comes when I attempt to calculate the variance. I can calculate
it OK, but I want to select only the items where the variance is >10% or
<10%. I receive a Data Type mismatch.

The Variance is calculated in Query 2 based on the results of a crosstab
query in Query 1.

I have created two queries thus far to accomplish this, and it seems that I
may be complicating it. There are inherent problems with my queries for
example, the second query (based on the crosstab) uses field names assigned
by the crosstab. This will be problematic when obtaining new years
information.

If it sounds like I am on the right track, let me know ... if you have
suggestions, please share them with me.

Thanks,

Tom

For those interested, here is what the datasources look like:

Client Database Table
RISK# NAME PR1
1 XYZ Bob
2 ABC Jan

Transactions Table
RISK# TRANSDATE Amount
1 01/01/2002 100.00
1 02/01/2002 100.00
1 03/01/2002 100.00
1 04/01/2002 100.00
1 05/01/2002 100.00
2 01/01/2002 100.00
2 02/01/2002 100.00
2 03/01/2002 100.00
2 04/01/2002 100.00
2 05/01/2002 100.00
2 01/01/2003 100.00
2 02/01/2003 100.00
2 03/01/2003 100.00
2 04/01/2003 50.00
Of course there are several other dates, spanning multiple years.

QUERY 1
TRANSFORM Sum(TRANSACTIONS.AMOUNT) AS SumOfAMOUNT
SELECT [CLIENT DATABASE].PR1, [CLIENT DATABASE].NAME
FROM TRANSACTIONS INNER JOIN [CLIENT DATABASE] ON TRANSACTIONS.[RISK#] =
[CLIENT DATABASE].[RISK#]
WHERE (((TRANSACTIONS.TRANSDATE) Between #1/1/2002# And #6/30/2002# Or
(TRANSACTIONS.TRANSDATE) Between #1/1/2003# And #6/30/2003#))
GROUP BY [CLIENT DATABASE].PR1, [CLIENT DATABASE].NAME
PIVOT Format([TRANSDATE],"yyyy");


QUERY 2
SELECT DISTINCTROW [qrySummary of Commission Received - Variance 1].PR1,
[qrySummary of Commission Received - Variance 1].NAME, [qrySummary of
Commission Received - Variance 1].[2002], [qrySummary of Commission
Received - Variance 1].[2003], Format(([2003]/[2002]-1),"Percent") AS
Variance, Format(([2003]-[2002]),"Currency") AS Variance2
FROM [qrySummary of Commission Received - Variance 1]
GROUP BY [qrySummary of Commission Received - Variance 1].PR1, [qrySummary
of Commission Received - Variance 1].NAME, [qrySummary of Commission
Received - Variance 1].[2002], [qrySummary of Commission Received - Variance
1].[2003], Format(([2003]/[2002]-1),"Percent"),
Format(([2003]-[2002]),"Currency");

 

[Duane Hookom]

I would use a column heading property of
ColHead: "Yr" & DateDiff("yyyy",[DateField], Year(Date))
Set the Column Headings property to
Column Headings: "Yr0", "Yr1"
Yr0 will be the current year and Yr1 is last year.

Sometimes crosstab values are returned as variants. You can use a Value of
TheVal:Val(Sum(Nz([YourField],0)))

--
Duane Hookom
MS Access MVP

Please, save me from myself! I am trying to accomplish a simple(?) query.
I am hoping your fresh eyes can help simplify what I seem to be
overcomplicating.

Here's what I am looking for ... a report that looks similar to the
following

Name Producer Commissions 2002 Commissions 2003 Variance
XYZ Bob $500.00
0 -100%
ABC Jan $500.00
$350.00 -30%
---------------

The problem comes when I attempt to calculate the variance. I can calculate
it OK, but I want to select only the items where the variance is >10% or
<10%. I receive a Data Type mismatch.

The Variance is calculated in Query 2 based on the results of a crosstab
query in Query 1.

I have created two queries thus far to accomplish this, and it seems that I
may be complicating it. There are inherent problems with my queries for
example, the second query (based on the crosstab) uses field names assigned
by the crosstab. This will be problematic when obtaining new years
information.

If it sounds like I am on the right track, let me know ... if you have
suggestions, please share them with me.

Thanks,

Tom

For those interested, here is what the datasources look like:

Client Database Table
RISK# NAME PR1
1 XYZ Bob
2 ABC Jan

Transactions Table
RISK# TRANSDATE Amount
1 01/01/2002 100.00
1 02/01/2002 100.00
1 03/01/2002 100.00
1 04/01/2002 100.00
1 05/01/2002 100.00
2 01/01/2002 100.00
2 02/01/2002 100.00
2 03/01/2002 100.00
2 04/01/2002 100.00
2 05/01/2002 100.00
2 01/01/2003 100.00
2 02/01/2003 100.00
2 03/01/2003 100.00
2 04/01/2003 50.00
Of course there are several other dates, spanning multiple years.

QUERY 1
TRANSFORM Sum(TRANSACTIONS.AMOUNT) AS SumOfAMOUNT
SELECT [CLIENT DATABASE].PR1, [CLIENT DATABASE].NAME
FROM TRANSACTIONS INNER JOIN [CLIENT DATABASE] ON TRANSACTIONS.[RISK#] =
[CLIENT DATABASE].[RISK#]
WHERE (((TRANSACTIONS.TRANSDATE) Between #1/1/2002# And #6/30/2002# Or
(TRANSACTIONS.TRANSDATE) Between #1/1/2003# And #6/30/2003#))
GROUP BY [CLIENT DATABASE].PR1, [CLIENT DATABASE].NAME
PIVOT Format([TRANSDATE],"yyyy");


QUERY 2
SELECT DISTINCTROW [qrySummary of Commission Received - Variance 1].PR1,
[qrySummary of Commission Received - Variance 1].NAME, [qrySummary of
Commission Received - Variance 1].[2002], [qrySummary of Commission
Received - Variance 1].[2003], Format(([2003]/[2002]-1),"Percent") AS
Variance, Format(([2003]-[2002]),"Currency") AS Variance2
FROM [qrySummary of Commission Received - Variance 1]
GROUP BY [qrySummary of Commission Received - Variance 1].PR1, [qrySummary
of Commission Received - Variance 1].NAME, [qrySummary of Commission
Received - Variance 1].[2002], [qrySummary of Commission Received - Variance
1].[2003], Format(([2003]/[2002]-1),"Percent"),
Format(([2003]-[2002]),"Currency");