Link Records in the Same Table

[threepwoodjr]

Hello,



In my database I want to link tickets with other tickets. I thought I could use a traditional linking table but I'm stuck on the problem that the primary key can exist in both foreign key fields.

tblTicket
--tblTicket_ID (pk)
...

linkTicket
--linkTicket_ID (pk)
--linkTicket_TicketID1 (fk)
--linkTicket_TicketID2 (fk)

How do I display all the related tickets for a ticket if the ID for that ticket can be in two fields? I found a lot of info regarding one-to-many or many-to-many self-joins. Is that the way to go? It seems extremely difficult and beyond my skill level.

I appreciate your help.

[RuralGuy]

Maybe this link will help: http://allenbrowne.com/ser-06.html

[threepwoodjr]

Thanks for the example. If I understand it correctly, it doesn't help with my situation. This example is configured so a child object is related to its two parent objects continually upward. I don't understand how that would allow me to link a ticket directly with other tickets. Instead, with this example, I could only link a ticket with two "master" tickets. Then those two master tickets would link with more master tickets and so on.

That doesn't work if I'm looking at ticket 1 and I just want to add more tickets to it because if ticket 1 is already linked with two tickets (2 and 3) and I want to link three more tickets to ticket 1, I have to go to ticket 2 and link it with tickets 4 and 5 and link ticket 3 with ticket 6 so that tickets 4, 5 and 6 are now indirectly linked with ticket 1. That's doesn't seem right.

It works for pedigree but not for tickets because I need the ability to link a child directly with its mother and father and link it directly to grandpa and grandma without going through the parents.


I keep going back to the linked table. There must be some way. If I'm looking at ticket 1 and I link it with ticket 2, ticket 1 will go in field 1 and ticket 2 will go in field 2. However, now I'm looking at ticket 3 and I want to link it to ticket 1. It will place ticket 3 in field 1 and ticket 1 in field 2. That makes it impossible to filter for ticket 1 when it exists in both field 1 and 2.

The only solution I can think of is to create some kind of error check that checks to see if the ticket you are trying to link to has a link already. It would then tell you to go back to ticket 1 and link it to ticket 3. That way, it's guaranteed the ticket your are filtering for will always be in field 1. Unless there's code that would detect the existing link and then flip the fields for the new entry.

[threepwoodjr]

I finally found the solution to my problem. I created a union query. Now I can link tickets with other tickets and hardware with other hardware and it doesn't matter which field the foreign key is in. I should probably learn SQL.

SELECT tblTicket.tblTicket_ID, tblTicket.tblTicket_Title, tblTicket.tblTicket_Status
FROM linkTicket INNER JOIN tblTicket ON linkTicket.linkTicket_TicketID2 = tblTicket.tblTicket_ID
WHERE (((linkTicket.linkTicket_TicketID1)=[Forms]![frmViewTicket]![txtTicketID]));
UNION ALL SELECT tblTicket.tblTicket_ID, tblTicket.tblTicket_Title, tblTicket.tblTicket_Status
FROM linkTicket INNER JOIN tblTicket ON linkTicket.linkTicket_TicketID1 = tblTicket.tblTicket_ID
WHERE (((linkTicket.linkTicket_TicketID2)=[Forms]![frmViewTicket]![txtTicketID]))
ORDER BY tblTicket.tblTicket_Status;

[RuralGuy]

Thanks for posting back with your success.