Query / Report Problem

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

I am new at this, so if you can help, please explain with lots of detail.

I'm using: WIN98(SE) and Access 2000.
The database (MAHC.mdb) has several tables, but I am at the moment only
trying to bind information from one of the tables (MASTER) to a report
using a query.
I have create the basic report in 'Design View'.
I designed the query using the 'wizard'.
In the query (AUTO), I am only using two of the fields (COVERAGE and
CURRES).
COVERAGE is a 'text' field and CURRES is a 'number' field.
I want to sum all records in 'MASTER' table who's COVERAGE field is "LIAB".

The structure of the query is as follows:
Field: Total: CURRES COVERAGE
Table: MASTER MASTER
Total: Sum Expression
Sort:
Show 'checked' 'not checked'
Criteria "AUTOLIAB"

When I run the query, I get
TOTAL
500

500 is the correct sum of all the 'CURRES' fields corresponding the
COVERAGE fields that contain "AUTOLIAB'.

In the Report (SUMMARY) I placed a 'text box (Text Box 49)'.
In properties of Text Box 49, I chose the "Data' tap and clicked on
'...' to get the expression builder.
In the Expression Builder, first double-clicked on the query (AUTO)I
then got: 'Total' in the center window and '<value>' in the right window.
I double-clicked on 'value' and got '[AutoLiab-Current Reserves]![Total]
' in the Window.

Now is get: (under the 'data' tab)
Control Source: =[AutoLiab-Current Reserves]![Total]
Input Mask
Running sum: no

When I preview my report, I get "#Name?" in Text Box 49 instead of the
desired answer of "500".

Can anyone tell me what I am doing wrong?

Bob
 
To count the number of records in your report, add a text box to the Report
Header or Footer and set the control source to:
=Count(*)
If you want to sujm a field
=Sum([NumericField])

BTW: since you are new, it's a great time to start using a naming
convention. I would think about something more descriptive than Master and
Auto.
 
I do not understand ? Are you saying to change something in the query
or the report?
I first want to do the sum function in the query, and as far as I can
tell, that part 'appears' to be working.
I then want to retreive the results of the query in the report by using
the query as the source; that part seems broke!
Bob
 
I made some changes to my original messageformatting to hopefully make
it more readable.
I am new at this, so if you can help, please explain with lots of detail.

I'm using: WIN98(SE) and Access 2000. The database (MAHC.mdb) has
several tables, but I am at the moment only trying to bind information
from one of the tables (MASTER) to a report using a query.
I have create the basic report in 'Design View'.
I designed the query using the 'wizard'.
In the query (AUTO), I am only using two of the fields (COVERAGE and
CURRES).
COVERAGE is a 'text' field and CURRES is a 'number' field.
I want to sum all records in 'MASTER' table who's COVERAGE field is
"LIAB".
Click to expand...

The structure of the query is as follows:
Field: Total: CURRES COVERAGE
Table: MASTER MASTER
Total: Sum Expression
Sort: (blank) (blank)
Show (checked) (not checked)
Criteria "AUTOLIAB"
When I run the query, I get
TOTAL
500

500 is the correct sum of all the 'CURRES' fields corresponding the
COVERAGE fields that contain "AUTOLIAB'.

In the Report (SUMMARY) I placed a 'text box (Text Box 49)'.
In properties of Text Box 49, I chose the "Data' tap and clicked on
'...' to get the expression builder.
In the Expression Builder, first double-clicked on the query (AUTO)


I double-clicked on 'value' and got '[AutoLiab-Current
Reserves]![Total] ' in the query window.

Under the 'data' tab of properties, I now get:
Click to expand...

Control Source: =[AutoLiab-Current Reserves]![Total]
Input Mask
Running sum: no
 
Is the record source of the report the query you designed? If so, view the
fields and drag the total field to your report. If the report has a
different record source, then add a text box with a control source of:
=DSum("Total","[AutoLiab-Current Reserves]")

As you have experienced, you can't use a simple table!field expression in
the control source of a control.

BTW: consider finding and using a good naming convention.

--
Duane Hookom
MS Access MVP


BobC said:
I made some changes to my original messageformatting to hopefully make
it more readable.
I am new at this, so if you can help, please explain with lots of detail.

I'm using: WIN98(SE) and Access 2000. The database (MAHC.mdb) has
several tables, but I am at the moment only trying to bind information
from one of the tables (MASTER) to a report using a query.
I have create the basic report in 'Design View'.
I designed the query using the 'wizard'.
In the query (AUTO), I am only using two of the fields (COVERAGE and
CURRES).
COVERAGE is a 'text' field and CURRES is a 'number' field.
I want to sum all records in 'MASTER' table who's COVERAGE field is
"LIAB".
Click to expand...

The structure of the query is as follows:
Field: Total: CURRES COVERAGE
Table: MASTER MASTER
Total: Sum Expression
Sort: (blank) (blank)
Show (checked) (not checked)
Criteria "AUTOLIAB"
When I run the query, I get
TOTAL
500

500 is the correct sum of all the 'CURRES' fields corresponding the
COVERAGE fields that contain "AUTOLIAB'.

In the Report (SUMMARY) I placed a 'text box (Text Box 49)'.
In properties of Text Box 49, I chose the "Data' tap and clicked on
'...' to get the expression builder.
In the Expression Builder, first double-clicked on the query (AUTO)


I double-clicked on 'value' and got '[AutoLiab-Current
Reserves]![Total] ' in the query window.

Under the 'data' tab of properties, I now get:
Click to expand...

Control Source: =[AutoLiab-Current Reserves]![Total]
Input Mask
Running sum: no
When I preview my report, I get "#Name?" in Text Box 49 instead of
the desired answer of "500".

Can anyone tell me what I am doing wrong?
Bob
Click to expand...
Click to expand...
 
Duane,

Yes, the record source of the report is the query I designed.
(apparently not too well...)

I know the names are a bit weird, but they are acronyms and
abbreviations that stand for the functions they are performing. I was
given those names to work with. I suppose I could change them or use
aliases, but since I understood the names as I was given; I chose not to
change them unless they conflicted with some kind of reserve word or
whatever.

I will see if I can better explain what I want to do ... sorry, but I am
just learning; and THANKS for trying to help me!

I was given a table that contains about 2200 records with 33 fields. I
was also given a couple smaller tables that contain names, addresses,
etc., but for the moment, they do not play into the picture. I will
eventually need to create 13 reports that are based primarily on this
table by using more complex queries.

I this, my first attempt, I am trying to locate the records in the
MASTER table in which the COVERAGE field contains "AUTOLIAB" and sum on
the corresponding values that are contained in the CURRES field.

I am hoping that if I can get the linking of the queries to the reports,
then I will be able to merely change the queries to generate the various
elements of the reports. So far, I have not succeeded in getting a
simple sum to work.

I was able to tie a query to a report using the report wizard, but I
have not been able to do the same thing when I generate the report using
the 'design view'. (The report wizard does not allow me to configure the
reports the way I need.)

Thanks again for your help!

Bob
 
While in the design view of a report, you can view the report properties,
data tab and set the control source to a table, query, or sql statement.
This allows you to view the fields and drag a field to your report. Have you
tried this with your report?

--
Duane Hookom
MS Access MVP


BobC said:
Duane,

Yes, the record source of the report is the query I designed.
(apparently not too well...)

I know the names are a bit weird, but they are acronyms and
abbreviations that stand for the functions they are performing. I was
given those names to work with. I suppose I could change them or use
aliases, but since I understood the names as I was given; I chose not to
change them unless they conflicted with some kind of reserve word or
whatever.

I will see if I can better explain what I want to do ... sorry, but I am
just learning; and THANKS for trying to help me!

I was given a table that contains about 2200 records with 33 fields. I
was also given a couple smaller tables that contain names, addresses,
etc., but for the moment, they do not play into the picture. I will
eventually need to create 13 reports that are based primarily on this
table by using more complex queries.

I this, my first attempt, I am trying to locate the records in the
MASTER table in which the COVERAGE field contains "AUTOLIAB" and sum on
the corresponding values that are contained in the CURRES field.

I am hoping that if I can get the linking of the queries to the reports,
then I will be able to merely change the queries to generate the various
elements of the reports. So far, I have not succeeded in getting a
simple sum to work.

I was able to tie a query to a report using the report wizard, but I
have not been able to do the same thing when I generate the report using
the 'design view'. (The report wizard does not allow me to configure the
reports the way I need.)

Thanks again for your help!

Bob







Duane said:
Is the record source of the report the query you designed? If so, view the
fields and drag the total field to your report. If the report has a
different record source, then add a text box with a control source of:
=DSum("Total","[AutoLiab-Current Reserves]")

As you have experienced, you can't use a simple table!field expression in
the control source of a control.

BTW: consider finding and using a good naming convention.
Click to expand...
Click to expand...
 
Duane,

I was not able to 'drag a field" ... remember, I am new at this
I was able to set the 'record source' under the data tab to a query
named CurrRes(AutoLiab)
The record sourc now shows SELECT [CurrRes(AutoLiab)].[SumOfCURRES], *
FROM [CurrRes(AutoLiab)];

The structure of the query is as follows:

Field: CURRES COVERAGE
Table: MASTER MASTER
Total: Sum Expression
Sort: (blank) (blank)
Show (checked) (NOT checked)
Criteria "AUTOLIAB"

When I run the query, I get the correct answer ... 500

I could not seem to drag any fields ... I guess I am in the wrong window or something!

Thanks for being so patient! ... and for you help!

Also, I will go buy another book, I you you can recommend one that will explain this for a beginner.
I currently have:
Using Microsoft Access 2000 by Roger Jennings
Microsoft Step by Step (Access 2000), Microsoft Press ... just bought this one!

Bob
 
Duane,

UPDATE!

I think I'm catching on!
I'd still be interested in your book recommendations!
Bob
 
Check John Viescas' site http://www.viescas.com/Info/books.htm. Don't forget to check out the books he has authored.

--
Duane Hookom
MS Access MVP


Duane,

UPDATE!

I think I'm catching on!
I'd still be interested in your book recommendations!
Bob

Duane Hookom wrote:

While in the design view of a report, you can view the report properties,
data tab and set the control source to a table, query, or sql statement.
This allows you to view the fields and drag a field to your report. Have you
tried this with your report?
 
Back
Top