By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,900 Members | 1,348 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,900 IT Pros & Developers. It's quick & easy.

Best technique to produce report with data and percentages of thetotal

P: n/a
I need to produce a report like this

Color: # %
------ --- ---
White 10 20
Black 25 50
other 15 30
---- ----
Total 50 100
Data (from summary query)

White 10
Black 25
Other 15

I know you can use computations involving DSUM to calculate the
percentages in a query.

I have also created a summary query of the total then combined that with
each record of the detail so I could always create percentages. (This
is easy to do in Access by including two tables in the query design view
without any relation between them. The result is records having all
combinations.

Combined data
White 10 50
Black 25 50
other 15 50

The percentage is computed from data on each record.

Anyone have another way that is faster or easier???

Bob
Sep 28 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Thu, 28 Sep 2006 13:25:46 -0500, Bob Alston <bo********@yahoo.com>
wrote:

In order to calculate the percentages you need the sum of the counts.
DSum may not be the fastest way to get it, if you have a large (>1000
rows) base data set. If so, try a totals query.
Also calling the Totals query or DSum once per row is likely not as
fast as pre-computing it, and passing it into your query as a
parameter.

In T-SQL there is the interesting WITH CUBE clause, but it does not
exist in Access SQL.

-Tom.

>I need to produce a report like this

Color: # %
------ --- ---
White 10 20
Black 25 50
other 15 30
---- ----
Total 50 100
Data (from summary query)

White 10
Black 25
Other 15

I know you can use computations involving DSUM to calculate the
percentages in a query.

I have also created a summary query of the total then combined that with
each record of the detail so I could always create percentages. (This
is easy to do in Access by including two tables in the query design view
without any relation between them. The result is records having all
combinations.

Combined data
White 10 50
Black 25 50
other 15 50

The percentage is computed from data on each record.

Anyone have another way that is faster or easier???

Bob
Sep 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.