Dsum slow

G

Guest

Well... I think I know the answer but just in case... I'm using the the
following query to aggregate project information. I can also accomplish this
using a series of subqueries... the only reason I'm attempting to use DSum is
to futility attempt to clean up my app which is quickly getting littered with
objects.

Of course the problem is that DSUM seems to run so very slow... any other
alternatives or anything in the below that could be done differntly to
improve performance?

SELECT Projects.ProjectID, Projects.ProjectName, Projects.ProjType,
Projects.ClientID, Projects.Deposit, Projects.Status,
DSum("[BillableHours]","[Time Card Hours]","projectid=" & [projectid]) AS
[SumOfTotal Hours], DSum("[BillableHours]","[Time Card Hours]","projectid=" &
[projectid])*DLookUp("[BillingRate]","[Time Card Hours]","projectid=" &
[projectid]) AS [SumOfBilling Amount], DSum("[ExpenseAmount]","[Time Card
Expenses]","projectid=" & [projectid]) AS [Total Expenses],
DSum("[PTamount]","[tbl_PTCharges]","PTPRoject=" & [projectid]) AS
SumOfPTamount
FROM Projects
ORDER BY Projects.Status DESC;
 
G

Guest

You could use a Totals query using the query builder. You would want to
select Group By for all the non numeric fields and Sum for the numeric fields.

If this query is being used as the record source for a report. It would be
a lot faster if you don't do any summing in the query, but do it in the
report.
 
J

John Spencer

One problem is that your field and table names will require using square
brackets. If you can avoid spaces and special characters in your field
names and table names (stick to A-z and 0-9 and underscore characters). If
you didn't have the spaces in the names, you could probably speed this up
significantly by using embedded subqueries in the FROM clause of the query.

That said, you can try the following. Hopefully I don't have any typing
errors in it.

SELECT Projects.ProjectID
, Projects.ProjectName
, Projects.ProjType
, Projects.ClientID
, Projects.Deposit
, Projects.Status
, (SELECT Sum(BillableHours)
FROM [Time Card Hours] as TCH
WHERE TCH.ProjectId = Projects.ProjectID) AS [SumOfTotal Hours]
, (SELECT Sum(BillableHours * BillingRate)
FROM [Time Card Hours] as TCH
WHERE TCH.ProjectID = Projects.projectid ) AS [SumOfBilling Amount]
, (SELECT Sum(ExpenseAmount)
FROM [Time Card Expenses] as TCE
WHERE TCE.projectid= Projects.projectid) AS [Total Expenses]
, (SELECT Sum(PTamount )
FROM tbl_PTCharges
WHERE PTProject.ProjectID= Projects.projectid) AS SumOfPTamount
FROM Projects
ORDER BY Projects.Status DESC;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dsum with multiple criteria 6
Problems with dates in query 2
parameters 2

Top