Parent - Child Relationship in the same table

[Chaz]

Hi All,

Trying to fix an issue where my client might give me a few different options for works on the same site.

We had hoped that there was a common unique item to link them together but sadly this is not the case.

I have therefore taking the ID (Autonumber) of the Primary Record and then in a Parent ID (Number) field I am putting in the parent ID from a lookup to the Parent ID field.

I am struggling to understand how to do the relationships for this. I have created a 1 to many relationship and currently using option 2 for the join type. I am also using cascade up and cascade delete as options. I must admit, Im not too clear about the join types and when each is used.

A primary record may, but not always contain a child record. There might be as many as 2 child records. They may share a common PO number, this is not certain however as teh client raises Purchase Orders with no set pattern and we can have 1, 2 or 3 work instructions per Purchase Order Number.

So parent has children in the same table. Id like to be able to easily find records that 'belong together' and be able to group / treat them as '1 set' for each common work instruction.

Any help appreciated. Thanks.

 

[namliam]

What is 2 and what is 3 depends on how exactly did you drag the fields...

Key is to read the description in detail then you will understand it...

If you want to find groups... Are groups limited by their manager/Parent?? Usually not... I would probably make a "group" table, where I could identify who belongs in what group. Usually a person belongs to more than one group too...

 

[Chaz]

What is 2 and what is 3 depends on how exactly did you drag the fields...

Key is to read the description in detail then you will understand it...

If you want to find groups... Are groups limited by their manager/Parent?? Usually not... I would probably make a "group" table, where I could identify who belongs in what group. Usually a person belongs to more than one group too...

Lets say I have records 1 - 20. Lets call this a Job number, issued by AutoNumber.

Job 1 might be the parent with job 2 and 3 being children as these are for the same site but a variance in the work instruction.

99% of the time (I dont know that it will not be 100%), there will be a parent, that might own 0, 1 or 2 children.

I have not dragged anything yet (unless Im taking that too literally?). I have viewed the table and then expanded the 'cross box' but I dont see the children listed there.

In the form, I am using a continous form and generally these are shown in sequence and its easy to spot the groups. The issue is that the site names are then normally the key, except at times the A site and B site names are swapped around and makes it more difficult to find the grouping.

Job 1 might be an Install for Site Name (A) Billy James to Site Name (B) Joe's Pub. Job 2 might be a Decommision, directly related to Job 1 but the Site A and Site B Names might be swapped around and might not easily be found among 5000 work instructions (over time).

 

[Chaz]

Not sure if this will help?

 

[namliam]

If you have created a relation and set that to "2" then you have "dragged" the JobNumber to the parent, or?

 

[Chaz]

Sorry, the quality is not good on those - just trying to show some data / detail and the relationship. Just realised - do I have the relationship the wrong way around? If its the same table, does it matter which is tbl_master and tbl_master_1?

 

[namliam]

Well you want to allow Install's without Parent's, right??

That would mean in your screeny you would want "2", testing it is simple...
Just run the query and see if it is what you expect, if not... Take the other option.

 

[Chaz]

Well you want to allow Install's without Parent's, right??

That would mean in your screeny you would want "2", testing it is simple...
Just run the query and see if it is what you expect, if not... Take the other option.

No, there will always be a parent or main item. This may or may not have children items. By default the main activity is Install and the children then Decoms or Hot Cut Overs. It will be very rare to have a Decom or Hot Cut Over with an install activity, but it is possible.

The issue too is that at times we might get a PO with Install and also Decom on the same PO. In this case Ill treat this as 1 Job and this to be the Parent.

 

[Chaz]

Demo Database uploaded.

Ignore that some of the combobox fields do not have lookup tables. I copied this from the previous version of the database.

You will see Ive wanted to use TNE and Dependant TNE numbers but these are not unique and not always issued, so no joy there.

Needs to come back to grouping of some form. Perhaps it would be wise to split the table into smaller chunks and keep the work instructions as a separate table and then allocate that (and site details, and the rest) accordingly? A bit like the idea of grouping?

Argh, cant upload it as its newer format and it wont let me save as .mdb as there are 'newer features in use'.

 

[Chaz]

Sorted, after deleted data in the table not really needed for this ....

 

[namliam]

OK Now I am lost, things seem to be working properly... What is your problem/question?

 

[Chaz]

OK Now I am lost, things seem to be working properly... What is your problem/question?

If the parent / child relationship is working, should I not see them listed in the table view when I click on a parent (record 1 for example), should I not see the children listed (which in this case is record 2)?

 

[namliam]

If you click on tthe "+" on the left of it yes... And I do...
Even if you dont, I wouldnt care as your average user will not/ should not see this anyway.

 

[Chaz]

If you click on tthe "+" on the left of it yes... And I do...
Even if you dont, I wouldnt care as your average user will not/ should not see this anyway.

Thanks, mine doesnt for some reason but only reason I wanted to see that is know that the relationship works so I can use it later if I try and group things.

 

[namliam]

See attached...

 

[Chaz]

Maybe a bug, mine doesnt show the same and its the same database Im checking. See screeny.

 

[gemma-the-husky]

offhand it looks like you ARE showing the +'s in thetable, but already expanded.

As mailman says, you/your users wouldnt normally see tables anyway - so this feature is both a waste of time in general, and dangerous in particular - as you dont want to encourage the use of tables directly

one other point - you mentioned setting relational integrity to allow cascading deletes - i don't like this anyway, and be very cautious in a dbs with table self-joins

 

[Chaz]

offhand it looks like you ARE showing the +'s in thetable, but already expanded.

As mailman says, you/your users wouldnt normally see tables anyway - so this feature is both a waste of time in general, and dangerous in particular - as you dont want to encourage the use of tables directly

one other point - you mentioned setting relational integrity to allow cascading deletes - i don't like this anyway, and be very cautious in a dbs with table self-joins

Not sure I understand the first sentence. Mine does not seem to show the child data.

That said, I dont like the idea of table with self join (presuming this is me setting up a relationship in the same table). Ive removed the delete update, thanks.

Just thinking how I can split the table so that I dont need to do join on the same table. I know I can extract the site information into a new table and link with the PK, just not certain how to split the table to allow for child / parent to be separated. Maybe I need a single table that just issues and handles the IDs but that doesnt make 100% sense cos Ill still need the details of the jobs somewhere. Argh.

 

[Chaz]

Is it even possible for a parent child relationship to exist without referring to the same table on the basis that its 'per record' and its not for example stock that belongs to a job or something, its a job that belongs to another job.

 

[namliam]

It is perfectly normal to have a self join... This happens for many reasons... i.e. Employee > Manager

Self joins are nothing more or less than any other join...