Cross tab query construction with Subqueries

I have query that has the following fields:

Date
Revenue Source 1
Revenue Source 2
Revenue Source 3
Total_Revenue
Status
Booking_Type
FiscalPeriod

I was trying to create a cross tab query that pivots along FiscalPeriod and
using Status and Booking_type as row headings with Total_Revenue (which is a
calculated field of Revenue source 1, 2, and 3).

However, I also need it to spit out values for Revenue_Sources 1, 2, and 3
by the same row and column headings.

I think the only way is to use union queries but in also doing some
research, I think that I could construct a crosstab query using subqueries
that may be able to do the same thing. Just need a little guidance.

Steven Cheng wrote:

I have query that has the following fields:

Date
Revenue Source 1
Revenue Source 2
Revenue Source 3
Total_Revenue
Status
Booking_Type
FiscalPeriod

I was trying to create a cross tab query that pivots along FiscalPeriod and
using Status and Booking_type as row headings with Total_Revenue (which is a
calculated field of Revenue source 1, 2, and 3).

I think you might want to set up two Crosstab Queries. In one, you
could have various values of [FiscalPeriod] as the column headings, and
[Total_Revenue] (which I assume is the sum of the 3 [Revenue Source ...]
fields) be the values. As I assume you would avoid having more than one
record for any one set of [Status], [Booking_type], and [FiscalPeriod],
you could use any of Sum() or First() or Last() as the aggregating
function without affecting the results.

However, I also need it to spit out values for Revenue_Sources 1, 2, and 3
by the same row and column headings.

For the second Crosstab Query, you could define a new field in your
Query that would combine the value of [FiscalPeriod] with "_1", "_2", or
"_3" to represent the various [Revenue Source ...] values. For example,
the values of this field might look like

2005_Q3_1
2005_Q3_2
2005_Q3_3
2005_Q4_1
...

and you could populate the body of your datasheet with these values,
again using Sum() or First(), and ignoring the [Total_Revenue] field.

But the column headings would NOT match those in the previous Crosstab
Query if you did this. However, you could define three Crosstab
Queries, one for each of the three [Revenue Source ...] fields. (I'm
not sure why you'd want to do that, though.)

I think the only way is to use union queries but in also doing some
research, I think that I could construct a crosstab query using subqueries
that may be able to do the same thing. Just need a little guidance.

I don't know where you want to go with this, but if you wished, you
could define a Select Query based on these two Crosstab Queries and
display the fields in any order you choose -- but this would be kind of
a high-maintenance kind of Query, as you might need to keep changing the
field names (to match new [FiscalPeriod] values) as time marched onward.
Or you'd need to calculate the SQL via a stored VBA procedure.

I suggest you avoid a Union Query for this, at least if you set up your
CrossTabs as I suggested, as the data you'd be reporting would involve
different numbers of fields (totals vs. 3 individual values).

Maybe if you post an example Select Query Datasheet displaying the data
you wish to start with, and an example Crosstab Query Datasheet, or
maybe a Report, displaying what you'd like the results to look like
based on those same sample data, it would be easier to give you specific
advice.

-- Vincent Johns
Please feel free to quote anything I say here.

Vincent;

Thanks for the advise. Yes, I figured that my explanation was confusing.

Steven

"Vincent Johns" wrote:

Steven Cheng wrote:

I have query that has the following fields:

Date
Revenue Source 1
Revenue Source 2
Revenue Source 3
Total_Revenue
Status
Booking_Type
FiscalPeriod

I was trying to create a cross tab query that pivots along FiscalPeriod and
using Status and Booking_type as row headings with Total_Revenue (which is a
calculated field of Revenue source 1, 2, and 3).

I think you might want to set up two Crosstab Queries. In one, you
could have various values of [FiscalPeriod] as the column headings, and
[Total_Revenue] (which I assume is the sum of the 3 [Revenue Source ...]
fields) be the values. As I assume you would avoid having more than one
record for any one set of [Status], [Booking_type], and [FiscalPeriod],
you could use any of Sum() or First() or Last() as the aggregating
function without affecting the results.

However, I also need it to spit out values for Revenue_Sources 1, 2, and 3
by the same row and column headings.

For the second Crosstab Query, you could define a new field in your
Query that would combine the value of [FiscalPeriod] with "_1", "_2", or
"_3" to represent the various [Revenue Source ...] values. For example,
the values of this field might look like

2005_Q3_1
2005_Q3_2
2005_Q3_3
2005_Q4_1
...

and you could populate the body of your datasheet with these values,
again using Sum() or First(), and ignoring the [Total_Revenue] field.

But the column headings would NOT match those in the previous Crosstab
Query if you did this. However, you could define three Crosstab
Queries, one for each of the three [Revenue Source ...] fields. (I'm
not sure why you'd want to do that, though.)

I think the only way is to use union queries but in also doing some
research, I think that I could construct a crosstab query using subqueries
that may be able to do the same thing. Just need a little guidance.

I don't know where you want to go with this, but if you wished, you
could define a Select Query based on these two Crosstab Queries and
display the fields in any order you choose -- but this would be kind of
a high-maintenance kind of Query, as you might need to keep changing the
field names (to match new [FiscalPeriod] values) as time marched onward.
Or you'd need to calculate the SQL via a stored VBA procedure.

I suggest you avoid a Union Query for this, at least if you set up your
CrossTabs as I suggested, as the data you'd be reporting would involve
different numbers of fields (totals vs. 3 individual values).

Maybe if you post an example Select Query Datasheet displaying the data
you wish to start with, and an example Crosstab Query Datasheet, or
maybe a Report, displaying what you'd like the results to look like
based on those same sample data, it would be easier to give you specific
advice.

-- Vincent Johns
Please feel free to quote anything I say here.

So... were you able to define a Query to do what you wanted? (I assume
so, but if not, people are here to help.)

-- Vincent Johns
Please feel free to quote anything I say here.


Steven Cheng wrote:
Vincent;

Thanks for the advise. Yes, I figured that my explanation was confusing.

Steven

"Vincent Johns" wrote:


Steven Cheng wrote:


I have query that has the following fields:

Date
Revenue Source 1
Revenue Source 2
Revenue Source 3
Total_Revenue
Status
Booking_Type
FiscalPeriod

I was trying to create a cross tab query that pivots along FiscalPeriod and
using Status and Booking_type as row headings with Total_Revenue (which is a
calculated field of Revenue source 1, 2, and 3).

I think you might want to set up two Crosstab Queries. In one, you
could have various values of [FiscalPeriod] as the column headings, and
[Total_Revenue] (which I assume is the sum of the 3 [Revenue Source ...]
fields) be the values. As I assume you would avoid having more than one
record for any one set of [Status], [Booking_type], and [FiscalPeriod],
you could use any of Sum() or First() or Last() as the aggregating
function without affecting the results.


However, I also need it to spit out values for Revenue_Sources 1, 2, and 3
by the same row and column headings.

For the second Crosstab Query, you could define a new field in your
Query that would combine the value of [FiscalPeriod] with "_1", "_2", or
"_3" to represent the various [Revenue Source ...] values. For example,
the values of this field might look like

2005_Q3_1
2005_Q3_2
2005_Q3_3
2005_Q4_1
...

and you could populate the body of your datasheet with these values,
again using Sum() or First(), and ignoring the [Total_Revenue] field.

But the column headings would NOT match those in the previous Crosstab
Query if you did this. However, you could define three Crosstab
Queries, one for each of the three [Revenue Source ...] fields. (I'm
not sure why you'd want to do that, though.)


I think the only way is to use union queries but in also doing some
research, I think that I could construct a crosstab query using subqueries
that may be able to do the same thing. Just need a little guidance.

I don't know where you want to go with this, but if you wished, you
could define a Select Query based on these two Crosstab Queries and
display the fields in any order you choose -- but this would be kind of
a high-maintenance kind of Query, as you might need to keep changing the
field names (to match new [FiscalPeriod] values) as time marched onward.
Or you'd need to calculate the SQL via a stored VBA procedure.

I suggest you avoid a Union Query for this, at least if you set up your
CrossTabs as I suggested, as the data you'd be reporting would involve
different numbers of fields (totals vs. 3 individual values).

Maybe if you post an example Select Query Datasheet displaying the data
you wish to start with, and an example Crosstab Query Datasheet, or
maybe a Report, displaying what you'd like the results to look like
based on those same sample data, it would be easier to give you specific
advice.

-- Vincent Johns
Please feel free to quote anything I say here.

Vincent;

Sorry, again, I was quite confusing...I have got to stop typing when it is
late and I am tired.

Ultimately, what I am trying to accomplish is a multi-value crosstab query
where the row headings are grouped by Status and Booking_Type, column
headings are FiscalPeriods, and values are Revenue Source 1, 2, 3, and
Total_revenue individually.

Example:

Status Type Source Q1 Q2

D G 1 XXX XXX
D G 2 XXX XXX
D G 3 XXX XXX
D L 1 XXX XXX
D L 2 XXX XXX
D L 3 XXX XXX
T G 1 XXX XXX
T G 2 XXX XXX
T G 3 XXX XXX

Although this looks like a crosstab with 3 row headings, it isn't because
Source is actuals fields RevenueSource 1, 2, and 3.

I was trying to find another solution other than trying to doing a union
query of 4 different cross tab queries where the values were for each of
these revenue sources. I am not quite imaginative enough at this point with
SQL statements and thus seeing if there is something smarter and faster. I
remember something about constructing a crosstab query without using the
TRANSFORM/PIVOT statements because they are native to the Jet engine and that
you could do the same with subqueries. Could you confirm this.

As for the time periods, thanks, I have the SQL stored in a VBA modules
passing values for the time periods.

Steven


"Vincent Johns" wrote:

So... were you able to define a Query to do what you wanted? (I assume
so, but if not, people are here to help.)

-- Vincent Johns
Please feel free to quote anything I say here.


Steven Cheng wrote:
Vincent;

Thanks for the advise. Yes, I figured that my explanation was confusing.

Steven

"Vincent Johns" wrote:


Steven Cheng wrote:


I have query that has the following fields:

Date
Revenue Source 1
Revenue Source 2
Revenue Source 3
Total_Revenue
Status
Booking_Type
FiscalPeriod

I was trying to create a cross tab query that pivots along FiscalPeriod and
using Status and Booking_type as row headings with Total_Revenue (which is a
calculated field of Revenue source 1, 2, and 3).

I think you might want to set up two Crosstab Queries. In one, you
could have various values of [FiscalPeriod] as the column headings, and
[Total_Revenue] (which I assume is the sum of the 3 [Revenue Source ...]
fields) be the values. As I assume you would avoid having more than one
record for any one set of [Status], [Booking_type], and [FiscalPeriod],
you could use any of Sum() or First() or Last() as the aggregating
function without affecting the results.


However, I also need it to spit out values for Revenue_Sources 1, 2, and 3
by the same row and column headings.

For the second Crosstab Query, you could define a new field in your
Query that would combine the value of [FiscalPeriod] with "_1", "_2", or
"_3" to represent the various [Revenue Source ...] values. For example,
the values of this field might look like

2005_Q3_1
2005_Q3_2
2005_Q3_3
2005_Q4_1
...

and you could populate the body of your datasheet with these values,
again using Sum() or First(), and ignoring the [Total_Revenue] field.

But the column headings would NOT match those in the previous Crosstab
Query if you did this. However, you could define three Crosstab
Queries, one for each of the three [Revenue Source ...] fields. (I'm
not sure why you'd want to do that, though.)


I think the only way is to use union queries but in also doing some
research, I think that I could construct a crosstab query using subqueries
that may be able to do the same thing. Just need a little guidance.

I don't know where you want to go with this, but if you wished, you
could define a Select Query based on these two Crosstab Queries and
display the fields in any order you choose -- but this would be kind of
a high-maintenance kind of Query, as you might need to keep changing the
field names (to match new [FiscalPeriod] values) as time marched onward.
Or you'd need to calculate the SQL via a stored VBA procedure.

I suggest you avoid a Union Query for this, at least if you set up your
CrossTabs as I suggested, as the data you'd be reporting would involve
different numbers of fields (totals vs. 3 individual values).

Maybe if you post an example Select Query Datasheet displaying the data
you wish to start with, and an example Crosstab Query Datasheet, or
maybe a Report, displaying what you'd like the results to look like
based on those same sample data, it would be easier to give you specific
advice.

-- Vincent Johns
Please feel free to quote anything I say here.

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
datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet View, and you'll probably define a Report based
on this, anyway, so the order of fields on the Datasheet will be
immaterial. (I had to move the [RS1]...[RS3] fields that way to put
them at the left end.)

[Q040_BookingsByQuarter_Xtab] Query Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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
WHERE (((Q030_Amounts.FiscalPeriod)="Q2"));

[Q082_Amounts_Q2] Query Datasheet View:

Status Booking Source Fiscal Amount Date
_Type Period
------ ------- ------ ------ ------ ---------
D G 1 Q2 $10.00 1/6/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

Now that we've defined both columns, we plug them into
[Q070_Amounts_Rows] (or something pretty similar) to produce a fake
Crosstab Query.

[Q090_Amounts_Alternate] SQL:

SELECT Q070_Amounts_Rows.Status,
Q070_Amounts_Rows.Booking_Type AS Type,
Q070_Amounts_Rows.Source,
QA1.Amount AS Q1, QA2.Amount AS Q2
FROM (Q070_Amounts_Rows
LEFT JOIN Q081_Amounts_Q1 AS QA1
ON (Q070_Amounts_Rows.Source = QA1.Source)
AND (Q070_Amounts_Rows.Status = QA1.Status)
AND (Q070_Amounts_Rows.Booking_Type
= QA1.Booking_Type))
LEFT JOIN Q082_Amounts_Q2 AS QA2
ON (Q070_Amounts_Rows.Source = QA2.Source)
AND (Q070_Amounts_Rows.Booking_Type
= QA2.Booking_Type)
AND (Q070_Amounts_Rows.Status = QA2.Status)
WHERE (((QA1.Amount) Is Not Null))
OR (((QA2.Amount) Is Not Null))
ORDER BY Q070_Amounts_Rows.Status,
Q070_Amounts_Rows.Booking_Type,
Q070_Amounts_Rows.Source;

[Q090_Amounts_Alternate] Query Datasheet 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

OK, so it's possible to do what you asked, and with only a moderate
amount of extra work. However, unless you have an SQL processor that
simply won't handle Crosstabs, I think you'd be ahead running a Crosstab
Query that comes close to doing what you want, and using that Crosstab
as a data source for another Select Query that filters, sorts, and links
the results as you want it to.

-- Vincent Johns
Please feel free to quote anything I say here.


Steven Cheng wrote:

Vincent;

Sorry, again, I was quite confusing...I have got to stop typing when it is
late and I am tired.

Ultimately, what I am trying to accomplish is a multi-value crosstab query
where the row headings are grouped by Status and Booking_Type, column
headings are FiscalPeriods, and values are Revenue Source 1, 2, 3, and
Total_revenue individually.

Example:

Status Type Source Q1 Q2

D G 1 XXX XXX
D G 2 XXX XXX
D G 3 XXX XXX
D L 1 XXX XXX
D L 2 XXX XXX
D L 3 XXX XXX
T G 1 XXX XXX
T G 2 XXX XXX
T G 3 XXX XXX

Although this looks like a crosstab with 3 row headings, it isn't because
Source is actuals fields RevenueSource 1, 2, and 3.

I was trying to find another solution other than trying to doing a union
query of 4 different cross tab queries where the values were for each of
these revenue sources. I am not quite imaginative enough at this point with
SQL statements and thus seeing if there is something smarter and faster. I
remember something about constructing a crosstab query without using the
TRANSFORM/PIVOT statements because they are native to the Jet engine and that
you could do the same with subqueries. Could you confirm this.

As for the time periods, thanks, I have the SQL stored in a VBA modules
passing values for the time periods.

Steven


"Vincent Johns" wrote:


So... were you able to define a Query to do what you wanted? (I assume
so, but if not, people are here to help.)

-- Vincent Johns
Please feel free to quote anything I say here.


Steven Cheng wrote:

Vincent;

Thanks for the advise. Yes, I figured that my explanation was confusing.

Steven

"Vincent Johns" wrote:



Steven Cheng wrote:



I have query that has the following fields:

Date
Revenue Source 1
Revenue Source 2
Revenue Source 3
Total_Revenue
Status
Booking_Type
FiscalPeriod

I was trying to create a cross tab query that pivots along FiscalPeriod and
using Status and Booking_type as row headings with Total_Revenue (which is a
calculated field of Revenue source 1, 2, and 3).

I think you might want to set up two Crosstab Queries. In one, you
could have various values of [FiscalPeriod] as the column headings, and
[Total_Revenue] (which I assume is the sum of the 3 [Revenue Source ...]
fields) be the values. As I assume you would avoid having more than one
record for any one set of [Status], [Booking_type], and [FiscalPeriod],
you could use any of Sum() or First() or Last() as the aggregating
function without affecting the results.



However, I also need it to spit out values for Revenue_Sources 1, 2, and 3
by the same row and column headings.

For the second Crosstab Query, you could define a new field in your
Query that would combine the value of [FiscalPeriod] with "_1", "_2", or
"_3" to represent the various [Revenue Source ...] values. For example,
the values of this field might look like

2005_Q3_1
2005_Q3_2
2005_Q3_3
2005_Q4_1
...

and you could populate the body of your datasheet with these values,
again using Sum() or First(), and ignoring the [Total_Revenue] field.

But the column headings would NOT match those in the previous Crosstab
Query if you did this. However, you could define three Crosstab
Queries, one for each of the three [Revenue Source ...] fields. (I'm
not sure why you'd want to do that, though.)



I think the only way is to use union queries but in also doing some
research, I think that I could construct a crosstab query using subqueries
that may be able to do the same thing. Just need a little guidance.

I don't know where you want to go with this, but if you wished, you
could define a Select Query based on these two Crosstab Queries and
display the fields in any order you choose -- but this would be kind of
a high-maintenance kind of Query, as you might need to keep changing the
field names (to match new [FiscalPeriod] values) as time marched onward.
Or you'd need to calculate the SQL via a stored VBA procedure.

I suggest you avoid a Union Query for this, at least if you set up your
CrossTabs as I suggested, as the data you'd be reporting would involve
different numbers of fields (totals vs. 3 individual values).

Maybe if you post an example Select Query Datasheet displaying the data
you wish to start with, and an example Crosstab Query Datasheet, or
maybe a Report, displaying what you'd like the results to look like
based on those same sample data, it would be easier to give you specific
advice.

-- Vincent Johns
Please feel free to quote anything I say here.

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
datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet View, and you'll probably define a Report based
on this, anyway, so the order of fields on the Datasheet will be
immaterial. (I had to move the [RS1]...[RS3] fields that way to put
them at the left end.)

[Q040_BookingsByQuarter_Xtab] Query Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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

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
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
datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet View, and you'll probably define a Report based
on this, anyway, so the order of fields on the Datasheet will be
immaterial. (I had to move the [RS1]...[RS3] fields that way to put
them at the left end.)

[Q040_BookingsByQuarter_Xtab] Query Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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