Quering only those Parent Rec where Child rec exist

[I H Naqvi]

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.

 

[Granny Spitz via AccessMonster.com]

Now I want that Master Form Should Show only Those records for which atleast
one Child Record exist.

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;

 

[Marshall Barton]

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.

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

 

[Dirk Goldgar]

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

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);

 

[Marshall Barton]

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);

Good point Dirk.