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.

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.

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.

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