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;
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;