YearMonth as column Headings

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

How can I produce column headings consisting of YearMonth for all of 2004?
Starting at 200401 to 200412, or some other combination.

I have data that will crosstab easily with those column names but I have to
account for any instances where data is not present for a particular month
and therefore will not be one of the headings. I could design a query with
column names already in place, however it would not be dynamic and would
require changing for another year.

Is there a function that will give me the 12 values in a column that I can
crosstab to give the required results?

Regards
 
Beware, generic response to follow. If you need something more specific,
just ask...

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
 
Hi Duane,

Not quite what I needed, however your example did spark off another train of
thought to give the following.

There is a table that has all invoiced amounts and dates for Projects. So to
get some raw data for 2004 use a simple aggregate query,
qry_rpt_Scheduled_Invoicing1, to sum the amounts using a criteria to filter
only year 2004 and returning the date in the form 2004xx , where xx is the
month number.

In the crosstab using qry_rpt_Scheduled_Invoicing1, some Projects may not
have sums in some of the yyyymm months...problem!...because if a particular
month does not have any sums from any of the Projects then it will be
excluded from the crosstab...bad news..no October?.. but need to report zero
amount on the report!

The solution is peculiar but works. Produce 12 separate querys to generate
12 consecutive month results between them with zero sums, then use a Union
query to join qry_rpt_Scheduled_Invoicing1 and all 12 dummy data querys. Use
the output of the Union query in the crosstab. Below is the first dummy data
query SQL, note that I had to include qry_rpt_Scheduled_Invoicing1 and
choose an abitrary Project from it to ensure I had a valid Project that
would have a sum in the final crosstab. The distinct is necessary to curtail
duplicate output lines as only one is needed.

SELECT DISTINCT Min([qry_rpt_Scheduled_Invoicing1]![ProjectNumber]) AS
ProjectNumber, (Year(Date())*100)+1 AS MonthYear, 0 AS InvoiceAmountEst, 0
AS InvoiceAmountAct
FROM qry_rpt_Scheduled_Invoicing1;

To get the other months, the above is January, change the
(Year(Date())*100)+1 AS MonthYear to (Year(Date())*100)+2 AS MonthYear to
get February and so on to complete the 12 querys in all.

The Union looks like this, sorry it's longish:
SELECT *
FROM qry_rpt_Scheduled_Invoicing1
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U01
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U02
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U03
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U04
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U05
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U06
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U07
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U08
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U09
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U10
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U11
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U12;

This takes the output from qry_rpt_Scheduled_Invoicing1 and adds 12 dummy
data lines with 200401 to 200412, all with zero sum totals and therefore no
affect on the overall result for the Project concerned. Now feed that into
the Crosstab and hey-presto column names from 200401 to 200412 with sum
totals below with the dummy data ensuring that all months are represented.

UNLESS there is an easier way to produce the 12 dummy data lines in just 1
query :-)

I seen this type of problem in the newsgroup before, that's why I have
included this detailed method that works for me.

Regards










Duane Hookom said:
Beware, generic response to follow. If you need something more specific,
just ask...

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP
--

Terry said:
How can I produce column headings consisting of YearMonth for all of
2004?
Starting at 200401 to 200412, or some other combination.

I have data that will crosstab easily with those column names but I have to
account for any instances where data is not present for a particular
month
and therefore will not be one of the headings. I could design a query
with
column names already in place, however it would not be dynamic and would
require changing for another year.

Is there a function that will give me the 12 values in a column that I
can
crosstab to give the required results?

Regards
Click to expand...
Click to expand...
 
I don't know what you mean by "Not quite what I needed". It seems to me that
the solution I suggested will create month columns for all months for the
year.

--
Duane Hookom
MS Access MVP
--

Terry said:
Hi Duane,

Not quite what I needed, however your example did spark off another train of
thought to give the following.

There is a table that has all invoiced amounts and dates for Projects. So to
get some raw data for 2004 use a simple aggregate query,
qry_rpt_Scheduled_Invoicing1, to sum the amounts using a criteria to filter
only year 2004 and returning the date in the form 2004xx , where xx is the
month number.

In the crosstab using qry_rpt_Scheduled_Invoicing1, some Projects may not
have sums in some of the yyyymm months...problem!...because if a particular
month does not have any sums from any of the Projects then it will be
excluded from the crosstab...bad news..no October?.. but need to report zero
amount on the report!

The solution is peculiar but works. Produce 12 separate querys to generate
12 consecutive month results between them with zero sums, then use a Union
query to join qry_rpt_Scheduled_Invoicing1 and all 12 dummy data querys. Use
the output of the Union query in the crosstab. Below is the first dummy data
query SQL, note that I had to include qry_rpt_Scheduled_Invoicing1 and
choose an abitrary Project from it to ensure I had a valid Project that
would have a sum in the final crosstab. The distinct is necessary to curtail
duplicate output lines as only one is needed.

SELECT DISTINCT Min([qry_rpt_Scheduled_Invoicing1]![ProjectNumber]) AS
ProjectNumber, (Year(Date())*100)+1 AS MonthYear, 0 AS InvoiceAmountEst, 0
AS InvoiceAmountAct
FROM qry_rpt_Scheduled_Invoicing1;

To get the other months, the above is January, change the
(Year(Date())*100)+1 AS MonthYear to (Year(Date())*100)+2 AS MonthYear to
get February and so on to complete the 12 querys in all.

The Union looks like this, sorry it's longish:
SELECT *
FROM qry_rpt_Scheduled_Invoicing1
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U01
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U02
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U03
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U04
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U05
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U06
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U07
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U08
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U09
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U10
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U11
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U12;

This takes the output from qry_rpt_Scheduled_Invoicing1 and adds 12 dummy
data lines with 200401 to 200412, all with zero sum totals and therefore no
affect on the overall result for the Project concerned. Now feed that into
the Crosstab and hey-presto column names from 200401 to 200412 with sum
totals below with the dummy data ensuring that all months are represented.

UNLESS there is an easier way to produce the 12 dummy data lines in just 1
query :-)

I seen this type of problem in the newsgroup before, that's why I have
included this detailed method that works for me.

Regards










Duane Hookom said:
Beware, generic response to follow. If you need something more specific,
just ask...

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP
--

Terry said:
How can I produce column headings consisting of YearMonth for all of
2004?
Starting at 200401 to 200412, or some other combination.

I have data that will crosstab easily with those column names but I
Click to expand...
have
to
account for any instances where data is not present for a particular
month
and therefore will not be one of the headings. I could design a query
with
column names already in place, however it would not be dynamic and would
require changing for another year.

Is there a function that will give me the 12 values in a column that I
can
crosstab to give the required results?

Regards
Click to expand...
Click to expand...
Click to expand...
 
Your absolutely correct in that, with one proviso. If there are no sales in
a given month that month will not be represented in the query output. It's
still a useful query in as much it converts dates to monthly representation
and with a little more code to handle the swing about Mth0 'December' it
could be used.

The problem I brought to light was that special case of no invoices at all
in a particular month. This meant that I had to inject dummy data for each
month to ensure that all months appeared regardles.

12 queries to do that is probably 'over the top' and I reckon I could
probably get that down to 1 query + 1 function.

The other reason for representing months and years as a numeric yyyymm is
that in the final report the columns will be headed Jan-04 Feb-04 etc, which
do not sort to well due to the alpha-numeric nature. Whereas a numeric
representation will and with a little tinkering in the report can be made to
show Jan-04 onwards.

Thanks for the help
Regards


Duane Hookom said:
I don't know what you mean by "Not quite what I needed". It seems to me
that
the solution I suggested will create month columns for all months for the
year.

--
Duane Hookom
MS Access MVP
--

Terry said:
Hi Duane,

Not quite what I needed, however your example did spark off another train of
thought to give the following.

There is a table that has all invoiced amounts and dates for Projects. So to
get some raw data for 2004 use a simple aggregate query,
qry_rpt_Scheduled_Invoicing1, to sum the amounts using a criteria to filter
only year 2004 and returning the date in the form 2004xx , where xx is
the
month number.

In the crosstab using qry_rpt_Scheduled_Invoicing1, some Projects may not
have sums in some of the yyyymm months...problem!...because if a particular
month does not have any sums from any of the Projects then it will be
excluded from the crosstab...bad news..no October?.. but need to report zero
amount on the report!

The solution is peculiar but works. Produce 12 separate querys to
generate
12 consecutive month results between them with zero sums, then use a
Union
query to join qry_rpt_Scheduled_Invoicing1 and all 12 dummy data querys. Use
the output of the Union query in the crosstab. Below is the first dummy data
query SQL, note that I had to include qry_rpt_Scheduled_Invoicing1 and
choose an abitrary Project from it to ensure I had a valid Project that
would have a sum in the final crosstab. The distinct is necessary to curtail
duplicate output lines as only one is needed.

SELECT DISTINCT Min([qry_rpt_Scheduled_Invoicing1]![ProjectNumber]) AS
ProjectNumber, (Year(Date())*100)+1 AS MonthYear, 0 AS InvoiceAmountEst,
0
AS InvoiceAmountAct
FROM qry_rpt_Scheduled_Invoicing1;

To get the other months, the above is January, change the
(Year(Date())*100)+1 AS MonthYear to (Year(Date())*100)+2 AS MonthYear to
get February and so on to complete the 12 querys in all.

The Union looks like this, sorry it's longish:
SELECT *
FROM qry_rpt_Scheduled_Invoicing1
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U01
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U02
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U03
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U04
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U05
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U06
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U07
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U08
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U09
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U10
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U11
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U12;

This takes the output from qry_rpt_Scheduled_Invoicing1 and adds 12 dummy
data lines with 200401 to 200412, all with zero sum totals and therefore no
affect on the overall result for the Project concerned. Now feed that
into
the Crosstab and hey-presto column names from 200401 to 200412 with sum
totals below with the dummy data ensuring that all months are
represented.

UNLESS there is an easier way to produce the 12 dummy data lines in just
1
query :-)

I seen this type of problem in the newsgroup before, that's why I have
included this detailed method that works for me.

Regards










Duane Hookom said:
Beware, generic response to follow. If you need something more
specific,
just ask...

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP
--

How can I produce column headings consisting of YearMonth for all of
2004?
Starting at 200401 to 200412, or some other combination.

I have data that will crosstab easily with those column names but I have
to
account for any instances where data is not present for a particular
month
and therefore will not be one of the headings. I could design a query
with
column names already in place, however it would not be dynamic and would
require changing for another year.

Is there a function that will give me the 12 values in a column that I
can
crosstab to give the required results?

Regards
Click to expand...
Click to expand...
Click to expand...
 
Two points:
1) My suggestion will have all months represented regardless of your data.
From my previous posting:
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
2) I'm not sure what you mean by "sort to well" since the months I assumed
were column headings. The control sources that I suggested resolve as Date
values which can be formatted in any date format you require.
--
Duane Hookom
MS Access MVP


Terry said:
Your absolutely correct in that, with one proviso. If there are no sales in
a given month that month will not be represented in the query output. It's
still a useful query in as much it converts dates to monthly representation
and with a little more code to handle the swing about Mth0 'December' it
could be used.

The problem I brought to light was that special case of no invoices at all
in a particular month. This meant that I had to inject dummy data for each
month to ensure that all months appeared regardles.

12 queries to do that is probably 'over the top' and I reckon I could
probably get that down to 1 query + 1 function.

The other reason for representing months and years as a numeric yyyymm is
that in the final report the columns will be headed Jan-04 Feb-04 etc, which
do not sort to well due to the alpha-numeric nature. Whereas a numeric
representation will and with a little tinkering in the report can be made to
show Jan-04 onwards.

Thanks for the help
Regards


Duane Hookom said:
I don't know what you mean by "Not quite what I needed". It seems to me
that
the solution I suggested will create month columns for all months for the
year.

--
Duane Hookom
MS Access MVP
--

Terry said:
Hi Duane,

Not quite what I needed, however your example did spark off another
Click to expand...
train
of
thought to give the following.

There is a table that has all invoiced amounts and dates for Projects.
Click to expand...
So
to
get some raw data for 2004 use a simple aggregate query,
qry_rpt_Scheduled_Invoicing1, to sum the amounts using a criteria to filter
only year 2004 and returning the date in the form 2004xx , where xx is
the
month number.

In the crosstab using qry_rpt_Scheduled_Invoicing1, some Projects may not
have sums in some of the yyyymm months...problem!...because if a particular
month does not have any sums from any of the Projects then it will be
excluded from the crosstab...bad news..no October?.. but need to report zero
amount on the report!

The solution is peculiar but works. Produce 12 separate querys to
generate
12 consecutive month results between them with zero sums, then use a
Union
query to join qry_rpt_Scheduled_Invoicing1 and all 12 dummy data
Click to expand...
querys.
Use
the output of the Union query in the crosstab. Below is the first dummy data
query SQL, note that I had to include qry_rpt_Scheduled_Invoicing1 and
choose an abitrary Project from it to ensure I had a valid Project that
would have a sum in the final crosstab. The distinct is necessary to curtail
duplicate output lines as only one is needed.

SELECT DISTINCT Min([qry_rpt_Scheduled_Invoicing1]![ProjectNumber]) AS
ProjectNumber, (Year(Date())*100)+1 AS MonthYear, 0 AS InvoiceAmountEst,
0
AS InvoiceAmountAct
FROM qry_rpt_Scheduled_Invoicing1;

To get the other months, the above is January, change the
(Year(Date())*100)+1 AS MonthYear to (Year(Date())*100)+2 AS MonthYear to
get February and so on to complete the 12 querys in all.

The Union looks like this, sorry it's longish:
SELECT *
FROM qry_rpt_Scheduled_Invoicing1
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U01
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U02
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U03
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U04
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U05
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U06
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U07
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U08
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U09
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U10
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U11
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U12;

This takes the output from qry_rpt_Scheduled_Invoicing1 and adds 12 dummy
data lines with 200401 to 200412, all with zero sum totals and
Click to expand...
therefore
no
affect on the overall result for the Project concerned. Now feed that
into
the Crosstab and hey-presto column names from 200401 to 200412 with sum
totals below with the dummy data ensuring that all months are
represented.

UNLESS there is an easier way to produce the 12 dummy data lines in just
1
query :-)

I seen this type of problem in the newsgroup before, that's why I have
included this detailed method that works for me.

Regards










Beware, generic response to follow. If you need something more
specific,
just ask...

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP
--

How can I produce column headings consisting of YearMonth for all of
2004?
Starting at 200401 to 200412, or some other combination.

I have data that will crosstab easily with those column names but I have
to
account for any instances where data is not present for a particular
month
and therefore will not be one of the headings. I could design a query
with
column names already in place, however it would not be dynamic and would
require changing for another year.

Is there a function that will give me the 12 values in a column that I
can
crosstab to give the required results?

Regards
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

Works a treat, thank you.

regards

Duane Hookom said:
Two points:
1) My suggestion will have all months represented regardless of your data.
From my previous posting:
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
2) I'm not sure what you mean by "sort to well" since the months I assumed
were column headings. The control sources that I suggested resolve as Date
values which can be formatted in any date format you require.
--
Duane Hookom
MS Access MVP


Terry said:
Your absolutely correct in that, with one proviso. If there are no sales in
a given month that month will not be represented in the query output.
It's
still a useful query in as much it converts dates to monthly representation
and with a little more code to handle the swing about Mth0 'December' it
could be used.

The problem I brought to light was that special case of no invoices at
all
in a particular month. This meant that I had to inject dummy data for
each
month to ensure that all months appeared regardles.

12 queries to do that is probably 'over the top' and I reckon I could
probably get that down to 1 query + 1 function.

The other reason for representing months and years as a numeric yyyymm is
that in the final report the columns will be headed Jan-04 Feb-04 etc, which
do not sort to well due to the alpha-numeric nature. Whereas a numeric
representation will and with a little tinkering in the report can be made to
show Jan-04 onwards.

Thanks for the help
Regards


Duane Hookom said:
I don't know what you mean by "Not quite what I needed". It seems to me
that
the solution I suggested will create month columns for all months for the
year.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Not quite what I needed, however your example did spark off another train
of
thought to give the following.

There is a table that has all invoiced amounts and dates for Projects. So
to
get some raw data for 2004 use a simple aggregate query,
qry_rpt_Scheduled_Invoicing1, to sum the amounts using a criteria to
filter
only year 2004 and returning the date in the form 2004xx , where xx is
the
month number.

In the crosstab using qry_rpt_Scheduled_Invoicing1, some Projects may not
have sums in some of the yyyymm months...problem!...because if a
particular
month does not have any sums from any of the Projects then it will be
excluded from the crosstab...bad news..no October?.. but need to
report
zero
amount on the report!

The solution is peculiar but works. Produce 12 separate querys to
generate
12 consecutive month results between them with zero sums, then use a
Union
query to join qry_rpt_Scheduled_Invoicing1 and all 12 dummy data querys.
Use
the output of the Union query in the crosstab. Below is the first
dummy
data
query SQL, note that I had to include qry_rpt_Scheduled_Invoicing1 and
choose an abitrary Project from it to ensure I had a valid Project
that
would have a sum in the final crosstab. The distinct is necessary to
curtail
duplicate output lines as only one is needed.

SELECT DISTINCT Min([qry_rpt_Scheduled_Invoicing1]![ProjectNumber]) AS
ProjectNumber, (Year(Date())*100)+1 AS MonthYear, 0 AS InvoiceAmountEst,
0
AS InvoiceAmountAct
FROM qry_rpt_Scheduled_Invoicing1;

To get the other months, the above is January, change the
(Year(Date())*100)+1 AS MonthYear to (Year(Date())*100)+2 AS MonthYear to
get February and so on to complete the 12 querys in all.

The Union looks like this, sorry it's longish:
SELECT *
FROM qry_rpt_Scheduled_Invoicing1
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U01
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U02
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U03
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U04
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U05
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U06
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U07
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U08
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U09
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U10
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U11
UNION SELECT ALL *
FROM qry_rpt_Scheduled_Invoicing_U12;

This takes the output from qry_rpt_Scheduled_Invoicing1 and adds 12 dummy
data lines with 200401 to 200412, all with zero sum totals and therefore
no
affect on the overall result for the Project concerned. Now feed that
into
the Crosstab and hey-presto column names from 200401 to 200412 with
sum
totals below with the dummy data ensuring that all months are
represented.

UNLESS there is an easier way to produce the 12 dummy data lines in just
1
query :-)

I seen this type of problem in the newsgroup before, that's why I have
included this detailed method that works for me.

Regards










Beware, generic response to follow. If you need something more
specific,
just ask...

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales
from
the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP
--

How can I produce column headings consisting of YearMonth for all
of
2004?
Starting at 200401 to 200412, or some other combination.

I have data that will crosstab easily with those column names but I
have
to
account for any instances where data is not present for a
particular
month
and therefore will not be one of the headings. I could design a query
with
column names already in place, however it would not be dynamic and
would
require changing for another year.

Is there a function that will give me the 12 values in a column
that I
can
crosstab to give the required results?

Regards
Click to expand...
Click to expand...
Click to expand...
 
Back
Top