Query / Report Problem

[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

 

[Duane Hookom]

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.

 

[BobC]

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

 

[BobC]

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".

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:

Control Source: =[AutoLiab-Current Reserves]![Total]
Input Mask
Running sum: no

 

[Duane Hookom]

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

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".

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:

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

 

[BobC]

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 Hookom]

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

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

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.

 

[BobC]

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

 

[BobC]

Duane,

UPDATE!

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

 

[Duane Hookom]

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?