Using Dates As Column Headings

Hello,

This is for Access 2003. I have a table, tblDates, that contains the
following Fields that I want to use as the source for a report.

pkProjDayID
fkProjID
dteProjDay

What I want to do is to use dteProjDay as my column headings. Currenly I
have the following dates that I want to use.

4/2/2010
4/5/2010
4/7/2010
4/9/2010
4/12/2010
4/14/2010
4/16/2010
4/19/2010
4/21/2010
4/23/2010
4/26/2010
4/28/2010
4/30/2010

Each of these dates needs to become a column heading. Additionally, I only
want to use the day from each of these dates so that the headings are as
follows.

2 5 7 9 12 14 16 19 21 23 26 28 30

I've played with crosstab queries but I wasn't able to get the desired
results. Any assistance would be greatly appreciated.

--
Regards,
Chris

There is a solution for monthly crosstab reports at
http://www.tek-tips.com/faqs.cfm?fid=5466

You should be able to change the date interval from Month to Day.

--
Duane Hookom
Microsoft Access MVP


"eckert1961" wrote:

Hello,

This is for Access 2003. I have a table, tblDates, that contains the
following Fields that I want to use as the source for a report.

pkProjDayID
fkProjID
dteProjDay

What I want to do is to use dteProjDay as my column headings. Currenly I
have the following dates that I want to use.

4/2/2010
4/5/2010
4/7/2010
4/9/2010
4/12/2010
4/14/2010
4/16/2010
4/19/2010
4/21/2010
4/23/2010
4/26/2010
4/28/2010
4/30/2010

Each of these dates needs to become a column heading. Additionally, I only
want to use the day from each of these dates so that the headings are as
follows.

2 5 7 9 12 14 16 19 21 23 26 28 30

I've played with crosstab queries but I wasn't able to get the desired
results. Any assistance would be greatly appreciated.

--
Regards,
Chris

Hi Duane,

I actually tried that but I wasn't successful. I was able to get the correct
headings but when I set the source on my report the Value was displayed
rather than the header. Part of the problem was that I don't know what field
I can set to a Value.

Any idea on how I can set the source on the report so that the header is
displayed?

Thanks.
--
Regards,
Chris


"Duane Hookom" wrote:

There is a solution for monthly crosstab reports at
http://www.tek-tips.com/faqs.cfm?fid=5466

You should be able to change the date interval from Month to Day.

--
Duane Hookom
Microsoft Access MVP


"eckert1961" wrote:

Hello,

This is for Access 2003. I have a table, tblDates, that contains the
following Fields that I want to use as the source for a report.

pkProjDayID
fkProjID
dteProjDay

What I want to do is to use dteProjDay as my column headings. Currenly I
have the following dates that I want to use.

4/2/2010
4/5/2010
4/7/2010
4/9/2010
4/12/2010
4/14/2010
4/16/2010
4/19/2010
4/21/2010
4/23/2010
4/26/2010
4/28/2010
4/30/2010

Each of these dates needs to become a column heading. Additionally, I only
want to use the day from each of these dates so that the headings are as
follows.

2 5 7 9 12 14 16 19 21 23 26 28 30

I've played with crosstab queries but I wasn't able to get the desired
results. Any assistance would be greatly appreciated.

--
Regards,
Chris

I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.
--
Regards,
Chris


"eckert1961" wrote:

Hi Duane,

I actually tried that but I wasn't successful. I was able to get the correct
headings but when I set the source on my report the Value was displayed
rather than the header. Part of the problem was that I don't know what field
I can set to a Value.

Any idea on how I can set the source on the report so that the header is
displayed?

Thanks.
--
Regards,
Chris


"Duane Hookom" wrote:

There is a solution for monthly crosstab reports at
http://www.tek-tips.com/faqs.cfm?fid=5466

You should be able to change the date interval from Month to Day.

--
Duane Hookom
Microsoft Access MVP


"eckert1961" wrote:

Hello,

This is for Access 2003. I have a table, tblDates, that contains the
following Fields that I want to use as the source for a report.

pkProjDayID
fkProjID
dteProjDay

What I want to do is to use dteProjDay as my column headings. Currenly I
have the following dates that I want to use.

4/2/2010
4/5/2010
4/7/2010
4/9/2010
4/12/2010
4/14/2010
4/16/2010
4/19/2010
4/21/2010
4/23/2010
4/26/2010
4/28/2010
4/30/2010

Each of these dates needs to become a column heading. Additionally, I only
want to use the day from each of these dates so that the headings are as
follows.

2 5 7 9 12 14 16 19 21 23 26 28 30

I've played with crosstab queries but I wasn't able to get the desired
results. Any assistance would be greatly appreciated.

--
Regards,
Chris

I really would appreciate some assistance with this. Please let me know what
additional information is required in order to move this closer to a
resolution. Thank you.
--
Regards,
Chris


"eckert1961" wrote:

I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.
--
Regards,
Chris


"eckert1961" wrote:

Hi Duane,

I actually tried that but I wasn't successful. I was able to get the correct
headings but when I set the source on my report the Value was displayed
rather than the header. Part of the problem was that I don't know what field
I can set to a Value.

Any idea on how I can set the source on the report so that the header is
displayed?

Thanks.
--
Regards,
Chris


"Duane Hookom" wrote:

There is a solution for monthly crosstab reports at
http://www.tek-tips.com/faqs.cfm?fid=5466

You should be able to change the date interval from Month to Day.

--
Duane Hookom
Microsoft Access MVP


"eckert1961" wrote:

Hello,

This is for Access 2003. I have a table, tblDates, that contains the
following Fields that I want to use as the source for a report.

pkProjDayID
fkProjID
dteProjDay

What I want to do is to use dteProjDay as my column headings. Currenly I
have the following dates that I want to use.

4/2/2010
4/5/2010
4/7/2010
4/9/2010
4/12/2010
4/14/2010
4/16/2010
4/19/2010
4/21/2010
4/23/2010
4/26/2010
4/28/2010
4/30/2010

Each of these dates needs to become a column heading. Additionally, I only
want to use the day from each of these dates so that the headings are as
follows.

2 5 7 9 12 14 16 19 21 23 26 28 30

I've played with crosstab queries but I wasn't able to get the desired
results. Any assistance would be greatly appreciated.

--
Regards,
Chris

Try this query
TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

eckert1961 wrote:
I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.

Don't we want to remove tblDates.dteProjDay from the select and group by?

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT Day([dteProjDay]);

If not all days are displayed, you can add all days from 1 to 31 into the
Column Headings property.


--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

Try this query
TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

eckert1961 wrote:
I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.
.

Thankyou for the response John and Duane. I tried copying/pasting Duane's Sql
statement but I get a "Syntax error in TRANSFORM statement" when I attempt to
display the Datasheet view. I can't see anything obvious. Any idea where the
issue might be?
--
Regards,
Chris


"Duane Hookom" wrote:

Don't we want to remove tblDates.dteProjDay from the select and group by?

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT Day([dteProjDay]);

If not all days are displayed, you can add all days from 1 to 31 into the
Column Headings property.


--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

Try this query
TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

eckert1961 wrote:
I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.
.

I think I may have found the cause of the error. I added From ([tblDates])
after the SELECT statement.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
From ([tblDates])
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT Day([dteProjDay]);

This gives me the following output.

TheYear TheMonth 2 5 7 9 12 14 16 19 21 23 26 28 30
2010 4 1 1 1 1 1 1 1 1 1 1 1 1 1

What I need is to be able to link the query to the report's column header
text boxes so that the following days for April are displayed.

2 5 7 9 .... 30

Is it possible to achieve this with the output that this query provides?
--
Regards,
Chris


"eckert1961" wrote:

Thankyou for the response John and Duane. I tried copying/pasting Duane's Sql
statement but I get a "Syntax error in TRANSFORM statement" when I attempt to
display the Datasheet view. I can't see anything obvious. Any idea where the
issue might be?
--
Regards,
Chris


"Duane Hookom" wrote:

Don't we want to remove tblDates.dteProjDay from the select and group by?

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT Day([dteProjDay]);

If not all days are displayed, you can add all days from 1 to 31 into the
Column Headings property.


--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

Try this query
TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

eckert1961 wrote:
I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.
.

I would enter all of the dates in the column headings property of the
crosstab as suggested. Then the report will can have all the same columns.
--
Duane Hookom
Microsoft Access MVP


"eckert1961" wrote:

I think I may have found the cause of the error. I added From ([tblDates])
after the SELECT statement.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
From ([tblDates])
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT Day([dteProjDay]);

This gives me the following output.

TheYear TheMonth 2 5 7 9 12 14 16 19 21 23 26 28 30
2010 4 1 1 1 1 1 1 1 1 1 1 1 1 1

What I need is to be able to link the query to the report's column header
text boxes so that the following days for April are displayed.

2 5 7 9 .... 30

Is it possible to achieve this with the output that this query provides?
--
Regards,
Chris


"eckert1961" wrote:

Thankyou for the response John and Duane. I tried copying/pasting Duane's Sql
statement but I get a "Syntax error in TRANSFORM statement" when I attempt to
display the Datasheet view. I can't see anything obvious. Any idea where the
issue might be?
--
Regards,
Chris


"Duane Hookom" wrote:

Don't we want to remove tblDates.dteProjDay from the select and group by?

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT Day([dteProjDay]);

If not all days are displayed, you can add all days from 1 to 31 into the
Column Headings property.


--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

Try this query
TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

eckert1961 wrote:
I've played with Duane's suggestion and I'm not getting any closer to finding
a solution. Here is the sql of the crosstab query that I've put together.

TRANSFORM Count(tblDates.dteProjDay) AS CountOfdteProjDay
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth,
tblDates.dteProjDay
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay]), tblDates.dteProjDay
PIVOT Day([dteProjDay]);

This results in the following output:

TheYear TheMonth dteProjDay 2 5 7
2010 4 4/2/2010 1
2010 4 4/5/2010 1
2010 4 4/7/2010 1
2010 4 4/9/2010
2010 4 4/12/2010
2010 4 4/14/2010
2010 4 4/16/2010
2010 4 4/19/2010
2010 4 4/21/2010
2010 4 4/23/2010
2010 4 4/26/2010
2010 4 4/28/2010
2010 4 4/30/2010

From this I have no way of displaying the day; ie. 2, 5, 7 in the Textboxes
that make up my Report Column Headings. What get's input into the Header is
the count of 1.

What do I need to change in the query to get my desired results? Thanks.
.