Creating a percentage of total records report

[sai_rlaf]

I have a table with four distinct values. What I would like to do is create a report that takes the total of each value and returns the percentage of the total records in my table/query.

When I create it now, all of my percentages are returned as 100% instead of what I want.



Any thoughts on what is needed to correct?

[rpeare]

if you're doing it row by row you'd have to do something like

[item1]/([item1] + [item2] + [item3] + [item4])

So if you had four items

2,3,4,5 and you added my percentage calculation you'd end up with a row that looked like

2,3,4,5,.1428,.2142,.2857,.3571

[pbaldy]

This type of thing should work:

SELECT CAR_RECORDS.Company_no, Count(CAR_RECORDS.car_no) AS CarCount, Round(Count([car_no])/DCount("*","car_records"),2) AS PercentOfTotal
FROM CAR_RECORDS
GROUP BY CAR_RECORDS.Company_no;

[rpeare]

if you're creating a report you do not need to calculate the percentage in your query. Stay away from d functions in queries if you can help it (no offense pbaldy) On your report just sum the fields you're interested in, let's say in your group or report footer you have the formula =sum(item1) and text box with this formula is called Item1Total. Now if you want to find the percentage of item1 that came from each row of your data you could have another unbound text box in the DETAIL section of your report with the formula

=[item1]/[Item1Total] and your percentage should be displayed.