Multiple Queries in a Report

[clickster]

I have a table with about 40-45 fields. Each field represents a piece of equipment that may or may not be checked out to a particular employee. Each of the fileds is a simple Yes/No checkbox. I have written 40-45 queries (one for each field) to add up the total number of boxes check under each field (this tells me how many of a certain piece of equipment is checked out). I would like to write a report that shows the "CountOfxxxx" fields from all of the queries. The problem is that the report wizard will not accept information from more than one query, even though they are querying the same table. Anyone have any ideas or work-arounds. I am looking for something that require no manual entry of SQL. I need a straight GUI way if at all possible.

 

[BanditWk]

In the design of the query, what I would do is create one query that would have a field in it that would sum up all what you're looking for.

For instance, in Design View, the one field would have:
CntItem1: Sum(IIf([Item1]=True,1,0))

This will count up each Item, hence, eliminating the need to use 40-45 queries to sum each Item up. Then, you can just sum up the "Items" in the last column:

TtlItems: [CntItem1] + ... + [CntItem45]

HTH
Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can and I'm bored at work - no need to send flowers, wine, dinner, or coffee... Just send me a smile to show me that I've helped." ~ Roy McCafferty, seen on a corn