I was finally able to figure it out. Changing the SQL statement to the
following gives me the desired results.
TRANSFORM First(tblDates.dteProjDay) AS [Date]
SELECT Year([dteProjDay]) AS TheYear, Month([dteProjDay]) AS TheMonth
FROM tblDates
GROUP BY Year([dteProjDay]), Month([dteProjDay])
PIVOT tblDates.EventLabel In ("Day 1","Day 2","Day 3","Day 4","Day 5","Day
6","Day 7","Day 8","Day 9","Day 10","Day 11","Day 12","Day 13","Day 14","Day
15");
In the report I entered the following into the text box column header
Control Source.
=DatePart("d",[Day 1])
I enter this in all 15 of the column header text boxes.
This works exactly as needed. Duane and John, thanks so much for taking the
time to assist me with this.
--
Regards,
Chris
eckert1961 said:
Hi Duane,
My apologies but I don't follow the recommendation. Here is what I need the
output to look like.
TheYear TheMonth Day1 Day2 Day3 ....
2010 4 2 5 7
The dates under Day1, Day2, Day3 are pulled from the tables dteProjDay
field. What will I need to change to get this output? Thanks.
--
Regards,
Chris
:
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
:
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
:
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
:
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
:
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.
.
