How do I remove parent-child relationships in the parent table?

[Pat Dools]

Hello,

I have a table ('tSubj_info') that is set up as the 'One' table in my
relationships window. It is joined to two different 'Many' tables --
('tAttendance') and ('tVisit'). In the Datasheet view of the 'tSubj_Info'
table, I noticed the (+) sign in the first column of the table, expanded it,
and noticed that I could set a parent-child relationship. Being the curious
type, I did (between 'tSubj_Info' and 'tVisit') and now I can see all the
Visits related to a particular Subject.

However, when I write a query joining the three tables above, I ONLY get
records returned that have entries in both the 'tSubj_Info' and the 'tVisit'
tables. I don't get records that have 'tSubj_Info' and 'tAttendance' info.
and do NOT have 'tVisit' info. I think this is because of the parent-child
relationship I have set up, but I cannot figure out how to remove it. Where
do I go to remove this in Access 2003?

Thank you.

 

[Larry Linson]

In the Query Builder (and in the Relationships screen, just to make certain)
right-click the Join and change it from "only those records in both..." to
"all the records in table A and only those that match in table D" for each
Join or Relationship.

Larry Linson
Microsoft Office Access MVP

 

[Dirk Goldgar]

Hello,

I have a table ('tSubj_info') that is set up as the 'One' table in my
relationships window. It is joined to two different 'Many' tables --
('tAttendance') and ('tVisit'). In the Datasheet view of the 'tSubj_Info'
table, I noticed the (+) sign in the first column of the table, expanded
it,
and noticed that I could set a parent-child relationship. Being the
curious
type, I did (between 'tSubj_Info' and 'tVisit') and now I can see all the
Visits related to a particular Subject.

However, when I write a query joining the three tables above, I ONLY get
records returned that have entries in both the 'tSubj_Info' and the
'tVisit'
tables. I don't get records that have 'tSubj_Info' and 'tAttendance'
info.
and do NOT have 'tVisit' info. I think this is because of the
parent-child
relationship I have set up, but I cannot figure out how to remove it.
Where
do I go to remove this in Access 2003?

I don't think this is due to the subdatasheet you established for
tSubj_Info, though you can remove that if you want, by opening the table in
design view, opening the table's property sheet, and changing the
Subdatasheet Name property to [None]. I think most likely your query is
giving you the results you see because you are inner-joining tSubj_Info and
tVisit in the query, instead of left-joining them. If youll post the SQL of
the query, we can probably fix it for you.

 

[Pat Dools]

Hi Larry and Dirk,

Both of you are totally correct. I assumed the outer join when the
'one-to-many' relationship was set up.

My bad,
Patrick

Hello,

I have a table ('tSubj_info') that is set up as the 'One' table in my
relationships window. It is joined to two different 'Many' tables --
('tAttendance') and ('tVisit'). In the Datasheet view of the 'tSubj_Info'
table, I noticed the (+) sign in the first column of the table, expanded
it,
and noticed that I could set a parent-child relationship. Being the
curious
type, I did (between 'tSubj_Info' and 'tVisit') and now I can see all the
Visits related to a particular Subject.

However, when I write a query joining the three tables above, I ONLY get
records returned that have entries in both the 'tSubj_Info' and the
'tVisit'
tables. I don't get records that have 'tSubj_Info' and 'tAttendance'
info.
and do NOT have 'tVisit' info. I think this is because of the
parent-child
relationship I have set up, but I cannot figure out how to remove it.
Where
do I go to remove this in Access 2003?

I don't think this is due to the subdatasheet you established for
tSubj_Info, though you can remove that if you want, by opening the table in
design view, opening the table's property sheet, and changing the
Subdatasheet Name property to [None]. I think most likely your query is
giving you the results you see because you are inner-joining tSubj_Info and
tVisit in the query, instead of left-joining them. If youll post the SQL of
the query, we can probably fix it for you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)