Using DSum

  • Thread starter Thread starter red6000
  • Start date Start date
R

red6000

Hi I have a table called 'Tasks'.

It has 3 colums:

WorkDate
TaskName
TimeSpent

I can write the SQL to sum up the total time spent for various time frames,
ie:

SELECT Tasks.WorkDate, DSum("TimeSpent","Tasks") AS TotalTime
FROM Tasks
WHERE (((Month([WorkDate]))=[Enter Month]))
GROUP BY Tasks.WorkDate;

What I want is for the results to group together each task and the relevant
subtotal. However if I add the Tasks to the above, it just shows the full
total for each task?

Any help appreciated.

Thanks
 
Add the TaskName to the query and then change the DSum statement to:
DSum("TimeSpent","Tasks", "TaskName = '" & [TaskName] & "'")

Hope that helps!
 
red6000 said:
Hi I have a table called 'Tasks'.

It has 3 colums:

WorkDate
TaskName
TimeSpent

I can write the SQL to sum up the total time spent for various time
frames, ie:

SELECT Tasks.WorkDate, DSum("TimeSpent","Tasks") AS TotalTime
FROM Tasks
WHERE (((Month([WorkDate]))=[Enter Month]))
GROUP BY Tasks.WorkDate;

What I want is for the results to group together each task and the
relevant subtotal. However if I add the Tasks to the above, it just
shows the full total for each task?

Any help appreciated.

Thanks
Click to expand...

You don't normally want DSum() or DAnything in a query. Plain old Sum()
should be used instead.

The Domain aggregate functions (DSum(), DCount(), etc.), work across the
"domain" specified in their second argument whereas the "normal" aggregate
functions (Sum(), Count(), etc.), work across the domain defined by the
where they are used.

By the way, if [TimeSpent] is a DateTime field then summing that is not
going to work if the total exceeds 24 hours. DateTimes are for points in
time, not amounts of time.
 
Back
Top