SQL Subquery in MS Access to Calculate Percentages

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Subquery in MS Access to Calculate Percentages

Author

Topic

MotleyCrueFTW
Starting Member

2 Posts

Posted - 2012-10-03 : 17:23:25

New to the forum and I would first like to thank everyone who posts on these excel, access, and sql/programming forums. Your contrbutions have helped me immensley over the years.

Here is my question. I will try to spell it out as clearly as possible as I know that is paramount to getting this answered.

I am trying combine a process that takes two separate queries into one query using a subquery and SQL code within an access database. I am trying to create an average for the unique records within a dataset (table is called "Data"). I can get it so that my query returns the percentage each unique record is to the whole, but I cannot seem to create the code so that it will give me the average of the group that data is within.

Here is the "data" table:
Item Code Purchases
XX1 10 20
XX1 20 100
XX2 10 2
XX2 20 55
XX3 10 42
XX3 20 23
XX4 10 190
XX4 20 2

Ideally this would look like this at the end:

Item Code Purchases Percentage
XX1 10 20 0.17
XX1 20 100 0.83
XX2 10 2 0.04
XX2 20 55 0.96
XX3 10 42 0.65
XX3 20 23 0.35
XX4 10 190 0.99
XX4 20 2 0.01

I could definitely accomplish this by creating a query that sums all of the XX1-XX4 purchases and then use those totals (XX1 would be 120) in another query to caluclate the average, but I would like to avoid doing that (mostly to figure out how to do it in SQL

).

Right now my SQL code to find the percent of the whole would be this

Item Code Purchases Percent
XX1 10 20 4.60829493087558E-02
XX1 20 100 0.230414746543779
XX2 10 2 4.60829493087558E-03
XX2 20 55 0.126728110599078
XX3 10 42 9.67741935483871E-02
XX3 20 23 5.29953917050691E-02
XX4 10 190 0.43778801843318
XX4 20 2 4.60829493087558E-03

Code below:

SELECT Data.Item, Data.Code, Sum(Data.Purchases) AS Purchases, Sum(Data.Purchases)/(SELECT SUM(DATA.PURCHASES) FROM DATA) AS [Percent]
FROM Data
GROUP BY Data.Item, Data.Code;

I am really trying to figure out where I need to modify my subquery to have the total of purchases correspond to XX1 - XX4's totals and not the entire dataset totals.

Thanks for all the help in advance!

Double Yew Tea Eff?

perceptus
Starting Member

3 Posts

Posted - 2012-10-29 : 22:22:28

Did you figure this out? I would try
SELECT Data.Item, Data.Code, Sum(Data.Purchases) AS Purchases, Sum(Data.Purchases)/(SELECT SUM(DATA.PURCHASES) FROM DATA where Main.Data.Item = Data.Item) AS [Percent]
FROM Data as Main
GROUP BY Data.Item, Data.Code;

MotleyCrueFTW
Starting Member

2 Posts

Posted - 2012-11-26 : 11:12:29

Thanks Perspectus for the reply. I tried your code and it looks like I am getting those "Enter Parameter Value" boxes for the Data.XXX data values and the Main.Data.XXX value. Any suggestions on how to fix and make it work? thanks!

Double Yew Tea Eff?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources