Crosstab Query

[akins4lyfe]

i have designed a crosstab query from a table with fields containing DATETIME datatype, to produce a report showing employee start time, and end time, for sets of specified dates, e.g "Current Week".

here is my query:

TRANSFORM Sum(Format([Logout Time]-[Login Time],"h")) AS HoursWorked
SELECT [First Name] & " " & [Last Name] AS [Agent Name], Sum([HoursWorked]) AS [Total Hours]
FROM tblEmployee INNER JOIN tblTimeSheet ON tblEmployee.[LAN ID] = tblTimeSheet.[LAN ID]
WHERE (((Year([Date])*"53"+DatePart("ww",[Date]))=Year(Date())*"53"+DatePart("ww",Date())-1))
GROUP BY [First Name] & " " & [Last Name]
PIVOT tblTimeSheet.Date;

Hours Worked is rounding up to a whole number, like 4 hours, i would like to display hours and minutes for example 4hrs:45min. And also to label the column heads with Weekdays e.g Monday, Tuesday e.t.c.

Could someone pls point me in the right direction. Thanks.

 

[SkipVought]

hi,

Hours Worked is rounding up to a whole number, like 4 hours, i would like to display hours and minutes for example 4hrs:45min

Cuopla things.

The Format function returns a STRING. So you want to SUM the STRING? How does that work? Well There is forgiveness of sins -- the compiler actually converts your STRING to a NUMBER so it can be SUMMED!!!

But what happens when you use the Format function to return the STRING, 4:45? Well I'd guess that the gracious compiler, overlooking your increasing discression, again CONVERTS your 4:45 STRING to a NUMBER so it can be SUMMED.

In either case, you end up with a....er....uh NUMBER like 0.197916667 which is 4 hours and 45 minutes. Yes, that's what it is, expressed in DAYS. To get HOURS, you simply convert Days to Hours.

TRANSFORM ([Logout Time]-[Login Time])*24

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Also, name a field "Date", which is a RESERVE WORD as well, is not a particularly sound practice, as in...

 tblTimeSheet.[b]Date[/b];

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[akins4lyfe]

Hi Skip,

Thanks for the useful advice. I thought i could get away with the Format function by appending "h\hn\". i have removed the format function, and changed "Date to Dates", but still can't figure out why TRANSFORM ([Logout Time]-[Login Time])*24 returned decimal numbers again.


Total Hours 17.9505555555556

new query with dates as values:

TRANSFORM ([Logout Time]-[Login Time])*24 AS HoursWorked
SELECT [First Name] & " " & [Last Name] AS [Agent Name], Sum([HoursWorked]) AS [Total Hours]
FROM tblEmployee INNER JOIN tblTimeSheet ON tblEmployee.[LAN ID] = tblTimeSheet.[LAN ID]
WHERE (((Year([Date])*"53"+DatePart("ww",[Date]))=Year(Date())*"53"+DatePart("ww",Date())-1))
GROUP BY [First Name] & " " & [Last Name]
PIVOT tblTimeSheet.Date;

 

[SkipVought]

so sorry, TRANSFORM requires SUM

TRANSFORM [b]SUM([/b]([Logout Time]-[Login Time])*24[b])[/b]

but still can't figure out why TRANSFORM ([Logout Time]-[Login Time])*24 returned decimal numbers again.

That's because you are SUMMING a pure NUMBER that happens to represent hours.

I have not tried this, but what would happen if you did this...

TRANSFORM Format(SUM(([Logout Time]-[Login Time])*24),"hh:nn")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

I'm not sure why you would multiply by 24 which would convert hours to days. Also it doesn't make sense to multiply a string.

WHERE (((Year([Date])*[b][red]"[/red][/b]53[b][red]"[/red][/b]+DatePart("ww",[Date]))=Year(Date())*[b][red]"[/red][/b]53[b][red]"[/red][/b]+DatePart("ww",Date())-1))

I suggest:

WHERE (((Year([Date])*53+DatePart("ww",[Date]))=Year(Date())*53+DatePart("ww",Date())-1))

Duane
Hook'D on Access
MS Access MVP

 

[akins4lyfe]

I have finally figured this out, here is the magic query that did the trick:

TRANSFORM Sum([Logout Time]-[Login Time]) AS HoursWorked
SELECT [First Name] & " " & [Last Name] AS [Agent Name], Sum([HoursWorked]) AS [Total Hours]
FROM tblEmployee INNER JOIN tblTimeSheet ON tblEmployee.[LAN ID] = tblTimeSheet.[LAN ID]
WHERE (((DatePart("ww",[Date]))=DatePart("ww",Date())) AND ((Year([Date]))=Year(Date())))
GROUP BY [First Name] & " " & [Last Name]
ORDER BY [First Name] & " " & [Last Name], tblTimeSheet.LoginDate
PIVOT tblTimeSheet.LoginDate;



thanks all,