two tables in a report

[ted]

I have a report that I would like to bind fields from one table to a text
box in the header. In the details I would like to bind fields from another
table to a text box. Set the record source from one table but unable for the
second table. Went to the expression generator & clicked on the table2 then
fieldx & got something like =[table2]![fieldx] but when executing the report
it keeps asking about table2 & then giving an error. HELP! TIA

 

[KARL DEWEY]

Try using the two tables joined in a query or use report and sureport.

 

[John W. Vinson]

I have a report that I would like to bind fields from one table to a text
box in the header. In the details I would like to bind fields from another
table to a text box. Set the record source from one table but unable for the
second table. Went to the expression generator & clicked on the table2 then
fieldx & got something like =[table2]![fieldx] but when executing the report
it keeps asking about table2 & then giving an error. HELP! TIA

How are the tables related, if at all?

A Report can have only one Recordsource; frequently this is a Query involving
two or more tables. If the record in the header is logically related to the
records in the detail section this should work. Or, you might need a Report
and a Subreport. It all depends on the structure and relationships of your
tables.

 

[ted]

I have a report that I would like to bind fields from one table to a text
box in the header. In the details I would like to bind fields from another
table to a text box. Set the record source from one table but unable for
the
second table. Went to the expression generator & clicked on the table2
then
fieldx & got something like =[table2]![fieldx] but when executing the
report
it keeps asking about table2 & then giving an error. HELP! TIA

How are the tables related, if at all?

No relationship, one table is the dates of class & the other is the
attendence of the class.

 

[Jeff Boyce]

Ted

If you wish to print two unrelated sets of data in a single report, you can:

1. create a report about the first set of data
2. create a report about the second set of data
3. create an "empty" report and use #1 & #2 as sub-reports in that empty
report

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that I would like to bind fields from one table to a text
box in the header. In the details I would like to bind fields from
another
table to a text box. Set the record source from one table but unable for
the
second table. Went to the expression generator & clicked on the table2
then
fieldx & got something like =[table2]![fieldx] but when executing the
report
it keeps asking about table2 & then giving an error. HELP! TIA

How are the tables related, if at all?

No relationship, one table is the dates of class & the other is the
attendence of the class.

 

[KARL DEWEY]

No relationship, one table is the dates of class & the other is the
attendence of the class.
Seems to me the 'class' is the relation.

--
KARL DEWEY
Build a little - Test a little

I have a report that I would like to bind fields from one table to a text
box in the header. In the details I would like to bind fields from another
table to a text box. Set the record source from one table but unable for
the
second table. Went to the expression generator & clicked on the table2
then
fieldx & got something like =[table2]![fieldx] but when executing the
report
it keeps asking about table2 & then giving an error. HELP! TIA

How are the tables related, if at all?

No relationship, one table is the dates of class & the other is the
attendence of the class.

 

[Ken Sheridan]

You haven't given us a lot to go on, but I'd envisage a set of tables along
these lines:

Classes
………Class

Sessions
………SessionDate
………Class

Attendance
………SessionDate
………Class
………StudentID

Students
………StudentD
………FirstName
………LastName
………<etc>

In the first table, Classes, I'm using the term 'class' to mean the title of
a series of sessions rather than the individual events, so it would contain
unique values of each class title. This would be its primary key of course.
You can of course change the table and column names to whatever you wish, but
one thing I would suggest is that you use the same name for a foreign key
column in a referencing (many-side) table as that for the primary key column
of the referenced (one-side) table.

Sessions would contain unique pairs of values of the date and class title
per session. In this table the primary key would be a composite one of both
columns. The Class column is a foreign key referencing the primary key of
classes.

Attendance models the many-to-many relationship between Students and
Sessions, referencing the primary key of sessions with the composite foreign
key made up of SessionDate and Class, and the primary key of Students with
the StudentID foreign key. So the relationships would look like this:

Classes---<Sessions---<Attendance>---Students

where the < and > signs indicate the 'many' side of each relationship type.

For your report's RecordSource you simply join the last three tables in a
query. You don't need to include the Classes table as the Sessions table
contains the Class titles. This is because 'natural' keys are used here, the
class title text value. If, however, you used surrogate numeric keys, e.g.
ClassID, then you'd also need to include the Classes table in the query to
return the class title. You might think that the Classes table is
unnecessary, but that is not the case as it allows you to enforce referential
integrity in the relationship between Classes and Sessions, thus preventing
an invalid class title from being entered in Sessions. It also means that
the database can include classes for which no sessions have yet been
scheduled.

Note that Students does use a surrogate StudentID key, e.g. an autonumber.
This because personal names can be duplicated so are unsuitable as keys.

Ken Sheridan
Stafford, England