If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dsum in query
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!! |
#2
|
|||
|
|||
dsum in query
"D" wrote:
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 |
#3
|
|||
|
|||
dsum in query
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. "Gary Walter" wrote: "D" wrote: 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 |
#4
|
|||
|
|||
dsum in query
"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_TurnoverChartLeve l1") 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 |
Thread Tools | |
Display Modes | |
|
|