Create Averages

  • Thread starter Thread starter NeonSky via AccessMonster.com
  • Start date Start date
N

NeonSky via AccessMonster.com

Good Morning,

I am stuck on how to generate the proper calculations to finish off my query,
perhaps you can help. Please see below.

Manager Site Advisor YUSAGE CONVERTED_1
SUMCCODE_1

Jeff Child Aruba Amanda Husted 2007 Deposit
229.50
Jeff Child Aruba Amanda Husted 2007 Occupy
410.50
Jeff Child Aruba Amanda Husted 2007 Rental
30.00
Jeff Child Aruba Amanda Husted 2007 Trade for Points
127.00
Jeff Child Aruba Amanda Husted 2007 Unassigned
13.50

What I would like to occur is a to have a an additional field at the end
titled "Usage_Percentage", this field would be each individual "SUMMCCODE_1"
value divided by the SUM of all numbers in (SUMCCODE_1) relative to "Advisor"
and "YUSAGE" (As my results include multiple "Advisors" and "YUSAGE" for both
2007 and 2008).

Hence the final spreadsheet would look like below (I have excluded first few
fields excluded so they fit cleanly in this form).

CONVERTED_1 SUMCCODE_1 Usage_Percentage
Deposit 229.50 .28
Occupy 410.50 .50
Rental 30.00 .03
Trade for Points 127.00 .16
Unassigned 13.50 .01

I am happy to clarify.

Thank you for your time!
 
My initial example got mudled in the form....please see below for abriged
version which contains all the vital data. Thank you.

Advisor YUSAGE CONVERTED_1 SUMCCODE_1
Amanda Husted 2007 Deposit 229.50
Amanda Husted 2007 Occupy 410.50
Amanda Husted 2007 Rental 30.00
Amanda Husted 2007 Trade for Points 127.00
Amanda Husted 2007 Unassigned 13.50
 
Hello Jeff,

That certainly is a solution, as a matter of fact it is what we are currently
running! Being that it is a manual routine I am attempting to automate the
process.

Thanks for your input!

Jeff said:
You may have hit on your solution ... a spreadsheet.

One approach might be to use your query to get the "raw" data, then export
it to Excel, where you could do the extended math...
Good Morning,
Click to expand...
[quoted text clipped - 11 lines]
30.00
Jeff Child Aruba Amanda Husted 2007 Trade for Points
127.00
Jeff Child Aruba Amanda Husted 2007 Unassigned
Click to expand...
[quoted text clipped - 19 lines]
Thank you for your time!
Click to expand...
Click to expand...
 
my table field names

advisor
usage
converted
sum

using the following SQL:

SELECT Table1.Advisor, Table1.Usage, Table1.Converted, Sum(Table1.Cost) AS
[Sum], (DSum("[Cost]","[table1]")) AS Total, [sum]/[total] AS Perc
FROM Table1
GROUP BY Table1.Advisor, Table1.Usage, Table1.Converted;

will give you this

Advisor Usage Converted Sum Total Perc
A 2007 Deposit 229.5 810.5 0.28
A 2007 Occupy 410.5 810.5 0.51
A 2007 Rental 30 810.5 0.04
A 2007 Trade for points 127 810.5 0.16
A 2007 unassigned 13.5 810.5 0.02

NOTE: I have tried to discover how to include a grouping for each set of
advisor and usage but a bit stumped.

--
www.ae911truth.org



NeonSky via AccessMonster.com said:
Hello Jeff,

That certainly is a solution, as a matter of fact it is what we are currently
running! Being that it is a manual routine I am attempting to automate the
process.

Thanks for your input!

Jeff said:
You may have hit on your solution ... a spreadsheet.

One approach might be to use your query to get the "raw" data, then export
it to Excel, where you could do the extended math...
Good Morning,
Click to expand...
[quoted text clipped - 11 lines]
30.00
Jeff Child Aruba Amanda Husted 2007 Trade for Points
127.00
Jeff Child Aruba Amanda Husted 2007 Unassigned
Click to expand...
[quoted text clipped - 19 lines]
Thank you for your time!
Click to expand...
Click to expand...
Click to expand...
 
NeonSky said:
I am stuck on how to generate the proper calculations to finish off my query,
perhaps you can help. Please see below.

Manager Site Advisor YUSAGE CONVERTED_1
SUMCCODE_1

Jeff Child Aruba Amanda Husted 2007 Deposit
229.50
Jeff Child Aruba Amanda Husted 2007 Occupy
410.50
Jeff Child Aruba Amanda Husted 2007 Rental
30.00
Jeff Child Aruba Amanda Husted 2007 Trade for Points
127.00
Jeff Child Aruba Amanda Husted 2007 Unassigned
13.50

What I would like to occur is a to have a an additional field at the end
titled "Usage_Percentage", this field would be each individual "SUMMCCODE_1"
value divided by the SUM of all numbers in (SUMCCODE_1) relative to "Advisor"
and "YUSAGE" (As my results include multiple "Advisors" and "YUSAGE" for both
2007 and 2008).

Hence the final spreadsheet would look like below (I have excluded first few
fields excluded so they fit cleanly in this form).

CONVERTED_1 SUMCCODE_1 Usage_Percentage
Deposit 229.50 .28
Occupy 410.50 .50
Rental 30.00 .03
Trade for Points 127.00 .16
Unassigned 13.50 .01
Click to expand...


You might be able to use a subquery to get the total:

SELECT YUSAGE , Advisor, CONVERTED_1, SUMCCODE_1,
SUMCCODE_1 / (SELECT Sum(X.SUMCCODE_1)
FROM table As X
WHERE X.Advisor = T.Advisor
And X.YUSAGE = T.YUSAGE
) As Usage_Percentage
FROM table As T
 
This certainly helps, I really do appreciate the input!

Marshall said:
I am stuck on how to generate the proper calculations to finish off my query,
perhaps you can help. Please see below.
Click to expand...
[quoted text clipped - 28 lines]
Trade for Points 127.00 .16
Unassigned 13.50 .01
Click to expand...

You might be able to use a subquery to get the total:

SELECT YUSAGE , Advisor, CONVERTED_1, SUMCCODE_1,
SUMCCODE_1 / (SELECT Sum(X.SUMCCODE_1)
FROM table As X
WHERE X.Advisor = T.Advisor
And X.YUSAGE = T.YUSAGE
) As Usage_Percentage
FROM table As T
Click to expand...
 
this ROCKS! thanks!
my table field names

advisor
usage
converted
sum

using the following SQL:

SELECT Table1.Advisor, Table1.Usage, Table1.Converted, Sum(Table1.Cost) AS
[Sum], (DSum("[Cost]","[table1]")) AS Total, [sum]/[total] AS Perc
FROM Table1
GROUP BY Table1.Advisor, Table1.Usage, Table1.Converted;

will give you this

Advisor Usage Converted Sum Total Perc
A 2007 Deposit 229.5 810.5 0.28
A 2007 Occupy 410.5 810.5 0.51
A 2007 Rental 30 810.5 0.04
A 2007 Trade for points 127 810.5 0.16
A 2007 unassigned 13.5 810.5 0.02

NOTE: I have tried to discover how to include a grouping for each set of
advisor and usage but a bit stumped.
Hello Jeff,
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Back
Top