How to check if a parent record has any child records

[ShanVel]

Hi,
I have an access database (2007) and I have a parent table (Projects) and several child tables.

For a new project record, I want to copy a child record from another project's child record (as a new project's child record) and then plan to edit the copied record. My copy operation works fine in VBA but I wanted to add a check point so that the user can do this operation only if the new project's child tables contains no associated child records (i.e. there shouldn't be no child record for the new project before proceeding to copy operation).

So, my question is how do I check if a parent (a project) record has no child record (i.e. a child table with no records yet)?

I checked the forum but no direct help. Any advise is greatly aprreciated.

I work as a mechanical engineer for living but my Access SQL and VBA skill is just about average.

Thanks.

 

[Thinh]

you can create a query that links the parent table with the child table and check if the Child table fields is null. this means you have to have an outer join on parent table(all records) and only matching records in the child. if the record doesn't exist in child it will appear as a null value.

 

[ShanVel]

Hi Thinh,
Thanks for your tips. That is correct but I am not sure how do you build your SQL if I have more than 50 child tables related to one parent.

The SQL based on your idea would llook something like this, if there is one child:

SELECT tblProject.intProjectId, tblProject.chrProjectName, tblChild1.intChildId
FROM tblProject LEFT JOIN tblChild1 ON tblProject.intProjectId = tblChild1.intProjectId WHERE (((tblChild1.intChildId) Is Null));

Do you know if there is any system objects that we can use to build SQL to handle all the child tables? Otherwise the SQL gets too complex and I couldn't build the query.

 

[gemma-the-husky]

how do you have 50 child tables?

you shouldnt have multiple detailed project tables

you should only need (in principle)

project headers table
project details table

the point is that each item in the project details table carries a link to its project header - thats why you dont need separate details tables for EACH project