MS Access Query - Percentages

idejjedi2
idejjedi2 used Ask the Experts™
on
Hey Experts,

Trying to calculate the percentages.  So far I have a code that counts the results and displays them. This code is here:

SELECT LitOrigName.litName AS Originator, Count(LitOrigName.litName) AS [Number Of Members]
FROM MemberMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID
GROUP BY LitOrigName.litName;

Now, I would like to calculate the percentages of each result.  The Originator column produces 5 different results, with the first result displaying a count of 200 out of a total of 549.  I want to create a percentage column that displays the results, so the first result would produce a percentage result of 36.4% from 200/549.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
are you sure your sql statement is correct?
this line doesn't look right

FROM MemberMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID

Author

Commented:
Hey Rey,

Sorry.  I accidentally cut and paste a code that I was working on and not the already functioning code.  It is as follows and is functioning properly.

SELECT LitOrigName.litName AS Originator, Count(LitOrigName.litName) AS [Number Of Fellows], SUM(LitOrigName.litNam) AS [Totals]
FROM FellowshipMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID
WHERE FellowshipMasterTable.litID Is Not Null
GROUP BY LitOrigName.litName;

Author

Commented:
Sorry.  I accidentally cut and paste a code that I was working on and not the already functioning code.  It is as follows and is functioning properly.

SELECT LitOrigName.litName AS Originator, Count(LitOrigName.litName) AS [Number Of Fellows]
FROM FellowshipMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID
GROUP BY LitOrigName.litName;
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
try this

Select A.Originator,A.[Number Of Fellows],A.Totals, Format(A.[Number Of Fellows]/A.Totals,"Percent") As Percentage
From
(
SELECT LitOrigName.litName AS Originator, Count(LitOrigName.litName) AS [Number Of Fellows], DCount("*","LitOrigName","litName=" & FellowshipMasterTable.litID ) AS [Totals]
FROM FellowshipMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID
WHERE FellowshipMasterTable.litID Is Not Null
GROUP BY LitOrigName.litName;
) As A


.

Author

Commented:
I copied and pasted your code and returned a "Your query does not include specified expression 'DCount("*"<LitOrigName", "litName="& FellowshipMasterTable.litID) as part of an aggregate function.  

In response to this, I added FellowshipMasterTable.litID into the GROUP BY Clause and it produces the following results.
reyoutput1.jpg
Top Expert 2016

Commented:
can you upload a copy of your db...

Author

Commented:
I cannot.  Some sensitive information is involved.  I am somewhat new to SQL and not an expert in SQL, I am surprised that their is not an easy way to perform this type of query.  Seems like it would be a common question for businesses and organizations.  I did a general google search and noted some similar questions and answers in different database platforms (MySQL, MS SQL) that all came up with different and elaborate solutions. Just surprised.  But again, I am not an expert.  

Thanks for trying...  If I come up with a solution soon, I will post it to this for everyone to see...
Top Expert 2016
Commented:
try this

save your query as Q1
SELECT LitOrigName.litName AS Originator, Count(LitOrigName.litName) AS [Number Of Fellows]
FROM FellowshipMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID
GROUP BY LitOrigName.litName

now create another query based on Q1

select Originator,[Number Of Fellows],Dcount("*","Q1") as Total
From Q1

if you are getting the correct Total, try this, copy and paste

select Q.Originator,Q.[Number Of Fellows], Format(Q.[Number Of Fellows]/Q.Total,"Percent") as Percentage
From
(
select Originator,[Number Of Fellows],Dcount("*","Q1") as Total
From Q1
) As Q
Rey - I tried you code above and it did not give me the results I was looking for. I realize that I could not share the database and that may have affected your code.  However, your sub query put me on the right track and the code that finally worked is displayed below...  Thanks again.  Happy Holidays!

SELECT LitOrigName.litName AS Originator,
Count(LitOrigName.litName) AS [Number Of Fellows],
ROUND(Count(LitOrigName.litName)/(SELECT COUNT(FM.litID)
FROM FellowshipMasterTable FM
WHERE [FM].[litID] Is Not Null)*100,2) AS Percentage
FROM FellowshipMasterTable INNER JOIN LitOrigName ON FellowshipMasterTable.litID = LitOrigName.litID
GROUP BY LitOrigName.litName;

Author

Commented:
Could not get any of the other code to process the information correctly.  My code finally gave the results I was trying to get.  Rey's and his contributions involving sub queries was helpful, but even when adapting his code, it was not producing results required.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial