Report / Query 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

 

[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

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

 

[Roxie Aho]

Not sure I understand what you're trying to do, but base
your report on the query. Your Curres field should show
up automatically with no need to build the data source.
Roxie Aho

-----Original Message-----
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 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 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]

Roxie,

I have a large table with about 30 fields that I am going to want to do
various queries on the data in order to create a variety of reports.
This was my first attempt to tie a report to a query.
In this case, I want to sum the CURRES fields for all records in
'MASTER' table who'sCOVERAGE field is "LIAB".

Bob

Not sure I understand what you're trying to do, but base
your report on the query. Your Curres field should show
up automatically with no need to build the data source.
Roxie Aho

-----Original Message-----
I made some changes to my original messageformatting to

hopefully make

it more readable.

BobC wrote:

with lots of detail.

(MAHC.mdb) has

bind information

query.

(COVERAGE and

field.

COVERAGE field is

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"

corresponding the

49)'.

and clicked on

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

Bob

 

[Roxie Aho]

-----Original Message-----
Roxie,

I have a large table with about 30 fields that I am going to want to do
various queries on the data in order to create a variety of reports.
This was my first attempt to tie a report to a query.
In this case, I want to sum the CURRES fields for all records in
'MASTER' table who'sCOVERAGE field is "LIAB".

Bob

If you just want a report that shows the sum of CURRES,
you can proceed as you have by creating a report in Design
View but set the Record Source of the Report to the query
you created. Click the Field List button on the toolbar
and drag the field(s) you want on your report.

You should get something that looks like
SumofCURRERS: 500

Then you can play with the formatting.

Another report would show

tblMaster
COVERAGE: Text
CURRES: Number
Other fields:

qryCurres (based on tblMaster)
COVERAGE, criteria is "AUTOLIAB,"
CURRES

rptCurres based on qryCurres
Select Reports from the Database window
Select New
Select AutoReport: Tabular
Select qryCurres as your data source

The AutoReport: Tabular will give you a report that looks
something like this:
COVERAGE CURRES
AUTOLIAB 1
AUTOLIAB 10
etc etc

If you don't want the COVERAGE field to print, change its
Visible property to No.

For a sum at the end
Expand Report Footer
Insert a Text Box in the Report Footer
Set the Control Source property to =Sum([CURRES])

Your report would look something like this:
COVERAGE CURRES
AUTOLIAB 1
AUTOLIAB 10
etc etc
Total 11

Roxie Aho
roxiea(delete)@usinternet.com