Print record totals in Queries

[KAFES]

Greetings
Running Access 2002...

I often find myself running frequent quick queries that I print out for
meetings, etc... Is there a way to have the query's record total or a "auto"
colum with sequencial numbers (i.e. 1,2,3,...25) apperar on the printed
version?

I looked in tools options, in print properties, in the query properties, and
in the expression builder. I don't need the data to be presented in a report
nor do i have time to make a report each time.
I would perfer a simple setting which will automatically show a record tally
on a query's paper printout (like in a "query footer" - if one existed)

thanks for the help,
Mike

 

[Tom Ellison]

Dear Mike:

I'm not sure this will be easier or quicker for you than creating a report.

You create a separate query that gives the totals for a "group". Add empty
columns to this query as necessary so it has the exact same number of
columns as your existing query.

Add a new column to both queries, having 0 in this column for the detail
data, and 1 in this column in the totals query. Call this column Sequence.

Create a union of the two queries, and sort it by the "group" columns, then
by the Sequence. The result will then look like what you want.

A rough report can do this as well. With some practice, the report might
take less than half an hour to create. The above query might be a bit
quicker (again, with practice), but it cannot be formatted like the report.
The report could have totals lines and blank space between groups, making a
very readable result. The query is going to be minimally readable.

As a practical matter, a good (and pretty quick) report can be created for
meetings that will be easier to read and will be better for your reputation,
in my humble opinion. But, the choice of best solution is in your hands.

If you need help with this, please post SQL for your detail data and for the
totals you wish to add. I'll code a sample of the solution you can use.

Tom Ellison

 

[KAFES]

Thanks Tom,

That’s an interesting approach... I'd like to give that a try when I have
some "free time" (hehe)
I just can't believe that there isn't some property, setting, or option
SOMEWHERE in Access that would allow you to print the __ of __ records info
in the query window footer or as it is called in the form properties
[navigation buttons] bar. as it is... I
guess I will just have to remember to look at query on screen and simply
write the total at the bottom of the print out by hand

Thanks again,
Mike

 

[Tom Ellison]

Dear KAFES:

There is a way to do this in Access. It's called a report, which is a usual
way of doing this. You can preview and see this on the screen as well. I
thought the point was, you didn't want to create a report. I gave you an
alternative, which is pretty easy once you've mastered queries, but which is
inferior to using reports. Neither approach seems to me to be particularl
grevious, but each requires time to learn thoroughly and practice to become
proficient. For these attributes, there is simply no substitute.

Tom Ellison

Thanks Tom,

That's an interesting approach... I'd like to give that a try when I have
some "free time" (hehe)
I just can't believe that there isn't some property, setting, or option
SOMEWHERE in Access that would allow you to print the __ of __ records info
in the query window footer or as it is called in the form properties
[navigation buttons] bar. as it is... I
guess I will just have to remember to look at query on screen and simply
write the total at the bottom of the print out by hand

Thanks again,
Mike

Dear Mike:

I'm not sure this will be easier or quicker for you than creating a report.

You create a separate query that gives the totals for a "group". Add empty
columns to this query as necessary so it has the exact same number of
columns as your existing query.

Add a new column to both queries, having 0 in this column for the detail
data, and 1 in this column in the totals query. Call this column Sequence.

Create a union of the two queries, and sort it by the "group" columns, then
by the Sequence. The result will then look like what you want.

A rough report can do this as well. With some practice, the report might
take less than half an hour to create. The above query might be a bit
quicker (again, with practice), but it cannot be formatted like the report.
The report could have totals lines and blank space between groups, making a
very readable result. The query is going to be minimally readable.

As a practical matter, a good (and pretty quick) report can be created for
meetings that will be easier to read and will be better for your reputation,
in my humble opinion. But, the choice of best solution is in your hands.

If you need help with this, please post SQL for your detail data and for the
totals you wish to add. I'll code a sample of the solution you can use.

Tom Ellison


properties,
and

 

[Vincent Johns]

Dear KAFES:

There is a way to do this in Access. It's called a report, which is a usual
way of doing this. You can preview and see this on the screen as well. I
thought the point was, you didn't want to create a report. I gave you an
alternative, which is pretty easy once you've mastered queries, but which is
inferior to using reports. Neither approach seems to me to be particularl
grevious, but each requires time to learn thoroughly and practice to become
proficient. For these attributes, there is simply no substitute.

Tom Ellison

PLUS --- having defined a Query, it's pretty trivial to run the Report
Wizard on it and get what you want, or something pretty close. If time
is an issue, as it appears to be, I think I'd almost always do it that way.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.