Report Totals Multiple Queries

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

Guest

Based on everything I'm reading, Access provides no direct way to pull totals
off of existing reports for a "summary page of totals". Is this inherent in
ALL databases? I assume that in Access I will have to create NEW reports if
I choose to use subreports because I don't want any detail on the totals
report....I just want it to say:
"COMPUTERS - 109"
"OFFICE LICENSES - 108
ETC
Since the existing reports show detail, I will have to copy those reports
and set the detail section to invisible to be able to get each subreport to
show ONLY the totals. Is all this correct? I guess I could run all the
reports I have now and copy the totals into a Word Document but that seems
rather a cumbersome solution.
 
Not sure exactly what you are asking. My guess is that you want to use the
same report to display with or without details. If this is so, you can add
code to your report like:

'General declarations section
Option Compare Database
Dim booHideDetail As Boolean

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Cancel = booHideDetail
End Sub

Private Sub Report_Open(Cancel As Integer)
booHideDetail = (MsgBox("Hide Details", _
vbQuestion + vbYesNo, "Hide Details") = vbYes)
End Sub
 
OK, I'll try adding the code you provided....just two questions....uh, where
and how to add the code. I know these are big questions and I'll research it
but if you have any good resources, I'd appreciate links or whatever.

The goal here is to have a report with two words on it: COMPUTERS,
LICENSES. I have queries and reports for each of these that provide details
like purchase date, PO number, model, title, etc. I want a single report
that will give me a total number of computers and a total number of licenses.


My problem is I'm using a single table in which "COMPUTERS" are listed in a
field called "ITEM" in a one-to-one ratio....that is, if we have 250
computers, the word "COMPUTER" appears in the ITEM field 250 times.

Licenses are not in the "ITEM" field....instead I orginally used "SOFTWARE"
as the item and used another field called "TITLE" to differentiate software.
"LICENSES" is another field that lists the number of licenses of a particular
title purchased with a given PO.

Again the goal is simple....a single report that will show number of
computers and number of licenses. Two words, two numbers. And apparently
I'm having to create subreports and write (borrowed) code to do something
fairly routine....I keep thinking I don't understand this application at all
or I'm missing something.
--
Regards,

Rod Smothers


Duane Hookom said:
Not sure exactly what you are asking. My guess is that you want to use the
same report to display with or without details. If this is so, you can add
code to your report like:

'General declarations section
Option Compare Database
Dim booHideDetail As Boolean

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Cancel = booHideDetail
End Sub

Private Sub Report_Open(Cancel As Integer)
booHideDetail = (MsgBox("Hide Details", _
vbQuestion + vbYesNo, "Hide Details") = vbYes)
End Sub
Click to expand...
 
If I understand correctly, you can create an unbound report with two text
boxes. Set the control sources to something like:

=DCount("*","[Single table]", "ITEM='COMPUTERS'")
and
=DSum("LICENCES","[Single table]", "ITEM='SOFTWARE'")

You may need to change these to match your requirements.
--
Duane Hookom
MS Access MVP
--

Rod Smothers said:
OK, I'll try adding the code you provided....just two questions....uh,
where
and how to add the code. I know these are big questions and I'll research
it
but if you have any good resources, I'd appreciate links or whatever.

The goal here is to have a report with two words on it: COMPUTERS,
LICENSES. I have queries and reports for each of these that provide
details
like purchase date, PO number, model, title, etc. I want a single report
that will give me a total number of computers and a total number of
licenses.


My problem is I'm using a single table in which "COMPUTERS" are listed in
a
field called "ITEM" in a one-to-one ratio....that is, if we have 250
computers, the word "COMPUTER" appears in the ITEM field 250 times.

Licenses are not in the "ITEM" field....instead I orginally used
"SOFTWARE"
as the item and used another field called "TITLE" to differentiate
software.
"LICENSES" is another field that lists the number of licenses of a
particular
title purchased with a given PO.

Again the goal is simple....a single report that will show number of
computers and number of licenses. Two words, two numbers. And apparently
I'm having to create subreports and write (borrowed) code to do something
fairly routine....I keep thinking I don't understand this application at
all
or I'm missing something.
Click to expand...
 
I think all you need to do is using COUNT for your computer field and add for
licenses in your query then create a report base on that query, and then put
that report(now is subreport) in main report ...

AQ
Rod Smothers said:
OK, I'll try adding the code you provided....just two questions....uh, where
and how to add the code. I know these are big questions and I'll research it
but if you have any good resources, I'd appreciate links or whatever.

The goal here is to have a report with two words on it: COMPUTERS,
LICENSES. I have queries and reports for each of these that provide details
like purchase date, PO number, model, title, etc. I want a single report
that will give me a total number of computers and a total number of licenses.


My problem is I'm using a single table in which "COMPUTERS" are listed in a
field called "ITEM" in a one-to-one ratio....that is, if we have 250
computers, the word "COMPUTER" appears in the ITEM field 250 times.

Licenses are not in the "ITEM" field....instead I orginally used "SOFTWARE"
as the item and used another field called "TITLE" to differentiate software.
"LICENSES" is another field that lists the number of licenses of a particular
title purchased with a given PO.

Again the goal is simple....a single report that will show number of
computers and number of licenses. Two words, two numbers. And apparently
I'm having to create subreports and write (borrowed) code to do something
fairly routine....I keep thinking I don't understand this application at all
or I'm missing something.
Click to expand...
 
Back
Top