I have searched the forums, but the criteria I used must not have been appropriate.

I am trying to determine if there is a method for creating a report that will allow me to display multiple recordsets that are unrelated to each other. All these recordsets are counts compiled in queries and I would prefer to display the results in one report.

I would greatly appreciate any help with this.

Yes, I had to something like that this morning. What I did was opened up a new query and typed SQL statement directly into where you would add your field names. If you are not good with SQL you can retrieve the SQL statement from your queries if you open them up in SQL view.

I've had good luck using DCount. Have you tried this yet?

KeithG, I don't understand what you mean by "directly into where you would add your field names".

kaveman, the problem is that the values that are being counted can change and I am trying to develop the report in such a way that it wouldn't have to be redone every time someone changes/adds values to be counted.

Can you post your DB?

I cannot. It is for a private firm.

I will post an example for you.

Here you go

That's good. That gives a way to compile the counts into one location. Do you think that would work with providing the names of the items being counted also?

Not bad at all. I can think of a few places in my DB where this will work perfectly. Thanks for the example.

You can name the field what ever you would like but the subquery can only have on field in it.

Hmmm...that's not what I meant. I may not be using the proper terminology. I'll try to clarify.

You are counting items. I need to display the grouping of the items being counted.

Loaf of bread = 25
can of soup = 15
etc....

Are you talking about something like this?

No. Where you have Bill, Jim, etc. I would need to know how many Bill's.

So the result would be:

Bill = 2
Jim = 1

All three tables I'm trying to work with are in the same situation.

There are 100 records. One field contains a lookup value. I need to count how many of each one of those values. The SQL I use to count the one field is this:

SELECT tblSites.Category, Count(tblSites.Category) AS CountOfCategory, tlkpCategory.Description
FROM tlkpCategory INNER JOIN tblSites ON tlkpCategory.Category = tblSites.Category
GROUP BY tblSites.Category, tlkpCategory.Description;

So I get Category1 has this many, Category2 has this many.

Now is that clear as mud or what?:rolleyes:

EDIT: I tried to insert that SQL into a query as a subquery, but I end up with 100 results all showing -1. I had to use the EXISTS statement to even get it to start working. I think part of the problem with that method is related to the fact that I have to lookup the value of the category because the main table only stores the id of the category and not its name.

do you know which catogories you are going to have each time or will the list change?

The categories can change. For all three tables.

After pondering this for a while and trying to research the best method, I think I have decided to go with subreports. I didn't not realize that the page would automatically adjust to the proper sizes. In this way, I have a main report (unbound) and the three count queries in three subreports. This is probably the simplest method since I need to reflect the name of the item being counted next to each total.