Duane,
My many thanks for your continued help with this.
BTW, I noticed a spelling mistake in my original query,
which I have now corrected. The [Sum Of Durations]column
does indeed display the correct total--i.e. the sum of all
displayed duration decimals. However, when I apply the
format command that both you and John provided, all I get
is the value "0:00". It makes no difference into what
column the format is placed, all I get is "0:00".
My SQL is listed below:
TRANSFORM Sum([Sum Of calcProjectDuration])\24 & Format(Sum
([Sum Of calcProjectDuration])\1440 Mod 60,":00") AS
[SumOfSum Of calcProjectDuration]
SELECT [qryWorkloadSum].[fldProjectDescription] AS
Project, Sum([qryWorkloadSum].[Sum Of calcProjectDuration])
\24 & Format(Sum([qryWorkloadSum].[Sum Of
calcProjectDuration])\1440 Mod 60,":00") AS [Sum of
Durations]
FROM qryWorkloadSum
GROUP BY [qryWorkloadSum].[fldProjectDescription]
PIVOT [qryWorkloadSum].[fldEmployeeName];
Once again, my many thanks for your help and continued
support with this problem.
Thanks & regards
Duncan
-----Original Message-----
Are you getting any total in the [Sum Of Duartions]
column?
--
Duane Hookom
MS Access MVP
"Duncan Edment"
message Duane,
Modified SQL below:
PARAMETERS [Start Date?] DateTime, [End Date?] DateTime;
TRANSFORM Sum(qryWorkloadSum.[Sum Of ProjectDuartion])
\24 &
Format(Sum(qryWorkloadSum.[Sum Of ProjectDuartion])
\1440 Mod 60,":00") AS
Duration
SELECT qryWorkloadSum.fldProjectDescription AS Project,
Sum(qryWorkloadSum.[Sum Of ProjectDuartion]) AS [Sum Of
Duartions]
FROM qryWorkloadSum
WHERE (((qryWorkloadSum.fldDateWorked) Between [Start
Date?] And [End
Date?]))
GROUP BY qryWorkloadSum.fldProjectDescription
PIVOT qryWorkloadSum.fldEmployeeName;
--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.
Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.
in message
Paste your modified SQL into a reply.
--
Duane Hookom
Microsoft Access MVP
"Duncan Edment"
message Duane,
Thanks for the reply.
I've tried your suggestion however, the Crosstab
now displays all time
entries under each employee as "0:00"!
All I've done is replaced the original TRANSFORM
statement, with your
suggested one. I've even tried prefixing the [Sum
Of ProjectDuration]
entries with qryWorkloadSum., however it still does
not work.
Any ideas?
Thanks & regards
Duncan
--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.
Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.
"Duane Hookom" <duanehookom@NO_SPAMhotmail.com>
wrote in message
Try this:
TRANSFORM Sum([Sum Of ProjectDuartion])\24 &
Format(Sum([Sum Of
ProjectDuartion]) \ 1440 MOD 60, ":00") AS
[SumOfSum Of ProjectDuartion]
--
Duane Hookom
MS Access MVP
"Duncan Edment"
in
message
[email protected]...
-----Original Message-----
On Sun, 8 Feb 2004 23:07:06 -0000, "Duncan
Edment"
<duncan@_NOSPAMPLEASE_edment.freeserve.co.uk>
wrote:
Thanks for the reply. I have one question--
and maybe
I'm being a bit thick
here--but where do I enter the expression you
listed
above?
As a calculated field in the Query. You
haven't posted
the SQL of the
crosstab query nor am I sure where you want
this value to
appear in
the result so I can't be much more specific
than that!
John W. Vinson[MVP]
Come for live chats every Tuesday and
Thursday
http://go.compuserve.com/msdevapps?
loc=us&access=public
.
John,
I should have realised it would have made sense
to post
the SQL of the Crosstab & Query.
The Crosstab SQL is:
PARAMETERS [Start Date] DateTime, [End Date]
DateTime;
TRANSFORM Sum(qryWorkloadSum.[Sum Of
ProjectDuartion]) AS
[SumOfSum Of ProjectDuartion]
SELECT qryWorkloadSum.fldProjectDescription, Sum
(qryWorkloadSum.[Sum Of ProjectDuartion]) AS
[Total Of Sum
Of ProjectDuartion]
FROM qryWorkloadSum
WHERE (((qryWorkloadSum.fldDateWorked) Between
[Start
Date] And [End Date]))
GROUP BY qryWorkloadSum.fldProjectDescription
PIVOT qryWorkloadSum.fldEmployeeName;
Whilst the SQL of the Query on which the
Crosstab is based
is:
SELECT DISTINCTROW
qryWorkload.fldProjectDescription,
qryWorkload.fldEmployeeName,
qryWorkload.fldDateWorked, Sum
(qryWorkload.ProjectDuartion) AS [Sum Of
ProjectDuartion]
FROM qryWorkload
GROUP BY qryWorkload.fldProjectDescription,
qryWorkload.fldEmployeeName,
qryWorkload.fldDateWorked;
The SQL for the Query qryWorkload is:
SELECT tblProjects.fldProjectDescription,
tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked,
Sum([fldEndTime]-[fldStartTime]) AS
ProjectDuartion,
tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime,
tblTimeSheet.fldEmployeeID,
tblTimeSheet.fldProjectID
FROM tblProjects INNER JOIN (tblEmployee INNER
JOIN
tblTimeSheet ON tblEmployee.fldEmployeeID =
tblTimeSheet.fldEmployeeID) ON
tblProjects.fldProjectID =
tblTimeSheet.fldProjectID
GROUP BY tblProjects.fldProjectDescription,
tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked,
tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime,
tblTimeSheet.fldEmployeeID,
tblTimeSheet.fldProjectID;
What I want to do is, in the Crosstab view,
display the
times in the format HH:NN, rather than the
decimal format
they are currently in. You suggested using the
expression:
[field] \ 24 & Format([field] \ 1440 MOD
60, ":00")
However, I am unclear as to whether this should
be placed
in the Crosstab query, or the query that the
Crosstab is
based upon.
Also, are you suggesting that the calculated
field above
replace the value field that is in the Crosstab?
Regards
Duncan
.
) AS [SumOfSum Of ProjectDuartion]...