The simplest answer to your question is to use a Union query. This is a SQL
only query, it can't be made in the design grid. The easiest way to do this
would be to design your other queries (as you already have) then copy and
paste the SQL from these queries into the new query. Place the word Union
between the end of one query and the word Select in the next query. Remove
the trailing semicolon in each individual query except the last one. Also,
the sorting is done on the field names of the first individual query. The
field names for each individual query don't need to be the same, but the
number of fields and the data type of each field that lines up in the same
column should be the same.
Now, for another option. You could use the running sum ability of textboxes
in reports to do the counting there. You would group by sales person and set
the Running Sum to Over Group. You would have one textbox for each number
division that you want and would use an IIf statement in the control source
of the textbox.
Example:
IIf([RangeField]<5000, 1, 0)
This will make the value 1 if the range is less than 5000 and 0 if it is
greater than 5000. Since you are using a running sum, the sum of the ones
will be the same as the count. You would place these textboxes in the Detail
section of the report and probably set the Visible property to no. You would
then place another set of textboxes in the Group Footer and set the value of
these to the value of the associated hidden textboxes in the detail section,
that way only the final count will show up instead of the running count. If
you use this method, you may be able to get by with a single query.
Perhaps someone could also give you an idea on how to do the grouping in the
query itself, I'm drawing a bit of a blank on that at the moment.
--
Wayne Morgan
MS Access MVP
Bdehning said:
How does one combine results form multiple queries into one query that can
be used for a report record source?
I have queries which count the number of accounts for each consultant for the following ranges.
<5000
How do I get the results together to be able to place into a single report
?
