Crosstab with multiple Column Header Fields

R

RV Chris

I have created a Sales Comparison query that results in something like
the following:
(The first two columns compare last year with this year. The second 2
columns compare last month with the same month last year.)

CompanyName 2005 2006 200505 200605 Change
abc $777 $999 $22 $33 1.5
def $2929 $3999 $499 $899 1.8

It takes 3 queries to get here - 2 crosstabs and one select query that
puts the 2 crosstabs together, joined by the CompanyID.
1. qrySalesCompareAnnual (a crosstab)
2. qrySalesCompareMonthly (another crosstab)
3. qrySalesCompareAll (a select query that joins the two above)

It works beautiful, but it won't work next month, because the fields
200505 and 200605 won't exist. Next month, qrySalesCompareAll needs to
refer to 200506 and 200606.

Here's the SQL for qrySalesCompareAll

SELECT qrySalesCompareAnnual.*, qrySalesCompareMonthly.[200505],
qrySalesCompareMonthly.[200605], [200605]/[200505] AS Change
FROM qrySalesCompareAnnual INNER JOIN qrySalesCompareMonthly ON
qrySalesCompareAnnual.CoID = qrySalesCompareMonthly.CoID;

I can make a form which has a field for the current months, but I can't
figure out how to refer to that parameter as a field in my select
query. In my form, txtMonth = qrySalesCompareMonthly.[200605].

But, replacing qrySalesCompareMonthly.[200605] with
[Forms]![frmSalesComparison]![txtMonth] doesn't work.

Am I on the right track? Or is there another way to approach this.

Thanx!
Chris
 
G

Gary Walter

RV Chris said:
I have created a Sales Comparison query that results in something like
the following:
(The first two columns compare last year with this year. The second 2
columns compare last month with the same month last year.)

CompanyName 2005 2006 200505 200605 Change
abc $777 $999 $22 $33 1.5
def $2929 $3999 $499 $899 1.8

It takes 3 queries to get here - 2 crosstabs and one select query that
puts the 2 crosstabs together, joined by the CompanyID.
1. qrySalesCompareAnnual (a crosstab)
2. qrySalesCompareMonthly (another crosstab)
3. qrySalesCompareAll (a select query that joins the two above)

It works beautiful, but it won't work next month, because the fields
200505 and 200605 won't exist. Next month, qrySalesCompareAll needs to
refer to 200506 and 200606.

Here's the SQL for qrySalesCompareAll

SELECT qrySalesCompareAnnual.*, qrySalesCompareMonthly.[200505],
qrySalesCompareMonthly.[200605], [200605]/[200505] AS Change
FROM qrySalesCompareAnnual INNER JOIN qrySalesCompareMonthly ON
qrySalesCompareAnnual.CoID = qrySalesCompareMonthly.CoID;

I can make a form which has a field for the current months, but I can't
figure out how to refer to that parameter as a field in my select
query. In my form, txtMonth = qrySalesCompareMonthly.[200605].

But, replacing qrySalesCompareMonthly.[200605] with
[Forms]![frmSalesComparison]![txtMonth] doesn't work.

Am I on the right track? Or is there another way to approach this.
Click to expand...
Hi Chris,

I have to imagine this is for a report...
if so, I would go the "general" path,
then change text labels in report.

start with original table
and sum sales in subqueries
that filter for

this year
last year
this month
last month

w/o seeing your data...

SELECT
t.CompanyName,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
Year(Date) =Year(q.SalesDate)
AND
q.CompanyName = t.CompantName) As ThisYear,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
Year(Date) - 1 =Year(q.SalesDate)
AND
q.CompanyName = t.CompantName) As LastYear,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
DateSerial(Year(Date),Month(Date),1) =
DateSerial(Year(q.SalesDate),Month(q.SalesDate),1)
AND
q.CompanyName = t.CompantName) As ThisMonth,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
DateSerial(Year(Date),Month(Date) - 1,1) =
DateSerial(Year(q.SalesDate),Month(q.SalesDate),1)
AND
q.CompanyName = t.CompantName) As LastMonth,
(ThisYear - LastYear)/LastYear As YearChange,
(ThisMonth - LastMonth)/LastMonth As MonthChange
FROM yourtable As t
GROUP BY
t.CompanyName

Then in report, in place of labels,
use claculated textboxes, i.e,

textbox (label) over ThisYear
=Year(Date)

textbox (label) over LastYear
=Year(Date)-1

textbox (label) over ThisMonth
= Format(Date,"yyyymm")

textbox (label) over LastMonth
= Format(DateSerial(Year(Date)-1,Month(Date),1),"yyyymm")
 
G

Gary Walter

correction:

textbox (label) over LastMonth
= Format(DateSerial(Year(Date),Month(Date)-1,1),"yyyymm")

Gary Walter said:
RV Chris said:
I have created a Sales Comparison query that results in something like
the following:
(The first two columns compare last year with this year. The second 2
columns compare last month with the same month last year.)

CompanyName 2005 2006 200505 200605 Change
abc $777 $999 $22 $33 1.5
def $2929 $3999 $499 $899 1.8

It takes 3 queries to get here - 2 crosstabs and one select query that
puts the 2 crosstabs together, joined by the CompanyID.
1. qrySalesCompareAnnual (a crosstab)
2. qrySalesCompareMonthly (another crosstab)
3. qrySalesCompareAll (a select query that joins the two above)

It works beautiful, but it won't work next month, because the fields
200505 and 200605 won't exist. Next month, qrySalesCompareAll needs to
refer to 200506 and 200606.

Here's the SQL for qrySalesCompareAll

SELECT qrySalesCompareAnnual.*, qrySalesCompareMonthly.[200505],
qrySalesCompareMonthly.[200605], [200605]/[200505] AS Change
FROM qrySalesCompareAnnual INNER JOIN qrySalesCompareMonthly ON
qrySalesCompareAnnual.CoID = qrySalesCompareMonthly.CoID;

I can make a form which has a field for the current months, but I can't
figure out how to refer to that parameter as a field in my select
query. In my form, txtMonth = qrySalesCompareMonthly.[200605].

But, replacing qrySalesCompareMonthly.[200605] with
[Forms]![frmSalesComparison]![txtMonth] doesn't work.

Am I on the right track? Or is there another way to approach this.
Click to expand...
Hi Chris,

I have to imagine this is for a report...
if so, I would go the "general" path,
then change text labels in report.

start with original table
and sum sales in subqueries
that filter for

this year
last year
this month
last month

w/o seeing your data...

SELECT
t.CompanyName,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
Year(Date) =Year(q.SalesDate)
AND
q.CompanyName = t.CompantName) As ThisYear,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
Year(Date) - 1 =Year(q.SalesDate)
AND
q.CompanyName = t.CompantName) As LastYear,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
DateSerial(Year(Date),Month(Date),1) =
DateSerial(Year(q.SalesDate),Month(q.SalesDate),1)
AND
q.CompanyName = t.CompantName) As ThisMonth,
(SELECT Sum(q.Sales)
FROM yourtable As q
WHERE
DateSerial(Year(Date),Month(Date) - 1,1) =
DateSerial(Year(q.SalesDate),Month(q.SalesDate),1)
AND
q.CompanyName = t.CompantName) As LastMonth,
(ThisYear - LastYear)/LastYear As YearChange,
(ThisMonth - LastMonth)/LastMonth As MonthChange
FROM yourtable As t
GROUP BY
t.CompanyName

Then in report, in place of labels,
use claculated textboxes, i.e,

textbox (label) over ThisYear
=Year(Date)

textbox (label) over LastYear
=Year(Date)-1

textbox (label) over ThisMonth
= Format(Date,"yyyymm")

textbox (label) over LastMonth
= Format(DateSerial(Year(Date)-1,Month(Date),1),"yyyymm")
Click to expand...
 
D

Duane Hookom

You could do this all with one totals query (do the change calculation in
the report).
For instance in the Northwind database, if you want to get Sales by employee
for:
- All of last year
- All of this year
- This month last year
- This month This year
The following SQL combines just the Orders and Order Details tables. Since
the Northwind data is old, you would need to substitute Date() in place of
1/1/1998. Columns are calculated by creating a true or false expression for
each date range (month or year) and multiplying its absolute value times the
sales amount.

You can then use text boxes as column headings in your report that calculate
and format the date ranges.

SELECT Orders.EmployeeID,
Sum(Abs(Year([OrderDate])+1=Year(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS LastYear,
Sum(Abs(Year([OrderDate])=Year(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS ThisYear,
Sum(Abs(Year([OrderDate])+1=Year(#1/1/1998#) And
Month([Orderdate])=Month(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS MonthLastYear,
Sum(Abs(Year([OrderDate])=Year(#1/1/1998#) And
Month([Orderdate])=Month(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS MonthThisYear
FROM Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.EmployeeID;
--
Duane Hookom
MS Access MVP


RV Chris said:
I have created a Sales Comparison query that results in something like
the following:
(The first two columns compare last year with this year. The second 2
columns compare last month with the same month last year.)

CompanyName 2005 2006 200505 200605 Change
abc $777 $999 $22 $33 1.5
def $2929 $3999 $499 $899 1.8

It takes 3 queries to get here - 2 crosstabs and one select query that
puts the 2 crosstabs together, joined by the CompanyID.
1. qrySalesCompareAnnual (a crosstab)
2. qrySalesCompareMonthly (another crosstab)
3. qrySalesCompareAll (a select query that joins the two above)

It works beautiful, but it won't work next month, because the fields
200505 and 200605 won't exist. Next month, qrySalesCompareAll needs to
refer to 200506 and 200606.

Here's the SQL for qrySalesCompareAll

SELECT qrySalesCompareAnnual.*, qrySalesCompareMonthly.[200505],
qrySalesCompareMonthly.[200605], [200605]/[200505] AS Change
FROM qrySalesCompareAnnual INNER JOIN qrySalesCompareMonthly ON
qrySalesCompareAnnual.CoID = qrySalesCompareMonthly.CoID;

I can make a form which has a field for the current months, but I can't
figure out how to refer to that parameter as a field in my select
query. In my form, txtMonth = qrySalesCompareMonthly.[200605].

But, replacing qrySalesCompareMonthly.[200605] with
[Forms]![frmSalesComparison]![txtMonth] doesn't work.

Am I on the right track? Or is there another way to approach this.

Thanx!
Chris
Click to expand...
 
C

Christine King

Another thing that might help. you can't put a criteria from a form in a
crosstab query because it doesn't work. however, in this example, if you add
the form controlname - in this case

[Forms]![frmSalesComparison]![txtMonth]

exactly as-is, as a query parameter, and give the correct data-type. Then it
does. Don't ask me why!
--
Cheers,
Christine King



Duane Hookom said:
You could do this all with one totals query (do the change calculation in
the report).
For instance in the Northwind database, if you want to get Sales by employee
for:
- All of last year
- All of this year
- This month last year
- This month This year
The following SQL combines just the Orders and Order Details tables. Since
the Northwind data is old, you would need to substitute Date() in place of
1/1/1998. Columns are calculated by creating a true or false expression for
each date range (month or year) and multiplying its absolute value times the
sales amount.

You can then use text boxes as column headings in your report that calculate
and format the date ranges.

SELECT Orders.EmployeeID,
Sum(Abs(Year([OrderDate])+1=Year(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS LastYear,
Sum(Abs(Year([OrderDate])=Year(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS ThisYear,
Sum(Abs(Year([OrderDate])+1=Year(#1/1/1998#) And
Month([Orderdate])=Month(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS MonthLastYear,
Sum(Abs(Year([OrderDate])=Year(#1/1/1998#) And
Month([Orderdate])=Month(#1/1/1998#))*
[UnitPrice]*[Quantity]) AS MonthThisYear
FROM Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.EmployeeID;
--
Duane Hookom
MS Access MVP


RV Chris said:
I have created a Sales Comparison query that results in something like
the following:
(The first two columns compare last year with this year. The second 2
columns compare last month with the same month last year.)

CompanyName 2005 2006 200505 200605 Change
abc $777 $999 $22 $33 1.5
def $2929 $3999 $499 $899 1.8

It takes 3 queries to get here - 2 crosstabs and one select query that
puts the 2 crosstabs together, joined by the CompanyID.
1. qrySalesCompareAnnual (a crosstab)
2. qrySalesCompareMonthly (another crosstab)
3. qrySalesCompareAll (a select query that joins the two above)

It works beautiful, but it won't work next month, because the fields
200505 and 200605 won't exist. Next month, qrySalesCompareAll needs to
refer to 200506 and 200606.

Here's the SQL for qrySalesCompareAll

SELECT qrySalesCompareAnnual.*, qrySalesCompareMonthly.[200505],
qrySalesCompareMonthly.[200605], [200605]/[200505] AS Change
FROM qrySalesCompareAnnual INNER JOIN qrySalesCompareMonthly ON
qrySalesCompareAnnual.CoID = qrySalesCompareMonthly.CoID;

I can make a form which has a field for the current months, but I can't
figure out how to refer to that parameter as a field in my select
query. In my form, txtMonth = qrySalesCompareMonthly.[200605].

But, replacing qrySalesCompareMonthly.[200605] with
[Forms]![frmSalesComparison]![txtMonth] doesn't work.

Am I on the right track? Or is there another way to approach this.

Thanx!
Chris
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CrossTab Combining 2 fields into Column heading 1
Crosstab with multiple fields 2
Crosstab - Multiple values on each row 5
Crosstab Query - Column sorting problem - Ascending 3
Crosstab Conundrum 4
IIF in Crosstab Expression 0
Dynamic crosstab query with multiple values 3
Help with crosstab query 2

Top