MS Access Query - Percentages

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.
idejjedi2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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
idejjedi2Author 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;
idejjedi2Author 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;
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rey Obrero (Capricorn1)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


.
idejjedi2Author 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
Rey Obrero (Capricorn1)Commented:
can you upload a copy of your db...
idejjedi2Author 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...
Rey Obrero (Capricorn1)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
idejjedi2Author Commented:
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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
idejjedi2Author 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.