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 |
#1
|
|||
|
|||
CrossTab Query with Dates
Hi All,
I have a database with 3 tables: TBL_Persons; TBL_Absence; TBL_AbsenceReasons. When a person is absent I put a record in the TBL_Absence with the date, hours and I select a person and a reason. Every reason has also a letter code. Now I want to print a form what looks like this: x-axle: days of the month: 1 2 3 ... 31 y-axle: months of the year: Jan. Feb. Mar. ... Dec. Now on the right place in the table e.g. 3th of March (where 3 and march cross) I would like to have 4V what means 4 hours de Vacacion I tried to set this up in Access but canīt get it done. Any help or idea how to get this done would be very appreciated GNTK. |
#2
|
|||
|
|||
CrossTab Query with Dates
Hi,
TRANSFORM SUM(hour) SELECT Month(date_time) FROM myTable GROUP BY Month(date_time) PIVOT Day(date_time) IN( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31) I am not sure of SUM(hour), but that should be something like that. The "y" axis is the GROUP BY, the "x" axis is the PIVOT. Hoping it may help, Vanderghast, Access MVP "GNTK" wrote in message ... Hi All, I have a database with 3 tables: TBL_Persons; TBL_Absence; TBL_AbsenceReasons. When a person is absent I put a record in the TBL_Absence with the date, hours and I select a person and a reason. Every reason has also a letter code. Now I want to print a form what looks like this: x-axle: days of the month: 1 2 3 ... 31 y-axle: months of the year: Jan. Feb. Mar. ... Dec. Now on the right place in the table e.g. 3th of March (where 3 and march cross) I would like to have 4V what means 4 hours de Vacacion I tried to set this up in Access but canīt get it done. Any help or idea how to get this done would be very appreciated GNTK. |
#3
|
|||
|
|||
CrossTab Query with Dates
Hello Michel Walsh,
Iīve done what you said, and now I have the query like this: TRANSFORM Sum([TBL_Absence].[Hours]) SELECT ([TBL_Absence].[Date],"mm") FROM TBL_Absence GROUP BY Format([TBL_Absence].[Date],"mm") PIVOT Format([TBL_Absence].[Date],"dd") In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28, 29,30,31); When I run the query I donīt get an error message, but also not a table with data....? "Michel Walsh" vanderghast@VirusAreFunnierThanSpam escribió en el mensaje ... Hi, TRANSFORM SUM(hour) SELECT Month(date_time) FROM myTable GROUP BY Month(date_time) PIVOT Day(date_time) IN( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31) I am not sure of SUM(hour), but that should be something like that. The "y" axis is the GROUP BY, the "x" axis is the PIVOT. Hoping it may help, Vanderghast, Access MVP "GNTK" wrote in message ... Hi All, I have a database with 3 tables: TBL_Persons; TBL_Absence; TBL_AbsenceReasons. When a person is absent I put a record in the TBL_Absence with the date, hours and I select a person and a reason. Every reason has also a letter code. Now I want to print a form what looks like this: x-axle: days of the month: 1 2 3 ... 31 y-axle: months of the year: Jan. Feb. Mar. ... Dec. Now on the right place in the table e.g. 3th of March (where 3 and march cross) I would like to have 4V what means 4 hours de Vacacion I tried to set this up in Access but canīt get it done. Any help or idea how to get this done would be very appreciated GNTK. |
#4
|
|||
|
|||
CrossTab Query with Dates
Hi,
With: absence hours date 2 2004-05-31 4 2004-05-31 1 2004-05-30 as table and TRANSFORM SUM(hours) SELECT Format(date, "mm") FROM absence GROUP BY Format(date, "mm") PIVOT Format(date, "dd") IN( 30, 31) as SQL statement, I got: Query39 Expr1 30 31 05 1 6 Access 2003 seems to no be too strict: I even used hours stored as TEXT (data type) and the PIVOT seems to understand that the in list, is, indeed, ('30', '31') rather than (30, 31), numerical. I fail to see what is not working in your case. Vanderghast, Access MVP |
Thread Tools | |
Display Modes | |
|
|