Related records in same table

[MJH]

Hello

I am trying to relate multiply records in the same table.

I have a table called Jobs with each record having a unique serial id as well as a job number.   Often a new jobs comes in that is a revision of a previous job (or jobs) that has been billed and closed.  My database users would like to link the new jobs to the old ones.   I have created a second instance called Jobs_related and also tried putting a join table in between, but nothing I do to join them works.  

Thanks

[webko]

Don't do that - create a Master_Jobs table which then links to the Jobs that are relevant.

[shweta]

Create a new table to store all related jobs for a job.

That table will have 2 main field the Job id and related job id.

ex. if job with 4 is related to jobs with id 1 & 3 respectively. then this new table will have 2 records:

Job ID           Related Job ID

4

Create a new table to store all related jobs for a job.

That table will have 2 main field the Job id and related job id.

ex. if job with 4 is related to jobs with id 1 & 3 respectively. then this new table will have 2 records:

Job ID           Related Job ID

4                      1

4                      3

 

Then create a relationship of Job table with this new table based on Job ID. On jobs layout you can place a portal of this new table. 

Hope this helps.

[Agnes Riley]

You do not need a new table for related jobs. You just need to store the ID of the related job in the same table. Read about normalization. Whatever you can store in one place you do not store in a different place.


Sent from my iPhone using Tapatalk