asked on

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.

Rey Obrero (Capricorn1)

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

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

idejjedi2

ASKER

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;

idejjedi2

ASKER

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;

Rey Obrero (Capricorn1)

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

.

idejjedi2

ASKER

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)

can you upload a copy of your db...

idejjedi2

ASKER

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...

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

idejjedi2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

idejjedi2

ASKER

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.