Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •