Change table associated with report

[sasquatchlgmt]

I have several tables in my database that contain First and Last names. Call
them Actors, Directors and Producers. I created a report by highlighting the
Actors table and clicking on the create report. I then modified the report
format so that the Actors names are listed "Last, First" using event builder.
After getting the report formated the way I wanted it, I saved it as rActorsI
then copied and renamed the report as rDirectors. I have been unable to find
a way to disassociate the Actors table from the report and associate the
Directors table to the report so that when I modify the field names in the
event builder that the report will work. The field names in the tables are
unique e.g. they are all contain "First Name" and "Last Name" but are
prefixed with "Actor " or "Dir " or "Prod ".

While I can obviously restart from scratch for such a simple formatting,
there should be a way to disassociated one table and then associate a
different one with the report for similar reports with similare fields in
different tables with out having to totally recreate the report.

David

 

[Allen Browne]

You can open the report in design view, and change its Record Source
property to the name of the table you want it to draw from.

A real solution would be to use a normalized design.
1. Place everyone in one 'Client' table, with a ClientID primary key.

2. Create another table to give a list of the roles a person can have (e.g.
director, producer, actor, writer, ...), with a RoleID primary key.

3. Create a 3rd table with these 2 fields:
- ClientID
- RoleID
Now if someone has 2 roles (e.g. Clint Eastwood is both an actor and a
director), he will appear 2ce in this table.

You can now create a report based on the 3 tables, and list the roles for
each person, or filter to just the actors, or ...