# Thread: Creating a table with sums as a query result (Access2K, Win2KPro)

1. ## Creating a table with sums as a query result (Access2K, Win2KPro)

I am finishing up a large project involving many many queries with the results linked to Excel where graphs are created. On the form there is a group of checkboxes that the user can tick on for one or more barriers to a service. I want to do a bar chart on this checkbox group. the problem is, I can run a sum query for each checkbox and then write a new query with each sum query as a column in the main query, but if one or more of the sub queries is 0 then nothing displays. I am not relating the subqueries, just dumping them in the new main query. I can also just run a query showing each of the checkboxes and that gives me a nice grid that, when linked into Excel, gives a table with 0 or 1 depending on selections. Is there a way to make this query shows sums at the bottom? The Excel sheet is intended to hold the Access info dynamically so i can't hard-code anything as the table will change in size as results vary.

BTW, I got this idea on noticing you can create a pie chart in Excel with the raw numbers and set the chart to display in percentage, which is nice because otherwise you have to write a separate query to output percentages to Excel. I was hoping bar charts could act like that too, which would be nice as I can eliminate a lot of main queries that output percentage in Access to Excel.

TIA

2. ## Re: Creating a table with sums as a query result (Access2K, Win2KPro)

I don't think I quite understand, but perhaps, you can use the Nz function: replace Sum([field]) by Nz(Sum([field]),0) or by Sum(Nz([field],0))

As far as I know, only pie and donut charts display percentages.

3. ## Re: Creating a table with sums as a query result (Access2K, Win2KPro)

actually I just solved the problem. instead of running subqueries to find the number of times a checkbox in the group is ticked and plopping these subtotals into a main query (which overloads the SQL and returns null) I setup a query with a function for each subtotal:
Barrier Blah: Sum(Abs({Table}.{Barrier Blah} ))
which gives me the necessary results and obviates the need for the sub-queries.

Much easier. As for the percentage option on a bar chart, I was just hoping MS had an option for that squirreled away somewhere. If not, OK. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

the lounge cop won't allow the function to display with [ so I use { <img src=/S/cop.gif border=0 alt=cop width=15 height=24>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•