If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Using Dates As Column Headings
Of course. Thanks.
John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County 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. |
#12
|
|||
|
|||
Using Dates As Column Headings
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 "Duane Hookom" wrote: 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. . |
#13
|
|||
|
|||
Using Dates As Column Headings
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" wrote: 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 "Duane Hookom" wrote: 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. . |
|
Thread Tools | |
Display Modes | |
|
|