MS Access report: can the row count of a query be displayed?

Factual Questions
1

Here’s what I’ve got:

  • Table A

  • Query A that gives nothing more than the row count of Table A based on certain criteria ( where Column A is NULL). When you open Query A, you get a result set comprising only a single number – one row, one column.

  • A report that needs to show the result of Query A (that selfsame single number).

…
Can this be done? I’ve been striking out all afternoon. Getting a working Query A is easy – making the result of Query A show up in a report has proven impossible so far.

I understand about Bound and Unbound boxes in Access reports, and how to set Data Control Source of a text box. All the obvious methods of doing this are failing. :confused:

Thanks in advance.

2

Did you try the wizard? All you have to do is start a report and base it on that query via the wizard drop-down box. If it is taking you more than 30 seconds, you may have gone down the wrong path.

3

I am gussing now that you probably don’t want just a single number on a report. How about a subreport?

4

Subreports:

http://office.microsoft.com/en-ca/assistance/HA010550931033.aspx

5

One subreport for each of the 14 queries?

Yeah, I noticed that it didn’t look like Access allowed a single report to pull data from more than one table/query.

6

There should be a problem with that many subreports.

You can write SQL for a control box as well you know. You just enter it in the properties as the source. That may work.

7

There shouldn’t be a problem with that many subreports.

You can write SQL for a control box as well you know. You just enter it in the properties as the source. That may work.

8

Probably going to end up making a table out of all those row counts, then reporting from that.

You don’t happen to know how to add in a “dummy column” to a query in Access, do you? So that a column appears in the query result set that doesn’t appear in the query?

9

Not sure I follow that. It is a little Douglas Adams-ish. You can add anything you want to a query. Just name the column (or let it give you an Expr1 type name. And just put a calculation or even hard text like “FAKE” to appear.

I am serious when I say that you can probably query striaght from the boxes. I will try it in a second.

10

This is exactly what I want … hard text to appear. Not just the name of the column, though … but also a dummy entry in that field.

11

Figured it out:

SELECT “Hotel Rooms Occupied” as QRY, [1a Hotel Rooms Occupied - All].COUNT
FROM [1a Hotel Rooms Occupied - All]

yields:

QRY                             COUNT
-------------------------------------------------------------------
Hotel Rooms Occupied             298
12

All you need to do is enter a new field with a field name in the form of:
Test: “1”

13

It looks like subreports are the easiest way to do what you ant even if there are 14 of them. Just use the subreport tool to drop and drop it in the report and then follow the wizard to link it to a query. You have to play around with the properties a lot to get it to look right.

14

You could do a bunch of union queries to get that into something manageable if you want all the results in a list. That would only require one report or a report and a subreport/

15

Yeppity yep. Thanks for your help!