Percentage column not displaying in Access Listbox

I have a listbox on a report that's effectively summarizing another table, grouped by a short list of reasons. I have a column for the reasons, and a column for the number of times each reason occured. I want to have a column for what percentage those reasons constitute, but this column is not displaying - moreover it is giving the previous column (count by reason) wrong numbers.

Before editing, my code (which worked perfectly) was;

SELECT Reasons.Reason, Count([Master Data].ID) AS [Count]
FROM Reasons INNER JOIN [Master Data] ON Reasons.ID = [Master Data].[Lateness Reason]
GROUP BY Reasons.Reason;

I edited it to be;

SELECT Reasons.Reason, Count([Master Data].ID) AS [Count], Format(Count([Master Data].ID)/Count(AllData.ID),'\P') AS Percentage
FROM [Master Data] AS AllData, Reasons INNER JOIN [Master Data] ON Reasons.ID = [Master Data].[Lateness Reason]
GROUP BY Reasons.Reason;

But as mentioned, the third column doesn't show and the number in the Count Column is now wrong too.

Can someone explain why this is and what I should do to fix it please?

EDIT: I have worked out that the incorrect number showing in the 'Count' column is actually the correct number multiplied by Count(AllData.ID) although I can't understand why this would happen.

Correct/desired    |    Actual Output
value for "Count"  |      Value
___________________|___________________
10                   75500 
4                    30200
1                    7550
20                   151000
3                    22650
7                    52850

Solution

Try using the correct syntax for Format:

Format(Count([Master Data].ID)/Count(AllData.ID),'Percent') AS Percentage

And you will probably have to use a subquery to obtain the distinct count og the table with the smallest count of joined records ([Master Data]?).

Edit:

If your first solution seems slow, try this:


SELECT 
    ID,
    Reason,
    T.ReasonCount / (SELECT Count(*) FROM [Master Data])
FROM 
    Reasons
INNER JOIN
    (SELECT [Lateness Reason], Count(*) AS [ReasonCount]
    FROM [Master Data]
    GROUP BY [Lateness Reason]) AS T
    ON
    T.[Lateness Reason] = Reasons.ID
GROUP BY 
    ID,
    Reason,
    T.ReasonCount