Performing a calculation on a query

[red6000]

Hi,
I have a query that sums up totals for me, what I would like is to enhance
this query to show what the percentage of each group is versus the total.
My initial code is:

SELECT Activities.TaskName, DSum("TimeSpent","Activities","TaskName = '" &
[TaskName] & "'") AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)=Forms!InputForm2!MainTimesheet.Form!WorkDate)
And ((Activities.CSTRName)=Forms!InputForm!MainTimesheet.Form!EmployeeID))
GROUP BY Activities.TaskName, Activities.WorkDate
ORDER BY Val(DSum("TimeSpent","Activities","TaskName = '" & [TaskName] &
"'")) DESC;


I have tried the following, but the % comes out at 100% for each group:

SELECT Activities.TaskName, DSum("TimeSpent","Activities","TaskName = '" &
[TaskName] & "'") AS TOT,
FORMATPERCENT(DSum("TimeSpent","Activities","TaskName = '" & [TaskName] &
"'") / Sum(TimeSpent)) AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)=Forms!InputForm2!MainTimesheet.Form!WorkDate)
And ((Activities.CSTRName)=Forms!InputForm2!MainTimesheet.Form!EmployeeID))
GROUP BY Activities.TaskName, Activities.WorkDate;


Any help greatly appreciated.

Thanks.

 

[James A. Fortune]

Hi,
I have a query that sums up totals for me, what I would like is to enhance
this query to show what the percentage of each group is versus the total.
My initial code is:

SELECT Activities.TaskName, DSum("TimeSpent","Activities","TaskName = '" &
[TaskName] & "'") AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)=Forms!InputForm2!MainTimesheet.Form!WorkDate)
And ((Activities.CSTRName)=Forms!InputForm!MainTimesheet.Form!EmployeeID))
GROUP BY Activities.TaskName, Activities.WorkDate
ORDER BY Val(DSum("TimeSpent","Activities","TaskName = '" & [TaskName] &
"'")) DESC;


I have tried the following, but the % comes out at 100% for each group:

SELECT Activities.TaskName, DSum("TimeSpent","Activities","TaskName = '" &
[TaskName] & "'") AS TOT,
FORMATPERCENT(DSum("TimeSpent","Activities","TaskName = '" & [TaskName] &
"'") / Sum(TimeSpent)) AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)=Forms!InputForm2!MainTimesheet.Form!WorkDate)
And ((Activities.CSTRName)=Forms!InputForm2!MainTimesheet.Form!EmployeeID))
GROUP BY Activities.TaskName, Activities.WorkDate;


Any help greatly appreciated.

Thanks.

Hi Red6000,

The problem seems to be that the 'GROUP BY' sums the TimeSpent for each
task already.

Try replacing 'Sum(TimeSpent)' with something like (air code):

DSum("TimeSpent", "Activities",
"WorkDate=Forms!InputForm2!MainTimesheet.Form!WorkDate And
CSTRName=Forms!InputForm2!MainTimesheet.Form!EmployeeID")

That should sum the TimeSpent for all tasks on that WorkDate for that
EmployeeID. Then each task percentage should get the proper
denominator. If that idea doesn't work, post maybe 10 lines of sample
input data and what the result you want looks like so that anyone can
test the SQL. Also, once everything is working, perhaps putting the
Forms!... parts outside the string will keep the DSum from having to
resolve the form values multiple times. This could involve delimiters
such as # for dates and possibly Chr(34) or ' for strings. Does anyone
know if doing this will speed the DSum any?

I hope this helps,

James A. Fortune
(e-mail address removed)

 

[Guest]

hey thanks for this e-mail write back
red6000 wrote in message

 

[Guest]

hey
red6000 wrote in message