CODEWITHSUNDEEP
ms-access
edbyford
edbyford

Reputation: 67

Form based on a one-to-many relationship - just show parent records with child actions

I have two tables, one with parent records, and one with child records.

The child records are displayed in a subform attached to the bottom of the form containing the parent records.

I want the main form's recordset to only consist of those parent records for which child records exist. However, when I try to do this, I get multiple parent records (i.e. if I flick through the records, I have to pass four parent records if there are four associated child records, etc.).

I think I need to use a GROUP BY but when I try to create a query to do this (so I might be able to base my form on it) it tells me that I need to GROUP on all the fields in the query, then it errors when I do so.

Please help!

Thanks

Upvotes: 0

Views: 819

Answers (1)

Andy G
Andy G

Reputation: 19367

I don't know how you are populating the main (parent) form, but if you find the Record Source for this form, from its Property Sheet, then click the build button (...) to invoke the Query Builder, you can just add in (Show) the child table. Assuming there already exists a relationship between the two tables, this will create an INNER JOIN. If there isn't already a relationship (a join) then drag and drop from the primary key in the parent table to the foreign key in the child table.

You don't need to include any fields from the child table, but you will have to include all the fields you need from the parent table in the query grid. Adding the child table creates, as I mentioned, an INNER JOIN, which will only show records from the parent table if they have records in the child table.

Close and Save the Query Builder.

Upvotes: 1

Related Questions