Crosstab query Date Span

[Marios]

Hello gentlemen.

I Have a crosstab query, which provides sales per month. I have two
questions on that:
1) Is it possible to present the data from Jul - Jun (or any other
combination) instead on Jan - Dec?
2) Is it possible, if I want the sales for a period larger than one calendar
year to have more month columns (e.g. from 06/05 to 02/07, 20 columns)?

Thanks a lot.

 

[Rob Parker]

Hi Mario,

Yes, you can do both things, you ask about. The key to doing so is to use
the Column Headings property of the query itself to define the order, and
content, of what you want to appear. So, for example, to force the data to
display from Jul to Jun, you would enter "Jul, Aug, Sep, ..." in the Column
Headings section of the query's property box. If you look at the SQL view
of the query, this will appear as an IN clause after the PIVOT clause, eg:
...
PIVOT MonthFieldName IN ("Jul","Aug","Sep" ... );

This technique is also useful to force all months to appear in your
crosstab; without it, months with no data will not appear as column
headings.

For your second question, this will only work if your query has both month
and year in the grouping field in your crosstab; it won;t work if you've
reduced this to a month field, such as Jul, Aug, etc as you refer to in your
first question.

HTH,

Rob

 

[Gary Walter]

I Have a crosstab query, which provides sales per month. I have two
questions on that:
1) Is it possible to present the data from Jul - Jun (or any other
combination) instead on Jan - Dec?
2) Is it possible, if I want the sales for a period larger than one
calendar
year to have more month columns (e.g. from 06/05 to 02/07, 20 columns)?

Hi Mario,

You can add a WHERE clause
(using # delimiters and US date format)
to get however many months you want.

TRANSFORM aggregate function
SELECT
some field(s)
FROM
some table(s)
WHERE
[datefield] >=#6/1/2005#
AND
[datefield] < #3/1/2007#
GROUP BY
some fields
PIVOT Format([datefield], "yyyy\/mm")


Typically though queries do not exist
as "an entity in and of themselves."

You usually show your users results
of queries only in a form or report.

That is why one usually uses "relative" months
as column headings, for example
in NorthWind.mdb try:

TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
#3/1/1998# AS LastMnth
FROM Orders
WHERE
Orders.OrderDate >= #6/1/1996#
And
Orders.OrderDate < #3/1/1998#
GROUP BY
Orders.CustomerID,
#3/1/1998#
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],#3/1/1998#),"00");

this means that you can create a 20-column report
based on this crosstab using relative fields

"Mth01", "Mth02" etc

and report labels for these fields can be computed
in a textbox (not label), for example

=Format(DateAdd("m",-1,[LastMnth]), "yyyy\/mm")
=Format(DateAdd("m",-2,[LastMnth]), "yyyy\/mm")

Dates for the 20-month span can change in your xtab,
but your report does not need to be changed because
the field names are relative.


(all thanks go to Duane for this technique...
I'm just parotting)

good luck,

gary

 

[Gary Walter]

If you had a form "frmEndDate"
with a textbox "txtEndDate"
and you entered 2/1998 in textbox,
following should give same 20-month span results:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
Orders.OrderDate>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
Orders.OrderDate<DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
GROUP BY
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],DateAdd("m", 1,
[Forms]!frmEndDate![txtEndDate])),"00");

I Have a crosstab query, which provides sales per month. I have two
questions on that:
1) Is it possible to present the data from Jul - Jun (or any other
combination) instead on Jan - Dec?
2) Is it possible, if I want the sales for a period larger than one
calendar
year to have more month columns (e.g. from 06/05 to 02/07, 20 columns)?

Hi Mario,

You can add a WHERE clause
(using # delimiters and US date format)
to get however many months you want.

TRANSFORM aggregate function
SELECT
some field(s)
FROM
some table(s)
WHERE
[datefield] >=#6/1/2005#
AND
[datefield] < #3/1/2007#
GROUP BY
some fields
PIVOT Format([datefield], "yyyy\/mm")


Typically though queries do not exist
as "an entity in and of themselves."

You usually show your users results
of queries only in a form or report.

That is why one usually uses "relative" months
as column headings, for example
in NorthWind.mdb try:

TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
#3/1/1998# AS LastMnth
FROM Orders
WHERE
Orders.OrderDate >= #6/1/1996#
And
Orders.OrderDate < #3/1/1998#
GROUP BY
Orders.CustomerID,
#3/1/1998#
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],#3/1/1998#),"00");

this means that you can create a 20-column report
based on this crosstab using relative fields

"Mth01", "Mth02" etc

and report labels for these fields can be computed
in a textbox (not label), for example

=Format(DateAdd("m",-1,[LastMnth]), "yyyy\/mm")
=Format(DateAdd("m",-2,[LastMnth]), "yyyy\/mm")

Dates for the 20-month span can change in your xtab,
but your report does not need to be changed because
the field names are relative.


(all thanks go to Duane for this technique...
I'm just parotting)

good luck,

gary

 

[Gary Walter]

Hi Mario,

Maybe you have "moved on" but just in case...

I forgot to explicitly define the column headings
so those fields will *always exist* for your report:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m",1,[Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
(((Orders.OrderDate)>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
(Orders.OrderDate)<DateAdd("m",1,[Forms]![frmEndDate]![txtEndDate])))
GROUP BY
Orders.CustomerID,
DateAdd("m",1,[Forms]!frmEndDate![txtEndDate]),
"Mth" &
Format(DateDiff("m",[OrderDate],DateAdd("m",1,[Forms]!frmEndDate![txtEndDate])),"00")
PIVOT
"Mth" &
Format(DateDiff("m",[OrderDate],DateAdd("m",1,[Forms]!frmEndDate![txtEndDate])),"00")
In
("Mth01","Mth02","Mth03","Mth04","Mth05","Mth06","Mth07","Mth08","Mth09","Mth10",
"Mth11","Mth12","Mth13","Mth14","Mth15","Mth16","Mth17","Mth18","Mth19","Mth20");

Note that previously w/o column headings
the PIVOT expression was just an "Expression"
but when add column headings,
the PIVOT expression must be included in GROUP BY clause.

good luck,

gary

If you had a form "frmEndDate"
with a textbox "txtEndDate"
and you entered 2/1998 in textbox,
following should give same 20-month span results:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
Orders.OrderDate>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
Orders.OrderDate<DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
GROUP BY
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],DateAdd("m", 1,
[Forms]!frmEndDate![txtEndDate])),"00");

I Have a crosstab query, which provides sales per month. I have two
questions on that:
1) Is it possible to present the data from Jul - Jun (or any other
combination) instead on Jan - Dec?
2) Is it possible, if I want the sales for a period larger than one
calendar
year to have more month columns (e.g. from 06/05 to 02/07, 20 columns)?

Hi Mario,

You can add a WHERE clause
(using # delimiters and US date format)
to get however many months you want.

TRANSFORM aggregate function
SELECT
some field(s)
FROM
some table(s)
WHERE
[datefield] >=#6/1/2005#
AND
[datefield] < #3/1/2007#
GROUP BY
some fields
PIVOT Format([datefield], "yyyy\/mm")


Typically though queries do not exist
as "an entity in and of themselves."

You usually show your users results
of queries only in a form or report.

That is why one usually uses "relative" months
as column headings, for example
in NorthWind.mdb try:

TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
#3/1/1998# AS LastMnth
FROM Orders
WHERE
Orders.OrderDate >= #6/1/1996#
And
Orders.OrderDate < #3/1/1998#
GROUP BY
Orders.CustomerID,
#3/1/1998#
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],#3/1/1998#),"00");

this means that you can create a 20-column report
based on this crosstab using relative fields

"Mth01", "Mth02" etc

and report labels for these fields can be computed
in a textbox (not label), for example

=Format(DateAdd("m",-1,[LastMnth]), "yyyy\/mm")
=Format(DateAdd("m",-2,[LastMnth]), "yyyy\/mm")

Dates for the 20-month span can change in your xtab,
but your report does not need to be changed because
the field names are relative.


(all thanks go to Duane for this technique...
I'm just parotting)

good luck,

gary

 

[Marios]

Gary,

Actually I do have a "FromDate" and a "ToDate" text box. Does this change my
SQL query?

Ο χÏήστης "Gary Walter" έγγÏαψε:

Hi Mario,

Maybe you have "moved on" but just in case...

I forgot to explicitly define the column headings
so those fields will *always exist* for your report:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m",1,[Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
(((Orders.OrderDate)>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
(Orders.OrderDate)<DateAdd("m",1,[Forms]![frmEndDate]![txtEndDate])))
GROUP BY
Orders.CustomerID,
DateAdd("m",1,[Forms]!frmEndDate![txtEndDate]),
"Mth" &
Format(DateDiff("m",[OrderDate],DateAdd("m",1,[Forms]!frmEndDate![txtEndDate])),"00")
PIVOT
"Mth" &
Format(DateDiff("m",[OrderDate],DateAdd("m",1,[Forms]!frmEndDate![txtEndDate])),"00")
In
("Mth01","Mth02","Mth03","Mth04","Mth05","Mth06","Mth07","Mth08","Mth09","Mth10",
"Mth11","Mth12","Mth13","Mth14","Mth15","Mth16","Mth17","Mth18","Mth19","Mth20");

Note that previously w/o column headings
the PIVOT expression was just an "Expression"
but when add column headings,
the PIVOT expression must be included in GROUP BY clause.

good luck,

gary

If you had a form "frmEndDate"
with a textbox "txtEndDate"
and you entered 2/1998 in textbox,
following should give same 20-month span results:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
Orders.OrderDate>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
Orders.OrderDate<DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
GROUP BY
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],DateAdd("m", 1,
[Forms]!frmEndDate![txtEndDate])),"00");

"Marios" wrote:
I Have a crosstab query, which provides sales per month. I have two
questions on that:
1) Is it possible to present the data from Jul - Jun (or any other
combination) instead on Jan - Dec?
2) Is it possible, if I want the sales for a period larger than one
calendar
year to have more month columns (e.g. from 06/05 to 02/07, 20 columns)?

Hi Mario,

You can add a WHERE clause
(using # delimiters and US date format)
to get however many months you want.

TRANSFORM aggregate function
SELECT
some field(s)
FROM
some table(s)
WHERE
[datefield] >=#6/1/2005#
AND
[datefield] < #3/1/2007#
GROUP BY
some fields
PIVOT Format([datefield], "yyyy\/mm")


Typically though queries do not exist
as "an entity in and of themselves."

You usually show your users results
of queries only in a form or report.

That is why one usually uses "relative" months
as column headings, for example
in NorthWind.mdb try:

TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
#3/1/1998# AS LastMnth
FROM Orders
WHERE
Orders.OrderDate >= #6/1/1996#
And
Orders.OrderDate < #3/1/1998#
GROUP BY
Orders.CustomerID,
#3/1/1998#
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],#3/1/1998#),"00");

this means that you can create a 20-column report
based on this crosstab using relative fields

"Mth01", "Mth02" etc

and report labels for these fields can be computed
in a textbox (not label), for example

=Format(DateAdd("m",-1,[LastMnth]), "yyyy\/mm")
=Format(DateAdd("m",-2,[LastMnth]), "yyyy\/mm")

Dates for the 20-month span can change in your xtab,
but your report does not need to be changed because
the field names are relative.


(all thanks go to Duane for this technique...
I'm just parotting)

good luck,

gary

 

[Gary Walter]

Hi Marios,

I don't know how to answer that (or a
philosopher might say "mu" - the question
is too big).


If this query is for a report, then that report
expects its textboxes to be bound to certain
fields of certain names.

One could jump through a whole bunch of hoops
to work around variable columns from a crosstab
in a report, but it will be a lot of work.

I guess I would sit down and ask my self what
the user really needs. What is this crosstab for?

Could you meet the users' needs with a 12-month
and a 20-month report?

Actually...first, is this for a report?

If not, what is it for?

for "just a crosstab" with *variable fields*...
where you set the From and To in a form
"frmDateParameters" via textboxes
"txtFromDate" and "txtToDate," a simple
starting point using our previous example
would be:

PARAMETERS
[Forms]!frmDateParameters![txtFromDate] DateTime,
[Forms]!frmDateParameters![txtToDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
[Forms]!frmDateParameters![txtFromDate] As StrDte,
[Forms]!frmDateParameters![txtToDate] As EndDte
FROM Orders
WHERE
Orders.OrderDate >= [Forms]!frmDateParameters![txtFromDate]
And
Orders.OrderDate <= [Forms]!frmDateParameters![txtToDate]
GROUP BY
Orders.CustomerID,
[Forms]!frmDateParameters![txtFromDate],
[Forms]!frmDateParameters![txtToDate]
PIVOT
"Mth" &
Format(DateDiff("m",[OrderDate],
[Forms]!frmDateParameters![txtToDate])),"00")

Note that the txtToDate will have to be
the last day of the month to get aggregation
over that entire last month.

Likewise, the txtFromDate should be the first day
of the month to get aggregation over that entire
first month.

Actually I do have a "FromDate" and a "ToDate" text box. Does this change
my
SQL query?

? ??????? "Gary Walter" ???????:

Hi Mario,

Maybe you have "moved on" but just in case...

I forgot to explicitly define the column headings
so those fields will *always exist* for your report:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m",1,[Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
(((Orders.OrderDate)>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
(Orders.OrderDate)<DateAdd("m",1,[Forms]![frmEndDate]![txtEndDate])))
GROUP BY
Orders.CustomerID,
DateAdd("m",1,[Forms]!frmEndDate![txtEndDate]),
"Mth" &
Format(DateDiff("m",[OrderDate],DateAdd("m",1,[Forms]!frmEndDate![txtEndDate])),"00")
PIVOT
"Mth" &
Format(DateDiff("m",[OrderDate],DateAdd("m",1,[Forms]!frmEndDate![txtEndDate])),"00")
In
("Mth01","Mth02","Mth03","Mth04","Mth05","Mth06","Mth07","Mth08","Mth09","Mth10",
"Mth11","Mth12","Mth13","Mth14","Mth15","Mth16","Mth17","Mth18","Mth19","Mth20");

Note that previously w/o column headings
the PIVOT expression was just an "Expression"
but when add column headings,
the PIVOT expression must be included in GROUP BY clause.

good luck,

gary

If you had a form "frmEndDate"
with a textbox "txtEndDate"
and you entered 2/1998 in textbox,
following should give same 20-month span results:

PARAMETERS [Forms]!frmEndDate![txtEndDate] DateTime;
TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate]) AS LastMnth
FROM Orders
WHERE
Orders.OrderDate>=DateAdd("m",-20,[Forms]![frmEndDate]![txtEndDate])
And
Orders.OrderDate<DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
GROUP BY
Orders.CustomerID,
DateAdd("m", 1, [Forms]!frmEndDate![txtEndDate])
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],DateAdd("m", 1,
[Forms]!frmEndDate![txtEndDate])),"00");

:
"Marios" wrote:
I Have a crosstab query, which provides sales per month. I have two
questions on that:
1) Is it possible to present the data from Jul - Jun (or any other
combination) instead on Jan - Dec?
2) Is it possible, if I want the sales for a period larger than one
calendar
year to have more month columns (e.g. from 06/05 to 02/07, 20
columns)?

Hi Mario,

You can add a WHERE clause
(using # delimiters and US date format)
to get however many months you want.

TRANSFORM aggregate function
SELECT
some field(s)
FROM
some table(s)
WHERE
[datefield] >=#6/1/2005#
AND
[datefield] < #3/1/2007#
GROUP BY
some fields
PIVOT Format([datefield], "yyyy\/mm")


Typically though queries do not exist
as "an entity in and of themselves."

You usually show your users results
of queries only in a form or report.

That is why one usually uses "relative" months
as column headings, for example
in NorthWind.mdb try:

TRANSFORM NZ(Count(*),0) AS Cnt
SELECT
Orders.CustomerID,
#3/1/1998# AS LastMnth
FROM Orders
WHERE
Orders.OrderDate >= #6/1/1996#
And
Orders.OrderDate < #3/1/1998#
GROUP BY
Orders.CustomerID,
#3/1/1998#
PIVOT
"Mth" & Format(DateDiff("m",[OrderDate],#3/1/1998#),"00");

this means that you can create a 20-column report
based on this crosstab using relative fields

"Mth01", "Mth02" etc

and report labels for these fields can be computed
in a textbox (not label), for example

=Format(DateAdd("m",-1,[LastMnth]), "yyyy\/mm")
=Format(DateAdd("m",-2,[LastMnth]), "yyyy\/mm")

Dates for the 20-month span can change in your xtab,
but your report does not need to be changed because
the field names are relative.


(all thanks go to Duane for this technique...
I'm just parotting)

good luck,

gary

 

[john431]

I have found a simple solution to your problem. Use the Pivot Table query
mode. First, run the query and set the criteria for the "Date" field to the
range of dates you want in the Pivot Table. Then switch to Pivot Table view
and set it up to have year and month in the column headings. If the months
don't start where you want it to, just click on the month column and move
each of them to the proper position in your Pivot Table. This will work with
more than 12 months as long as you include the year in the column heading.

John