Craeting a Formula in an REPORT

[CCPKGUY]

I created a query and from the query I created a report. What I am trying to do is crete a formula that will give me the percentage. For example..

Cash 105
Check 200
Cross Applied Payment 16
Debit 56
Money Order 87

There are 5 Payment Types which is the field name with the count of payments per each payment type which is a total of 464. What I am looking to do is to create a formula to show the percentage of each payment type. For instance if I have Cash payments were done 105 times what is the correct formula to show the percentage if there were 464 payments but out of that 105 were cash payments. In my design view of the report I have the total count for each payment type as =Sum([COUNT OF PAYMENT TYPE]) and I have the count of payment type as COUNT OF PAYMENT TYPE. How can I create a formula to show the percentage. I should show 105 time for Cash / =Sum([COUNT OF PAYMENT TYPE]) multiply by 100 to get 22% of times payments were done by Cash.

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CCPKGUY]

Here is the SQL Code in my query?

SELECT [2013 Daily Transactions].trans_date AS [TRANSACTION DATE], [2013 Daily Transactions].descript AS [PAYMENT TYPE], Count([2013 Daily Transactions].descript) AS [COUNT OF PAYMENT TYPE], Sum([2013 Daily Transactions].trans_amt) AS [TOTAL AMOUNT]
FROM [2013 Daily Transactions]
GROUP BY [2013 Daily Transactions].trans_date, [2013 Daily Transactions].descript
HAVING ((([2013 Daily Transactions].trans_date)>=#1/1/2014#));

 

[PHV]

Something like this ?

SELECT A.trans_date AS [TRANSACTION DATE], A.descript AS [PAYMENT TYPE]
, Count(A.descript) AS [COUNT OF PAYMENT TYPE], Sum(A.trans_amt) AS [TOTAL AMOUNT]
, 100*Count(A.descript)/B.total AS Percent
FROM [2013 Daily Transactions] A INNER JOIN (
SELECT trans_date,Count(descript) AS total FROM [2013 Daily Transactions] 
WHERE trans_date>=#2014-01-01# GROUP BY trans_date
) B ON A.trans_date=B.trans_date
WHERE A.trans_date>=#2014-01-01#
GROUP BY A.trans_date, A.descript

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CCPKGUY]

One question... I know that A represents [2013 Daily Transactions] so what does B represents?

 

[PHV]

B represents the inline view joined to A.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Since you are only reporting one day you should be able to use a text box with these properties:

Control Source: =[COUNT OF PAYMENT TYPE]/Sum([COUNT OF PAYMENT TYPE])
Format: Percent

Duane
Hook'D on Access
MS Access MVP

 

[CCPKGUY]

I believe I have it working now. I am using this SQL script

SELECT qry2013DailyTransactionPercentageQuery.[TRANSACTION DATE], qry2013DailyTransactionPercentageQuery.[PAYMENT TYPE], qry2013DailyTransactionPercentageQuery.[COUNT OF PAYMENT TYPE], qry2013DailyTransactionPercentageQuery.[TOTAL AMOUNT], [qry2013DailyTransactionPercentageQuery]![COUNT OF PAYMENT TYPE]/[qryDailyCountofPayments]![CountOfdescript] AS Percentage
FROM qry2013DailyTransactionPercentageQuery LEFT JOIN qryDailyCountofPayments ON qry2013DailyTransactionPercentageQuery.[TRANSACTION DATE] = qryDailyCountofPayments.trans_date;


Thanks for your help..