Table set up

[Guest]

This is an easy one...for anybody but me.

I have two tables and the first is a list of reports, who created the
report, etc. The second table contains the data sources, key metrics,
status, etc for each of the reports. Is there a way I can extract the report
name from table 1 to populate the report name field in the second report?

Your help is appreciated.

 

[Guest]

Presumably you already have a field in each table which identifies the report
in question, e.g. ReportID. If so then you don't need to, and should not,
have a field for the report name in the second table. To duplicate the name
would constitute redundancy which leaves the door open to update anomalies as
you could have the same ReportID in a row in each table with different report
names.

All you need to do is join the two tables in a query on the ReportID fields
and return whatever fields from each table you want in the result table.

Ken Sheridan
Stafford, England

 

[Rick B]

populate the field in the second "report" or "table"?

If you want to populate the field in the second (related) table, then you
don't! That would be redundant. If you are trying to store the same piece
of information in two tables, you are making a mistake (as far as a
relational database is concerned).

If the two tables are related to each other, just include both tables in
your queries. Then, you can pull some fields from one table, and some from
the other, without the need to store the data in both tables. This is the
whole point (and beauty) of a relational database.

Post back if you need more details.

 

[Guest]

One more question...so how will I relate the data in the second table to the
report in the first table? Using the report ID #? I am sorry, but can you
clarify a bit more for me?

 

[Smartin]

One more question...so how will I relate the data in the second table to the
report in the first table? Using the report ID #? I am sorry, but can you
clarify a bit more for me?

Firstly, you do not need to duplicate ReportName in the tables if the
tables are related. If the tables are related, you can fetch any data
element about any report from either table.

Nor do I think you need separate tables for the list and the details,
but we will address that in a moment.

To set up the relationship, let's assume your first table is set up
something like this:

ReportList
-------------------
ReportID (index)
ReportName
WhoCreated

Then your second table needs to look something like this:

ReportDetails
-------------------
ReportDetailsID (index)
ReportID
DataSource
KeyMetrics
Status

Note you need the ReportID key in both tables. It doesn't actually have
to be called the same thing in both tables, but what you do in the next
step will make it easier to understand if they are the same.

Then you open the Relationships window (under Tools), add both tables to
the view, drag the ReportID from one table to the other (landing on
ReportID in the second table), click Create, et voila.

BUT

Now that we've done this, here are a couple things to think about:

1. Each report ID has a name, who created, data source, status. Why not
put these all in one table?

2. Each report ID has potentially many key metrics. Do you intend to
list these in a single field? Are you thinking you need many fields to
list each metric separately? If the latter, you should consider setting
up another table to identify the key metrics, and relating that to
ReportDetails thusly, and remove the KeyMetrics field from table
ReportDetails.

KeyMetrics
-------------------
MetricID (index)
ReportID
MetricDescription

You would then create a different kind of relationship between
ReportDetails and KeyMetrics. In the Relationships window again, add
table KeyMetrics. Drag ReportID from ReportDetails to KeyMetrics, check
"Enforce referential integrity" and "Cascade update related fields". The
Relationship Type should read "One-To-Many". Click Create, et voila.

Does this help?