Re: Cross tab query construction with Subqueries

Steven,

I wouldn't say it's only performance, though that might be an advantage when the alternative is several nested Select Queries. For me, the best reason would be that a simpler design is easier to understand, and therefore it's more likely to accurately model the real-world system that you are trying to represent in your database. Perhaps the single Crosstab Query is more efficient than several nested Select Queries (I assume it is), but to determine how much so you'd probably need to run your own tests. But I consider understanding what's going on to be vitally essential (both your understanding and that of anyone else involved in maintaining the database); without that, none of the rest of it is of any value to anyone.

-- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.

Steven Cheng wrote:

Thanks John. This is what I ma looking for. I guess the reason for keeping to the crosstabs is purely performance, right?

"Vincent Johns" wrote:


Steven,

I constructed a couple of sample Tables and a bunch of Queries based on them, to illustrate the use both of Crosstab Queries to display what I think you want, and of ordinary Select Queries that do the same kind of thing, though less concisely. I'd recommend using the Crosstab version of each one, if you can, but the Select Queries give you a bit more flexibility if you need it.

I numbered the Queries to help organize them a bit. I hope you're able to use them. Also, although I almost always attach a Lookup property to each foreign key, I didn't do that here, in an effort to keep the explanation short. (It's still fairly long.) The result is that you see some raw key values where I would normally hide them, but since only one field is affected, I figured that it shouldn't be too hard to live with.

OK, here we go...

First, we have 2 Tables containing the kinds of data I think you were talking about. I'm not sure what [Date] meant, nor how it relates to [FiscalPeriod], nor whether it applies to a [Bookings] record, or instead to a [Revenue] record. So I attached it to [Bookings], since it made no sense to me to have 3 [Revenue] records on a line in the data*** and only one of the attached [Date] fields. I hope that what follows is clear enough that you can easily figure out how to correct it, if I was wrong about that.

[Bookings] Table Data*** View:

Bookings_ID Status Booking_Type Date FiscalPeriod
----------- ------ ------------ --------- ------------
-541957595 D G 1/5/2006 Q1
650673794 D G 1/6/2006 Q2
-388935552 D L 1/11/2006 Q2
-1692522879 T G 1/12/2006 Q1
908372654 T G 1/13/2006 Q2
-244883893 T L 1/15/2006 Q1

[Revenue] Table Data*** View:

Revenue_ID Source Amount Bookings_ID
----------- ------ ------ -----------
-1898719723 1 $7.52 908372654
-1496282996 2 $3.00 -541957595
363313496 1 $2.00 -541957595
486581369 3 $4.00 -541957595
773955398 1 $10.00 650673794
1149363023 2 $11.00 -388935552
1171123940 3 $13.00 -1692522879
1238597554 3 $3.50 908372654

I placed an index on [Source] and [Bookings_ID] allowing no duplicate values of [Source] for any one [Bookings] record.

The [Revenue].[Bookings_ID] field is the foreign key; for example, in the first record of [Revenue] it's a reference to the [Bookings] record dated 1/13/2006.

The first 3 Queries display lists of records from the [Revenue] Table corresponding to the 3 revenue sources.

[Q011_Revenue Source 1] SQL:

SELECT Revenue.*
FROM Revenue
WHERE (((Revenue.Source)=1));

[Q011_Revenue Source 1] Query Data*** View:

Revenue_ID Source Amount Bookings_ID
----------- ------ ------ -----------
-1898719723 1 $7.52 908372654
363313496 1 $2.00 -541957595
773955398 1 $10.00 650673794

The SQL for the 2nd of these is identical to the 1st one except for the "2" replacing the "1".

[Q012_Revenue Source 2] SQL:

SELECT Revenue.*
FROM Revenue
WHERE (((Revenue.Source)=2));

[Q012_Revenue Source 2] Query Data*** View:

Revenue_ID Source Amount Bookings_ID
----------- ------ ------ -----------
-1496282996 2 $3.00 -541957595
1149363023 2 $11.00 -388935552

Similarly for the 3rd Query.

[Q013_Revenue Source 3] Query Data*** View:

Revenue_ID Source Amount Bookings_ID
---------- ------ ------ -----------
486581369 3 $4.00 -541957595
1171123940 3 $13.00 -1692522879
1238597554 3 $3.50 908372654

Having split these out, we can combine them into the format that you mentioned in your message. It looks a bit like a Crosstab Query but is a bit lengthier (and it depends on the Subqueries that we defined above).

[Q020_BookingsByQuarter] SQL:

SELECT Bookings.Date,
[Q011_Revenue Source 1].Amount AS RS1,
[Q012_Revenue Source 2].Amount AS RS2,
[Q013_Revenue Source 3].Amount AS RS3,
nz([RS1],0)+nz([RS2],0)+nz([RS3],0) AS Total_Revenue,
Bookings.Status, Bookings.Booking_Type,
Bookings.FiscalPeriod
FROM ((Bookings LEFT JOIN [Q011_Revenue Source 1]
ON Bookings.Bookings_ID
= [Q011_Revenue Source 1].Bookings_ID)
LEFT JOIN [Q012_Revenue Source 2]
ON Bookings.Bookings_ID
= [Q012_Revenue Source 2].Bookings_ID)
LEFT JOIN [Q013_Revenue Source 3]
ON Bookings.Bookings_ID
= [Q013_Revenue Source 3].Bookings_ID;

[Q020_BookingsByQuarter] Query Data*** View:

Date RS1 RS2 RS3 Total_ Status Booking Fiscal
Revenue _Type Period
--------- ----- ------ ------ ------- ------ ------- ------
1/12/2006 $13.00 $13.00 T G Q1
1/5/2006 $2.00 $3.00 $4.00 $9.00 D G Q1
1/11/2006 $11.00 $11.00 D L Q2
1/15/2006 $0.00 T L Q1
1/6/2006 $10.00 $10.00 D G Q2
1/13/2006 $7.52 $3.50 $11.02 T G Q2

The next Query splits out the records for use in a Crosstab Query, placing only one revenue source on a line.

[Q030_Amounts] SQL:

SELECT Bookings.Status, Bookings.Booking_Type,
Revenue.Source, Bookings.FiscalPeriod,
Revenue.Amount, Bookings.Date
FROM Bookings LEFT JOIN Revenue
ON Bookings.Bookings_ID = Revenue.Bookings_ID
ORDER BY Bookings.Status, Bookings.Booking_Type,
Revenue.Source;

[Q030_Amounts] Query Data*** View:

Status Booking Source Fiscal Amount Date
_Type Period
------ ------- ------ ------ ------ ---------
D G 1 Q2 $10.00 1/6/2006
D G 1 Q1 $2.00 1/5/2006
D G 2 Q1 $3.00 1/5/2006
D G 3 Q1 $4.00 1/5/2006
D L 2 Q2 $11.00 1/11/2006
T G 1 Q2 $7.52 1/13/2006
T G 3 Q2 $3.50 1/13/2006
T G 3 Q1 $13.00 1/12/2006
T L Q1 1/15/2006

The following Crosstab Query, based on [Q030_Amounts], produces results similar to those of [Q020_BookingsByQuarter].

[Q040_BookingsByQuarter_Xtab] SQL:

TRANSFORM Sum(Q030_Amounts.Amount) AS SumOfAmount
SELECT Q030_Amounts.Date, Q030_Amounts.Status,
Q030_Amounts.Booking_Type,
Q030_Amounts.FiscalPeriod,
Sum(Q030_Amounts.Amount) AS Total_Revenue
FROM Q030_Amounts
WHERE ((("RS" & [Source])<>"RS"))
GROUP BY Q030_Amounts.Date, Q030_Amounts.Status,
Q030_Amounts.Booking_Type, Q030_Amounts.FiscalPeriod
PIVOT "RS" & [Source];

I notice that I left the [Date] field out of order, but that's easy to move in Query Data*** View, and you'll probably define a Report based on this, anyway, so the order of fields on the Data*** will be immaterial. (I had to move the [RS1]...[RS3] fields that way to put them at the left end.)

[Q040_BookingsByQuarter_Xtab] Query Data*** View:

RS1 RS2 RS3 Total_ Date Status Booking Fiscal
Revenue _Type Period
------ ------ ------ ------- --------- ------ ------- ------
$2.00 $3.00 $4.00 $9.00 1/5/2006 D G Q1
$10.00 $10.00 1/6/2006 D G Q2
$11.00 $11.00 1/11/2006 D L Q2
$13.00 $13.00 1/12/2006 T G Q1
$7.52 $3.50 $11.02 1/13/2006 T G Q2


The following Crosstab Query, also based on [Q030_Amounts], produces results similar to those you described in your most recent posting. But (as I show later) you can do about the same thing using only Select Queries.

[Q050_Amounts_Xtab] SQL:

TRANSFORM Sum(([Q030_Amounts].Amount)) AS Amt
SELECT Q030_Amounts.Status,
Q030_Amounts.Booking_Type AS Type, Q030_Amounts.Source
FROM Q030_Amounts
WHERE (((Q030_Amounts.Source) Is Not Null))
GROUP BY Q030_Amounts.Status,
Q030_Amounts.Booking_Type, Q030_Amounts.Source
PIVOT Q030_Amounts.FiscalPeriod;

[Q050_Amounts_Xtab] Query Data*** View:

Status Type Source Q1 Q2
------ ----- ------ ------ ------
D G 1 $2.00 $10.00
D G 2 $3.00
D G 3 $4.00
D L 2 $11.00
T G 1 $7.52
T G 3 $13.00 $3.50

To do this via Select Queries, we need a few Subqueries, such as the following, which lists all the values of [Source] that appear in the [Revenue] Table.

[Q060_Sources] SQL:

SELECT DISTINCT Revenue.Source
FROM Revenue
ORDER BY Revenue.Source;

[Q060_Sources] Query Data*** View:

Source
------
1
2
3

OK, there (intentionally) aren't very many of them, but I needed a list. Having generated the list of [Source] values, I produce a list of all combinations of [Status], [Booking_Type], and [Source] -- the same fields that served as row headers in the Crosstab.

[Q070_Amounts_Rows] SQL:

SELECT DISTINCT Bookings.Status,
Bookings.Booking_Type, Q060_Sources.Source
FROM Bookings, Q060_Sources
ORDER BY Bookings.Status,
Bookings.Booking_Type, Q060_Sources.Source;

[Q070_Amounts_Rows] Query Data*** View:

Status Booking_Type Source
------ ------------ ------
D G 1
D G 2
D G 3
D L 1
D L 2
D L 3
T G 1
T G 2
T G 3
T L 1
T L 2
T L 3

Now, for each Crosstab column in the data section, I define a Select Query to produce the proper values.

[Q081_Amounts_Q1] SQL:

SELECT Q030_Amounts.*
FROM Q030_Amounts
WHERE (((Q030_Amounts.FiscalPeriod)="Q1"));

[Q081_Amounts_Q1] Query Data*** View:

Status Booking Source Fiscal Amount Date
_Type Period
------ ------- ------ ------ ------ ---------
D G 1 Q1 $2.00 1/5/2006
D G 2 Q1 $3.00 1/5/2006
D G 3 Q1 $4.00 1/5/2006
T G 3 Q1 $13.00 1/12/2006
T L Q1 1/15/2006

As before, the next one is identical except that it contains "Q2" instead of "Q1".

[Q082_Amounts_Q2] SQL:

SELECT Q030_Amounts.*
FROM Q030_Amounts
.