Help linking to similar records from same table when creating new record - Call Log

[kintap]

Hi,

I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.



I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).

For example.

Caller: John Smith (555) 555-5555 (this is the new record)

in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.


Any help would be great. This has been a year of trying to figure it out, and I am having no luck.


Kyle

[orange]

Tell us about your call log. What fields does it have?
How are the fields populated?

[June7]

First off, each caller needs a unique identifier (autonumber field will serve). Will need a table of callers, each record will be unique. This will require some effort to extract unique callers from the call log and then identify variable spellings:

Is John Smith (555) 555-5555 = John J. Smith (555) 555-5555 = John Smyth (555) 555-5555?

Also, what if John Smith made several calls from different phone numbers?

Then have to populate the call log records with the correct callerID, taking into consideration the variable spellings and multiple phone numbers.

This is a tedious and time-consuming task and probably won't catch them all.

And if you want users to make these decisions about associating records as they are responding to calls, sure, can have subform or listbox display all records that match last name Smith. But the picking records and updating with callerID "on-the-fly" could get complicated.

Maybe should forget about the past and just initiate structure with new calls?

[kintap]

I apologize I didn't respond sooner. I ended up being on serious medical leave till recently.

I have attached a redacted version of the call log we are using. It is 'janky' because I had to build this out of necessity not out of expertise.

I am hoping that it has not been too long and one of you will be able to help me out.

I would love to overhaul and start over but because of the service we provide I need access to this old data.

The database is split Front/Back. If you keep both files in the same folder they should link properly.

Username is login and password is: password when you open the database.

Kyle

[June7]

Have the db. It seems to be normalized. There is a Contacts table and the Calls table saves only ContactID. If you want to know if caller already in db at the time of logging in a call, various methods to aid in that. For a start try a combobox that lists the Contacts (Lastname, Firstname, Phone).

[kintap]

Hi June7,

I am not sure I follow. I am trying to find a way to link a record in the Contacts table to another record in the Contacts table. The Calls table is only for notes related to the selected record. I am not sure how using a combobox will let me link these.

Kyle

[June7]

Code can search the table to determine if multiple records for the same key but there is no 'link' between records of same table. Links are between fields to join related tables/queries in a query. A combobox would allow listing all contacts so if contact is already in table, user can see that.

[kintap]

Thank you June7 for trying to help me out. I think the problem i have is that in the Contact Table, which we create a new record for every call (unless a person is actively calling us back in the same day which we attach as a note in the Calls Table). So if a person calls a month later we create a new record to capture that work and unique outcome.

It sounds like there is no way to link records within a single Table. I have an image of the database that i mocked up a subform on to show what I was visioning in my head. My hopes were to have a way that once we 'attached' or 'linked' records in the Contact Table they would show in this subform for easy access for our staff to see previous call outcomes.

I am sure that I am not explaining this clearly because it makes my head spin trying to figure out how to verbalize what I am thinking.

Right now if we want to find this past contact we use our search then have to scroll through the list to find them and this get tedious, so I was hoping to find a way to do this search once then have a checkbox or something that would say 'these records are related'. Is there a way to do this, perhaps with another table??

[June7]

Many ways to accomplish.

But might require changes to data structure.

I picture a Contacts table with unique record for each contact. Then the call log would have related records for the call sessions with the contact.

Options for data entry:

1. main form bound to Contacts and subform bound to CallLog - search boxes on main form to locate existing contact, if not found then move to new record row and create contact record then the call record in the subform

2. a single form bound to CallLog and a combobox to select contact. If contact not in the list then popup form to enter new contact record