One-To-Many Problem

[Tom]

I need some help with table design. Below is the structure of just a few
tables from a database.

There is a One-To-Many relationship between "tblCorrespondence" to
"tblCorrespondenceActionOfficer", and also a One-To-Many relationship
between "tblCorrespondenceActionOfficer" and "tblComments".


tblCorrespondence
=================
- PK = CorrespondenceID


tblCorrespondenceActionOfficer
==============================
- PK = CorrespondenceActionOfficerID
- FK = CorrespondenceIDfk


tblComments
===========
- PK = CommentsID
- FK = CorrespondenceActionOfficerIDfk



Here the problem I'm having with this "junction table" structure. I need
another table (call it "tblNew") that follows the same schema like the
relationships from "tblCorrespondence" to "tblCorrespondenceActionOfficer"
to "
tblComments".

So, here's what I did..
- Copied "tblComments"
- Renamed it to "tblNew"
- Changed "CommentsID" to "NewID"
- Linked "tblNew" to "tblCorrespondenceActionOfficer";
"CorrespondenceActionOfficerID"


When I ran a query now on the 4 table, I don't see any records (not even
blank ones). I believe that I probably can't simply "splice in" the tblNew
into the existing schema.

Do I need to create yet another junction table diverting off
tblCorrespondence to "tblJunctionNew" to "tblNew"?


Thanks in advance,
Tom

 

[Lynn Trapp]

Without knowing what your SQL is like, it's going to be hard for anyone to
give you a definitive answer. Could you post that? I'm a bit puzzled why
you would need another table that has essentially the same data as
tblComments.

 

[Tom]

Lynn:

Thank you for your reply...

Not sure what SQL I would need to provide. At this moment, I simply need an
architecture that supports a 2nd table that also uses a One-To-Many
relationship in the same fashion as tblCorrespondence |
tblCorrespondenceActionOfficer | tblComments are linked.


When I stated that I copied tbl Comments and renamed the PK & FK, I also
meant that the table will also include new fields. So, never mind the "copy
tblComments"... instead I have an "identical relationship" to
tblCorrespondence between the tables.

Sorry for any confusion,
Tom

 

[Lynn Trapp]

Tom,
The problem does not relate to architecture for a 2nd table having a one to
many relationship to a parent table, but how you have joined the tables in
your query. That's why I asked to see the SQL statement that you tried and
got back no records with.

 

[Tom]

Lynn:

Hmh, this is odd...

Step1:
Here's what I have done in Query Design view --
- Added tables: tblCorrespondence, tblCorrespondenceActionOfficer,
tblComments
- Executed Table
- Although there are currently no records stored in the "testing database",
the query shows me that there at least no records stored (I see the fields 1
"empty line" plus the string "Autonumber" for the PKs
- The SQL code for this is below (#1)


Step2:
- Changed to Design View
- Added the "tblNew" to the query
- Executed query
- Here's where I believe the problem lies... I now CANNOT see that there are
no records stored in the database. I simply see the grey field names but I
don't see even a single blank line. This is why I thought I could not link
2 tables via the same FK to the overarching junction table's PK.
- The SQL code for this is below (#2)



Any thoughts what I'm doing wrong?


Thanks,
Tom



SQL QUERY #1:
SELECT tblCorrespondence.CorrespondenceID,
tblCorrespondence.ClassificationIDfk, tblCorrespondence.Author,
tblCorrespondenceActionOfficer.CorrespondenceActionOfficerID,
tblCorrespondenceActionOfficer.CorrespondenceIDfk, tblComments.CommentsID,
tblComments.CorrespondenceActionOfficerIDfk, tblComments.Comment FROM
tblCorrespondence INNER JOIN (tblCorrespondenceActionOfficer INNER JOIN
tblComments ON tblCorrespondenceActionOfficer.CorrespondenceActionOfficerID
= tblComments.CorrespondenceActionOfficerIDfk) ON
tblCorrespondence.CorrespondenceID =
tblCorrespondenceActionOfficer.CorrespondenceIDfk;

SQL QUERY #2:
SELECT tblCorrespondence.CorrespondenceID,
tblCorrespondence.ClassificationIDfk, tblCorrespondence.Author,
tblCorrespondenceActionOfficer.CorrespondenceActionOfficerID,
tblCorrespondenceActionOfficer.CorrespondenceIDfk, tblComments.CommentsID,
tblComments.CorrespondenceActionOfficerIDfk, tblComments.Comment,
tbNew.NewID, tbNew.CorrespondenceActionOfficerIDfk, tbNew.NewField FROM
(tblCorrespondence INNER JOIN (tblCorrespondenceActionOfficer INNER JOIN
tblComments ON tblCorrespondenceActionOfficer.CorrespondenceActionOfficerID
= tblComments.CorrespondenceActionOfficerIDfk) ON
tblCorrespondence.CorrespondenceID =
tblCorrespondenceActionOfficer.CorrespondenceIDfk) INNER JOIN tbNew ON
tblCorrespondenceActionOfficer.CorrespondenceActionOfficerID =
tbNew.CorrespondenceActionOfficerIDfk;

 

[Lynn Trapp]

Tom,
Let me suggest that you put some records in your test database to make sure
that Query 2 will not pull any records. The fact that you do not see a blank
line is an indication that the query is not updateable. If you have records
in the tables, it may very well pull those records. However, on the surface,
it looks like query 2 is way too complex to be updateable.