Collecting the results of many queries

J

Jessica_O

I'm trying to organize the results of many (10+) queries in a single form or
report. For example, for Agent X, I have a query that pulls the number of
product A attributed to Agent X. I have another query that pulls the number
of product B attributed to Agent X, and so forth. I'd like to organize these
results in a form or report, but I when I try to do so, I get an error
message saying that I need to pull fields from only one query or table.

I have also tried running a query off the multiple base queries, but the
resulting datasheet mutliplys the results together. For example, if Agent X
sold 5 of Product A and 8 of Product B, the resulting datasheet show 30 items
sold for both the Product A column and the Product B column.

I've tried working my way through the on-line training courses offered for
Access but I still haven't found something that will accomplish what I'm
looking to do.

Any help would be greatly appreciated (this is for Microsoft Access 2003).

Thanks!

Jessica
 
J

John Vinson

I'm trying to organize the results of many (10+) queries in a single form or
report. For example, for Agent X, I have a query that pulls the number of
product A attributed to Agent X. I have another query that pulls the number
of product B attributed to Agent X, and so forth. I'd like to organize these
results in a form or report, but I when I try to do so, I get an error
message saying that I need to pull fields from only one query or table.
Click to expand...

Two suggestions:

- rather than using ten separate queries, use *one* query, Grouped By
the Product as well as by the Agent. If you'ld like to see a grid,
with agents down the left and products across the top, with a count in
the intersection, use a Crosstab query.

- If there is some good reason for the added complexity of ten queries
(which there might be, undescribed in your post), you can use a UNION
query to "string together" the results of all the queries. Performance
may be less than lightning-quick though.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top