Complex Reporting

  • Thread starter Thread starter Terry Roberts
  • Start date Start date
T

Terry Roberts

I'm attempting to develop a report which will present data
to the user by region, then subcategorized by office
location. There are 9 data elements I'm trying to present
for each office, which are summations of value and count
of records meeting specific criteria. The problem is, I
have 23 offices, and 9 regions. I thought I would present
the data via queries, but it looks like it's getting way
out of hand.

As an example:

Query: Provide count of and sum of values for all orders
for West region, for office #1, in current year.

In this query, I can return two data elements (count of
orders & sum of order value). But since I have 23 offices
and 9 regions, I'm looking at a TON of queries! And I
still have another 7 data elements to generate for that
region/office combination. On top of all this, I need to
group and total by region and office.

I know there must be a more efficent way to do this, but
don't know how to start. Can anyone provide suggestions
for a good way to create this (beyond grinding out
queries!)?

TIA,

Terry Roberts
 
Please list the structure of your tables so that we can get a better picture
of your problem. Most query problems are the result of poorly designed
tables.
 
Have you looked at a totals query?

SELECT Region, Office, Sum(SomeValue), Count(SomeOtherField)
FROM YourTable
GROUP BY Region, Office

This should get you started.

If you are using the query grid, try building a standard select query that shows
the data you want and then Select View: Totals from the menu. Then in the new
row "Totals" you can select to group by columns or count the number of items in
the grouped items or sum them.
 
Back
Top