dsum in query

[D]

I have a field in query: New_Cost: [AllocatedCost]+[MonthlyCost]. I want a
new field called Cost_Percentage which needs to Sum the column [New_Cost] and
divide by each record [New_Cost] to get that percentage. Can this be done
with DSum and if so, how?
Thanks!!

 

[Gary Walter]

I have a field in query: New_Cost: [AllocatedCost]+[MonthlyCost]. I want
a
new field called Cost_Percentage which needs to Sum the column [New_Cost]
and
divide by each record [New_Cost] to get that percentage. Can this be done
with DSum and if so, how?

Hi D,

Yes it can be done, but it might help to know more details...

is this a simple SELECT, or already an aggregating query?

will DSUM() need some filtering?

do [AllocatedCost] and [MonthlyCost] come from same table?

At least, please provide the SQL for the query you have now.

Without that info, one could guess you would want to insert
DSUM() in grid (w/ an alias) before the percentage calculation

Field: TotCost:
DSUM("[AllocatedCost]+[MonthlyCost]","yurtable","somefiltering?")
Table:
Sort:
Show: <checked>
Criteria:
Or:

then use aliases of New_Cost and TotCost to
compute your percentage in column of grid
further to right of these 2 columns

Field: Cost_Percentage: [New_Cost] / [TotCost]
Table:
Sort:
Show: <checked>
Criteria:
Or:

when you divide in SQL, it never hurts to check for 0 divisor

Field: Cost_Percentage: IIF([TotCost]<>0, [New_Cost] / [TotCost], 0.0)
Table:
Sort:
Show: <checked>
Criteria:
Or:

good luck,

gary

 

[AccessARS]

Hello gary,
I have a similar scenario and was wondering if i can use the DSUM to resolve
my issue...

I am running a query off of a query where I am attempting to get a
percentage of a field next to a record from the sum of the same field from
the source query...

My string:
SELECT qry_TurnoverChartLevel1.EmployeeLevel,
Sum(qry_TurnoverChartLevel1.CountOfEmployeeLevel) AS
SumOfCountOfEmployeeLevel,
DSum([CountOfEmployeeLevel],[qry_TurnoverChartLevel1])/[CountOfEmployeeLevel]
AS [Level%]
FROM qry_TurnoverChartLevel1
GROUP BY qry_TurnoverChartLevel1.EmployeeLevel;

My Error:
You tired to execute a query that does not inlude the specified function
'DSum([CountOfEmployeeLevel],[qry_TurnoverChartLevel1])/[CountOfEmployeeLevel]' as part of an aggregate function.

....I have tried different methods with more or less the same result but from
what i have read in this message it might work for me.

Thank you in advance for your time.

I have a field in query: New_Cost: [AllocatedCost]+[MonthlyCost]. I want
a
new field called Cost_Percentage which needs to Sum the column [New_Cost]
and
divide by each record [New_Cost] to get that percentage. Can this be done
with DSum and if so, how?

Hi D,

Yes it can be done, but it might help to know more details...

is this a simple SELECT, or already an aggregating query?

will DSUM() need some filtering?

do [AllocatedCost] and [MonthlyCost] come from same table?

At least, please provide the SQL for the query you have now.

Without that info, one could guess you would want to insert
DSUM() in grid (w/ an alias) before the percentage calculation

Field: TotCost:
DSUM("[AllocatedCost]+[MonthlyCost]","yurtable","somefiltering?")
Table:
Sort:
Show: <checked>
Criteria:
Or:

then use aliases of New_Cost and TotCost to
compute your percentage in column of grid
further to right of these 2 columns

Field: Cost_Percentage: [New_Cost] / [TotCost]
Table:
Sort:
Show: <checked>
Criteria:
Or:

when you divide in SQL, it never hurts to check for 0 divisor

Field: Cost_Percentage: IIF([TotCost]<>0, [New_Cost] / [TotCost], 0.0)
Table:
Sort:
Show: <checked>
Criteria:
Or:

good luck,

gary

 

[Gary Walter]

"AccessARS"wrote:

I am running a query off of a query where I am attempting to get a
percentage of a field next to a record from the sum of the same field from
the source query...

My string:
SELECT qry_TurnoverChartLevel1.EmployeeLevel,
Sum(qry_TurnoverChartLevel1.CountOfEmployeeLevel) AS
SumOfCountOfEmployeeLevel,
DSum([CountOfEmployeeLevel],[qry_TurnoverChartLevel1])/[CountOfEmployeeLevel]
AS [Level%]
FROM qry_TurnoverChartLevel1
GROUP BY qry_TurnoverChartLevel1.EmployeeLevel;

My Error:
You tired to execute a query that does not inlude the specified function
'DSum([CountOfEmployeeLevel],[qry_TurnoverChartLevel1])/[CountOfEmployeeLevel]'
as part of an aggregate function.

I know from experience it is tough to ask a question
on these newsgroups. If you give too much details
you suspect their eyes will just glaze over and move
on. If you don't give enough detail (they cannot see
your data), they may not be able to zero in on your
problem and you end up with a discussion tree just
sorting out the details which can be frustrating for
both parties.

So...here's a guess and maybe we'll get lucky this time...

Domain functions can be "expensive," but it appears
in this case you just want to sum all the counts in your
query? Is there any reason this could not have been
already determined in qry_TurnoverChartLevel1?

Nevertheless...

I could be wrong but I think if you give the DSUM
its own column in the query grid, and give it an alias, and
set "Total" row to "Expression," it will be executed
only once over query execution. Also, in domain functions the
"expr" and "domain" need to be wrapped in quotes.

So (if we're lucky) your query grid might look like:

Field: TotCnt:
DSum("CountOfEmployeeLevel","qry_TurnoverChartLevel1")
Table:
Total: Expression
Sort:
Show:
Criteria:
or:

followed by the calculation column using our "TotCnt" alias:
(always good idea to not introduce punctuation in
your field names)

Field: LevelPerCent : IIF([CountOfEmployeeLevel]<>0,
[TotCnt] / [CountOfEmployeeLevel], 0)
Table:
Total: Expression
Sort:
Show:
Criteria:
or:

{above Field lines may word wrap here,
but should all be typed out all on one line}

good luck,

gary