Question Related calls

[rockyjr]

Hi everyone,

I'm trying to get some ideas on how to do this.

I have a database where I keep call the calls I get (a bit like a call center). What I would like to do is add an option to relate a current call to another call. If I open that call, I could have a little icon showing that this call has related calls.

That should be easy.... the hard part is:
For example -
I have ID 1, 2, and 3
1) I open ID 1 and add a relationship to 2, I should be able to see that 1 is related to 2 and if I go into ID 2, I will now see that 1 is related to 2.

2) if I go into ID 3, and add a relationship to 2.....when I open ID 1, the relationship should now be between 2 and 3. The logic is that if ID 1 and 2 are related, and that 2 and 3 are related, they should all related.

Any ideas would be great!!

Thanks,

Luc

 

[Kryst51]

What about having a Issue number or something, a one to many relationship between a "issue number" (call it what you want) and many calls, it would involve another table, but it would achieve what you want. Then those calls would all be related to one another through the "issue number"

 

[rockyjr]

I guess what I dont understand is that, when I open ID 2, how do I check if this calls has related calls?

Lets say the separate table has
ID (PK) CALLSID RELATEDID
##, 1, 2

If I open ID 1... I see that it is related to 2.

But, if I open ID 2, I dont see the relationship. What I did is, added a subform, and link the master and child fields.

Should I be doing it another way?

Luc

 

[georgedwilkinson]

If this is your requirement, you should be using a M:M relationship (AKA a junction table or an associative table). That way, you can have an unlimited number of calls "related" to each other.

 

[rockyjr]

I'm a bit confused.... sorry....

I made a little example of what I'm trying to do.

As you can see in the database, ID 1 and 2 are related to 4.

When I check ID 4, it should be show 1 and 2 are related to 4....

 

[Kryst51]

in your ex, is 1 also related to 2 and 4, and is 2 also related to 1 and 4?

 

[rockyjr]

Well, logically, if 1 is related to 4 and 2 is related to 4... yes, 1, 2, and 4 should all related.

Basically, if 10 IDs are related to ID number 44 .... and I relate 44 to lets say 77. 77, 44, and the 10 IDs should all relate to one another.

I hope I'm making myself clear....

 

[vbaInet]

Well, logically, if 1 is related to 4 and 2 is related to 4... yes, 1, 2, and 4 should all related.

Basically, if 10 IDs are related to ID number 44 .... and I relate 44 to lets say 77. 77, 44, and the 10 IDs should all relate to one another.

I hope I'm making myself clear....

Wouldn't it just be easier to have a Caller_Relationships table which will have an ID and a field from the Callers table which is the FK? Then just display all the links from that table based on which caller you selected?

Caller_Relations table
--------------------
CallRelationID
CallerID_FK linked to CallerID in Callers table

Just a suggestion

 

[Kryst51]

Hey, I am getting ready to leave work, I'll post an example of my idea tomorrow if you like. The idea is that there is and "Issue table" and the pk from that table is an FK in you calls table. This is a one to many relationship. Then you set up a form with your call information, and then have a query that looks at the issue associated with the call that is currently being displayed on the form, this query will be used to populate a list box with all the calls related to that issue.

There would be code to have that list box requery everytime a different call was displayed on the form.

 

[rockyjr]

Kryst51, I think you're getting somewhere..... ya, I would love to see your example. This sounds like what I'm trying to do.

 

[Kryst51]

Kryst51, I think you're getting somewhere..... ya, I would love to see your example. This sounds like what I'm trying to do.

Great, I will finish it up during lunch today, so noonish by Houston time.

 

[rockyjr]

ok, thank you.

 

[Kryst51]

Here's the idea I came up with, it's not perfect though. I don't have time to play around with a delete call button when you select an item in the list box, but there is one when you drill into the item by double clicking.

I am sure there are tons of other things that need to be added to this, but at least this shows the concept, and I hope it helps.

 

[rockyjr]

Thank you!!!

I will take a look at it. Thanks for the time buddy!!

LucS

 

[Kryst51]

Thank you!!!

I will take a look at it. Thanks for the time buddy!!

LucS

Your welcome, Please feel free to post back with questions.