Joint Tables

I am new to Microsoft Access and working on constructing my first database.

I have several tables that hold data. One table contains records of when
certain types of samples are prepared and where they are stored (or used) and
general information like that. I call this the Master Record.

A second table contains chemical names and two associated OLE ojbects
(bitmap images and Canvas9 files) displaying the chemical structures and
phase diagrams.

I need to call a report that prints out information from both tables (the
Master and the Chemical Information). Right now, the report I have does not
seem to work.

I've been playing with the Relationships Menu and I tried to create a Joint
Table so that a Many-to-Many Relationship is defined -- but I'm not sure how
to structure the information in the Joint Table. Does anyone have a better
explanation of how to set up a joint table then the Microsoft Help
description?

A copy of my database can be viewed:
http://ucsub.colorado.edu/~heberlin/FFdatabase.mdb

thanks for your help.

The general information table sounds corrupt. If each table has a unique ID,
then each subordinate table should have a "foreign key". The foreign key is a
'duplicate yes' field that references a record in the parental tier. A data
design in this form can support the one-to-many relationship. Therefore, you
don't need a bridge table, unless higher security structures preclude any
other design. If that is the case then you can merge data into a recordset
with the query builder.

I don't see that there is a many-to-many relationship here. It looks like a
simple many-to-one relationship between the Master Freeze Fracture Record
table and the List of Default Chemical Labels columns. If you change the
RecordSource of your report to the following query:

SELECT
[Master Freeze Fracture Record].*,[List of Default Chemical Labels].[Chemical Formula],[List of Default Chemical Labels].[Phase Diagram]
FROM[List of Default Chemical Labels] INNER JOIN [Master Freeze Fracture
Record]
ON[List of Default Chemical Labels].[Chemical Label]=[Master Freeze
Fracture Record].[Chemical Label];

And then change the ControlSource properties of the two bound object frames
to Phase Diagram and Chemical Formula you should find the report works.

Looking at your Master Freeze Fracture Record table I notice that the
Catalogue Name appears to be made up of the Chemical Label + the Target Phase
+ a string of other characters. If this is always the case this creates a
certain amount of redundancy and it would be possible for the values in these
three columns to be entered inconsistently (what is known in the jargon as an
update anomaly). It would be better if the Chemical Label and Target Phase
values were omitted from the Catalogue Name column's values, keeping only the
non-redundant part of the Catalogue Name in this column. The full Catalogue
Name can always be computed in a query by concatenating the values of the
three columns.

I see also that your table of Investigators has just the one column of
names. Personal names do not make good keys as they can be duplicated (I
worked once with two Maggie Taylors in a single office). Its would be better
to use a unique numeric InvestigatorID column as the key (an autonumber
column can be used for this). This would enable duplicate names to be
entered in the table if this should prove necessary at some time in the
future. The foreign key in the Master Freeze Fracture Record table would
then be a numeric InvestigatorID also, but not an autonumber this time, just
a straightforward long integer number. Two people of the same name can this
be distinguished. You would need some other column of meaningful data in the
Investigator table to do this of course (e.g. a job title). I realize that
this probably sounds like a remote possibility in the context of a small
number of personnel, but good database design caters for all possible
eventualities, however remote they may seem.

While I don't think it applies here a table which models a many-to-many
relationship type essentially has two foreign key columns each of which
referencing the primary keys of the tables modelling the entity types between
which the many-to-many relationship exists. The two foreign key columns
together form the composite primary key of the table. To take a simple
example, each Supplier might supply many Products, and each Product might be
supplied by many Suppliers; so the table modelling the relationship type
ProductsSupplied would have columns SupplierID and ProductID. The
many-to-many relationship type is thus modelled by being resolved to two
one-to-many relationships types. A relationship type is itself an entity
type, of a special kind, and can have its own attributes represented by
columns. In this simplified scenario the ProductsSupplied table might for
instance have a UnitPrice column representing the price charged by each
supplier for the product in question.

Ken Sheridan
Stafford, England

"Fawkes" wrote:

I am new to Microsoft Access and working on constructing my first database.

I have several tables that hold data. One table contains records of when
certain types of samples are prepared and where they are stored (or used) and
general information like that. I call this the Master Record.

A second table contains chemical names and two associated OLE ojbects
(bitmap images and Canvas9 files) displaying the chemical structures and
phase diagrams.

I need to call a report that prints out information from both tables (the
Master and the Chemical Information). Right now, the report I have does not
seem to work.

I've been playing with the Relationships Menu and I tried to create a Joint
Table so that a Many-to-Many Relationship is defined -- but I'm not sure how
to structure the information in the Joint Table. Does anyone have a better
explanation of how to set up a joint table then the Microsoft Help
description?

A copy of my database can be viewed:
http://ucsub.colorado.edu/~heberlin/FFdatabase.mdb

thanks for your help.

Ken Sheridan wrote:
your table of Investigators has just the one column of
names. Personal names do not make good keys as they can be duplicated (I
worked once with two Maggie Taylors in a single office). Its would be better
to use a unique numeric InvestigatorID column as the key (an autonumber
column can be used for this). This would enable duplicate names to be
entered in the table if this should prove necessary at some time in the
future.

An autonumber (ID) column does not make a good key either *because*
they permit duplicates to be created e.g. if you have Maggie Taylor ID
= 1 and Maggie Taylor ID = ID then how do you know they are or are not
the same person? Even the advocates of using an autonumber as a
'surrogate for 'efficiency' reasons agree that you also need a natural
key, otherwise you have no data integrity.

In order to 'break' duplicates, the OP would need to use something that
exists in the reality and is appropriate to the circumstances:
nickname, phone extension number, date of birth, SSN, fingerprints,
etc.

Jamie.

--

Thank you Mr. Sheridan:

You are correct that you did not see a Many-to-Many relationship defined
here. After reading Mr. Jason S.'s feedback, I realized that I did not need
a Many-to-Many relationship at all, and only a One-to-Many relationship, and
so I modified my sample database.

I appreciate your well thought out response. I tried generating a query to
pull data from multiple tables yesterday, but I did not know that the .*
pulled data from all fields of a column, and I did not know how to extend the
Query beyond the 16 or so parameters that the design view allows.

At any rate, following your suggestion, (and figuring out where the carriage
returns should actually be) I created the Query and the report works.

Regarding your comment of the Catalogue Number, I agree. If I was starting
the records now, it would be duplicate information. Unfortunately however, I
have 100+ records to enter into this database from old lab notebooks, many of
which did not assign a catalogue number consistent with the system we now
use. Because of this, the catalogue number is actually unique in many cases,
if not actually for all of the records, but it does indeed need be separate
from the other two categories.

I also concede that a well planned out database needs to consider and leave
room for all remote possibilities (and working Admissions in a Hospital ER
for 3 years I am fully aware of how frequently duplicate names appear --
however unlikely. Once we had a heart attack patient arrive in our ER from
another of our campuses and the nurse sent down the wrong chart from another
patient admitted on the same floor, with the same name, born in the same
month only a year apart! The patient was admitted under the incorrect
Medical Record Number and it took 6 months to sort out the mess). However,
since the record data will be entered using the Investigator's name, and not
their ID number, as was the case with the medical system -- I purposefully
intended to force them to come up with some other way to uniquely identify
themselves (i.e. by nickname, suffix or simply by adopting the British habit
of assuming 5 names).

At any rate, thank you for your help -- most especially with the Query syntax.


Respectfully,


Michael-Scott




"Ken Sheridan" wrote:

I don't see that there is a many-to-many relationship here. It looks like a
simple many-to-one relationship between the Master Freeze Fracture Record
table and the List of Default Chemical Labels columns. If you change the
RecordSource of your report to the following query:

SELECT
[Master Freeze Fracture Record].*,
[List of Default Chemical Labels].[Chemical Formula],
[List of Default Chemical Labels].[Phase Diagram]
FROM[List of Default Chemical Labels] INNER JOIN [Master Freeze Fracture
Record]
ON[List of Default Chemical Labels].[Chemical Label]=[Master Freeze
Fracture Record].[Chemical Label];

And then change the ControlSource properties of the two bound object frames
to Phase Diagram and Chemical Formula you should find the report works.

Looking at your Master Freeze Fracture Record table I notice that the
Catalogue Name appears to be made up of the Chemical Label + the Target Phase
+ a string of other characters. If this is always the case this creates a
certain amount of redundancy and it would be possible for the values in these
three columns to be entered inconsistently (what is known in the jargon as an
update anomaly). It would be better if the Chemical Label and Target Phase
values were omitted from the Catalogue Name column's values, keeping only the
non-redundant part of the Catalogue Name in this column. The full Catalogue
Name can always be computed in a query by concatenating the values of the
three columns.

I see also that your table of Investigators has just the one column of
names. Personal names do not make good keys as they can be duplicated (I
worked once with two Maggie Taylors in a single office). Its would be better
to use a unique numeric InvestigatorID column as the key (an autonumber
column can be used for this). This would enable duplicate names to be
entered in the table if this should prove necessary at some time in the
future. The foreign key in the Master Freeze Fracture Record table would
then be a numeric InvestigatorID also, but not an autonumber this time, just
a straightforward long integer number. Two people of the same name can this
be distinguished. You would need some other column of meaningful data in the
Investigator table to do this of course (e.g. a job title). I realize that
this probably sounds like a remote possibility in the context of a small
number of personnel, but good database design caters for all possible
eventualities, however remote they may seem.

While I don't think it applies here a table which models a many-to-many
relationship type essentially has two foreign key columns each of which
referencing the primary keys of the tables modelling the entity types between
which the many-to-many relationship exists. The two foreign key columns
together form the composite primary key of the table. To take a simple
example, each Supplier might supply many Products, and each Product might be
supplied by many Suppliers; so the table modelling the relationship type
ProductsSupplied would have columns SupplierID and ProductID. The
many-to-many relationship type is thus modelled by being resolved to two
one-to-many relationships types. A relationship type is itself an entity
type, of a special kind, and can have its own attributes represented by
columns. In this simplified scenario the ProductsSupplied table might for
instance have a UnitPrice column representing the price charged by each
supplier for the product in question.

Ken Sheridan
Stafford, England

"Fawkes" wrote:

I am new to Microsoft Access and working on constructing my first database.

I have several tables that hold data. One table contains records of when
certain types of samples are prepared and where they are stored (or used) and
general information like that. I call this the Master Record.

A second table contains chemical names and two associated OLE ojbects
(bitmap images and Canvas9 files) displaying the chemical structures and
phase diagrams.

I need to call a report that prints out information from both tables (the
Master and the Chemical Information). Right now, the report I have does not
seem to work.

I've been playing with the Relationships Menu and I tried to create a Joint
Table so that a Many-to-Many Relationship is defined -- but I'm not sure how
to structure the information in the Joint Table. Does anyone have a better
explanation of how to set up a joint table then the Microsoft Help
description?

A copy of my database can be viewed:
http://ucsub.colorado.edu/~heberlin/FFdatabase.mdb

thanks for your help.

I experienced an even more bizarre case of confusion of identities when I was
attending an outpatients clinic at our local hospital here; two women of
exactly the same name and exactly the same date of birth were attending the
same clinic! Murphy's Law dictated that their notes were interchanged of
course.

Incidentally the position of carriage returns in an SQL statement is
entirely arbitrary; they are inserted purely to aid readability.

Ken Sheridan
Stafford, England

"Fawkes" wrote:

Thank you Mr. Sheridan:

You are correct that you did not see a Many-to-Many relationship defined
here. After reading Mr. Jason S.'s feedback, I realized that I did not need
a Many-to-Many relationship at all, and only a One-to-Many relationship, and
so I modified my sample database.

I appreciate your well thought out response. I tried generating a query to
pull data from multiple tables yesterday, but I did not know that the .*
pulled data from all fields of a column, and I did not know how to extend the
Query beyond the 16 or so parameters that the design view allows.

At any rate, following your suggestion, (and figuring out where the carriage
returns should actually be) I created the Query and the report works.

Regarding your comment of the Catalogue Number, I agree. If I was starting
the records now, it would be duplicate information. Unfortunately however, I
have 100+ records to enter into this database from old lab notebooks, many of
which did not assign a catalogue number consistent with the system we now
use. Because of this, the catalogue number is actually unique in many cases,
if not actually for all of the records, but it does indeed need be separate
from the other two categories.

I also concede that a well planned out database needs to consider and leave
room for all remote possibilities (and working Admissions in a Hospital ER
for 3 years I am fully aware of how frequently duplicate names appear --
however unlikely. Once we had a heart attack patient arrive in our ER from
another of our campuses and the nurse sent down the wrong chart from another
patient admitted on the same floor, with the same name, born in the same
month only a year apart! The patient was admitted under the incorrect
Medical Record Number and it took 6 months to sort out the mess). However,
since the record data will be entered using the Investigator's name, and not
their ID number, as was the case with the medical system -- I purposefully
intended to force them to come up with some other way to uniquely identify
themselves (i.e. by nickname, suffix or simply by adopting the British habit
of assuming 5 names).

At any rate, thank you for your help -- most especially with the Query syntax.


Respectfully,


Michael-Scott

I did actually make that point in my original reply:

' Two people of the same name can thus be distinguished. You would need
some other column of meaningful data in the Investigator table to do this of
course (e.g. a job title).'

For a discussion of the pros and cons of surrogate and 'natural' keys see
Joe Celko's compilation of posts from the old Compuserve CASE forum,
available at:


http://community.netscape.com/n/pfx/...g=ws-msdevapps


Ken Sheridan
Stafford, England

"Jamie Collins" wrote:


Ken Sheridan wrote:
your table of Investigators has just the one column of
names. Personal names do not make good keys as they can be duplicated (I
worked once with two Maggie Taylors in a single office). Its would be better
to use a unique numeric InvestigatorID column as the key (an autonumber
column can be used for this). This would enable duplicate names to be
entered in the table if this should prove necessary at some time in the
future.

An autonumber (ID) column does not make a good key either *because*
they permit duplicates to be created e.g. if you have Maggie Taylor ID
= 1 and Maggie Taylor ID = ID then how do you know they are or are not
the same person? Even the advocates of using an autonumber as a
'surrogate for 'efficiency' reasons agree that you also need a natural
key, otherwise you have no data integrity.

In order to 'break' duplicates, the OP would need to use something that
exists in the reality and is appropriate to the circumstances:
nickname, phone extension number, date of birth, SSN, fingerprints,
etc.

Jamie.