Collecting the results of many queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top