One to Many Relationship

[Carrie]

Hi

I was wondering if anyone could give me some advice.

I'm currently setting up a data entry form.

Within this dataentry form is a one to many relationship. One person can have many jobs to do. However 99% of the time they only have one job to do. As a result however I've had to create a one to many relationship.

As most of the time only one person does one job I would like to just create one form.
Instead of having to create a mainform and subform structure. This is better from a dataentry point of view.

In fact I have actually created this and it works fine. However when I come to delete a record it only deletes the many side and not the one side.

I was wondering if there is a way around this? Which then made me think maybe I'm going about this the wrong way!!!

Any help would be much appreciated!


Thanks
Carrie

 

[pono1]

Carrie,

I can't tell for certain from your post whether you have one form or a main/sub form set up right now. But since "it only deletes the many side and not the one side" I will assume you currently have a main and subform.

If so, search on "cascading deletes" and "referential integrity" both here and in Help to see how to configure things so that when you delete the one-record in a DB all of the related many-records also get the ax.

Regards,
Tim

 

[Pat Hartman]

The main/sub form is a far superior solution. What difference does it make if the next field in the tab order is in a subform or on the main form. What happens in the small number of cases where you need multiple jobs? You actually make more work for yourself doing this the wrong way than doing it the right way.

If you want to delete a row from the parent table, you need the delete query to be based on the parent table, NOT a join of the parent and child. As you can see, deleting in the second case deletes just the child which is EXACTLY what you would want it to do if you were trying to delete one of many children.

Make sure your relationships are properly defined to enforce RI and specify cascade delete, then switch to a main form based on a query of the parent table and a subform based on a query of the child table.