Quering only those Parent Rec where Child rec exist

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have One Master Form named "Master Record" based on a parent table. There
is one embaded Sub Form "Leave Record" which is based on a Child Table. the
forms are Linked by MS Access automatically when i draged Sub Form on Master
Form. Master Form is working fine showing related child records atomatically.
Now I want that Master Form Should Show only Those records for which atleast
one Child Record exist. Pl Help me as I m new in MS Access.
 
I said:
Now I want that Master Form Should Show only Those records for which atleast
one Child Record exist.
Click to expand...

Make the main form's record source a query, not the Master table. The query
should look like this where the foreign key (V_CODE in this example) is the
column joined on:

SELECT Master.*
FROM Master INNER JOIN Child ON Master.V_CODE = Child.V_CODE;
 
I said:
I have One Master Form named "Master Record" based on a parent table. There
is one embaded Sub Form "Leave Record" which is based on a Child Table. the
forms are Linked by MS Access automatically when i draged Sub Form on Master
Form. Master Form is working fine showing related child records atomatically.
Now I want that Master Form Should Show only Those records for which atleast
one Child Record exist. Pl Help me as I m new in MS Access.
Click to expand...

Change the form's record source to a query that filters out
the undesired records. Something like this should be
sufficient:

SELECT DISTINCT tblparent.*
FROM tblparent INNER JOIN tblchild
ON tblparent.pkfield = tblchild.fkfield
 
Marshall Barton said:
Change the form's record source to a query that filters out
the undesired records. Something like this should be
sufficient:

SELECT DISTINCT tblparent.*
FROM tblparent INNER JOIN tblchild
ON tblparent.pkfield = tblchild.fkfield
Click to expand...

But that query won't be updatable, so the main form records can't be
edited. If the poster wants the main form to be editable, this might be
used:

SELECT tblParent.*
FROM tblParent
WHERE EXISTS
(SELECT * FROM tblChild
WHERE tblChild.fkfield = tblParent.pkfield);
 
Dirk said:
But that query won't be updatable, so the main form records can't be
edited. If the poster wants the main form to be editable, this might be
used:

SELECT tblParent.*
FROM tblParent
WHERE EXISTS
(SELECT * FROM tblChild
WHERE tblChild.fkfield = tblParent.pkfield);
Click to expand...


Good point Dirk.
 
Back
Top