One-To-Many Problem

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

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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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:

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" wrote in message
...
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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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

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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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" wrote in message
...
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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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:

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.CorrespondenceActio nOfficerID,
tblCorrespondenceActionOfficer.CorrespondenceIDfk, tblComments.CommentsID,
tblComments.CorrespondenceActionOfficerIDfk, tblComments.Comment FROM
tblCorrespondence INNER JOIN (tblCorrespondenceActionOfficer INNER JOIN
tblComments ON tblCorrespondenceActionOfficer.CorrespondenceActio nOfficerID
= tblComments.CorrespondenceActionOfficerIDfk) ON
tblCorrespondence.CorrespondenceID =
tblCorrespondenceActionOfficer.CorrespondenceIDfk;

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












"Lynn Trapp" wrote in message
...
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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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" wrote in message
...
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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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

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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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.CorrespondenceActio nOfficerID,
tblCorrespondenceActionOfficer.CorrespondenceIDfk, tblComments.CommentsID,
tblComments.CorrespondenceActionOfficerIDfk, tblComments.Comment FROM
tblCorrespondence INNER JOIN (tblCorrespondenceActionOfficer INNER JOIN
tblComments ON
tblCorrespondenceActionOfficer.CorrespondenceActio nOfficerID
= tblComments.CorrespondenceActionOfficerIDfk) ON
tblCorrespondence.CorrespondenceID =
tblCorrespondenceActionOfficer.CorrespondenceIDfk;

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












"Lynn Trapp" wrote in message
...
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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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" wrote in message
...
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.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
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