By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,345 Members | 2,404 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,345 IT Pros & Developers. It's quick & easy.

Tracking communication threads in Access

P: n/a
Hello All! I have a rather abstract question for some genius out
there to answer. I want to integrate communication tracking, for
customer relations, into an existing Access DB. What I was going for
is this...

tblCommunications trxCommunicationThreads
----------------- -----------------------
CommunicationID (PK) CommID1 (FK1)(PK)
ContactID (FK from contact table) CommID2 (FK2)(PK)
Content (text)
Date

Both CommID(s) in trxCommunicationThreads will reference a record in
tblCommunications. The end goal is to be able to store, track, and
display entire communication threads and the contacts that the
communications are from, kind of what you would see right here in
Google except not as robust. When a contact posts a follow-up to
communication1, the follow-up communication, communication2, will be
added to tblCommunications as a new record and a relationship between
communications 1 and 2 will be recorded in trxCommunicationThreads.

The problem is querying these tables in such a way where it will
display all related threads. The first layer is easy of course (i.e.
the first follow-up to an original communication), but it's the
follow-ups to the follow-ups that I can't quite figure out because it
would require some sort of recursive lookup. Is there a way to write
a recursive query? I can crunch through the trx table and create
thread relationships between communications with code, of course, but
it just seems like there should be a more direct way to do this.

I hope I am not being too elusive here. Please let me know if you
need further detail; although, it's hard to put this concept into
writing using only text.

Thanks to anyone that has any ideas. I have some thoughts of my own
but I thought I'd see what the rest of the community has to say about
this sort of thing before I got heavy into building it.

Bryan
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi,

I don't think you can make a single query who will return the full thread of
messages.
Instead, I'll suggest you another approach:

Create a temporary table and populate it with all the messages.
This can be done with VBA (creating the temp table, and populate it with the
communications messages, and deleting the table when is no necessary to
display the thread). The table can be created into a external MDB, created
also "on the fly" or on a "per session" basis (to not bloat your main
database with deleted tables).

Another approach, which will allow you to get all messages from a
communication with a query will involve a different data structure.

You'll need to have a threads table (you'll add there a record for each new
communication thread started). Then for each message it will have two
foreign keys: Thread ID and ParentMessageID. Also you can assign message
order ID for each message in the thread (actually this can be a timestamp
field), and with a single query you'll be able to retrieve all messages from
a given thread.

If you need any more help on this, you can contact me back.

Regards,
Bogdan Zamfir
____________________________

Independent Consultant
"Bryan Christopher" <bh*****@medlink.com> wrote in message
news:d7*************************@posting.google.co m...
Hello All! I have a rather abstract question for some genius out
there to answer. I want to integrate communication tracking, for
customer relations, into an existing Access DB. What I was going for
is this...

tblCommunications trxCommunicationThreads
----------------- -----------------------
CommunicationID (PK) CommID1 (FK1)(PK)
ContactID (FK from contact table) CommID2 (FK2)(PK)
Content (text)
Date

Both CommID(s) in trxCommunicationThreads will reference a record in
tblCommunications. The end goal is to be able to store, track, and
display entire communication threads and the contacts that the
communications are from, kind of what you would see right here in
Google except not as robust. When a contact posts a follow-up to
communication1, the follow-up communication, communication2, will be
added to tblCommunications as a new record and a relationship between
communications 1 and 2 will be recorded in trxCommunicationThreads.

The problem is querying these tables in such a way where it will
display all related threads. The first layer is easy of course (i.e.
the first follow-up to an original communication), but it's the
follow-ups to the follow-ups that I can't quite figure out because it
would require some sort of recursive lookup. Is there a way to write
a recursive query? I can crunch through the trx table and create
thread relationships between communications with code, of course, but
it just seems like there should be a more direct way to do this.

I hope I am not being too elusive here. Please let me know if you
need further detail; although, it's hard to put this concept into
writing using only text.

Thanks to anyone that has any ideas. I have some thoughts of my own
but I thought I'd see what the rest of the community has to say about
this sort of thing before I got heavy into building it.

Bryan

Nov 12 '05 #2

P: n/a
Bryan
Using 'nested set theory', (a googles of 'Joe Celko' will explain the
model), I've emailed you a small mdb that might be of help

bh*****@medlink.com (Bryan Christopher) wrote in message news:<d7*************************@posting.google.c om>...
Hello All! I have a rather abstract question for some genius out
there to answer. I want to integrate communication tracking, for
customer relations, into an existing Access DB. What I was going for
is this...

tblCommunications trxCommunicationThreads
----------------- -----------------------
CommunicationID (PK) CommID1 (FK1)(PK)
ContactID (FK from contact table) CommID2 (FK2)(PK)
Content (text)
Date

Both CommID(s) in trxCommunicationThreads will reference a record in
tblCommunications. The end goal is to be able to store, track, and
display entire communication threads and the contacts that the
communications are from, kind of what you would see right here in
Google except not as robust. When a contact posts a follow-up to
communication1, the follow-up communication, communication2, will be
added to tblCommunications as a new record and a relationship between
communications 1 and 2 will be recorded in trxCommunicationThreads.

The problem is querying these tables in such a way where it will
display all related threads. The first layer is easy of course (i.e.
the first follow-up to an original communication), but it's the
follow-ups to the follow-ups that I can't quite figure out because it
would require some sort of recursive lookup. Is there a way to write
a recursive query? I can crunch through the trx table and create
thread relationships between communications with code, of course, but
it just seems like there should be a more direct way to do this.

I hope I am not being too elusive here. Please let me know if you
need further detail; although, it's hard to put this concept into
writing using only text.

Thanks to anyone that has any ideas. I have some thoughts of my own
but I thought I'd see what the rest of the community has to say about
this sort of thing before I got heavy into building it.

Bryan

Nov 12 '05 #3

P: n/a
Thank you, everyone, for your input. I have a great idea of what I
will do now. If anyone needs anything, ya'll know how to contact me.

Thanks again,

Bryan

le*********@natpro.com (Roger) wrote in message news:<8c**************************@posting.google. com>...
Bryan
Using 'nested set theory', (a googles of 'Joe Celko' will explain the
model), I've emailed you a small mdb that might be of help

bh*****@medlink.com (Bryan Christopher) wrote in message news:<d7*************************@posting.google.c om>...
Hello All! I have a rather abstract question for some genius out
there to answer. I want to integrate communication tracking, for
customer relations, into an existing Access DB. What I was going for
is this...

tblCommunications trxCommunicationThreads
----------------- -----------------------
CommunicationID (PK) CommID1 (FK1)(PK)
ContactID (FK from contact table) CommID2 (FK2)(PK)
Content (text)
Date

Both CommID(s) in trxCommunicationThreads will reference a record in
tblCommunications. The end goal is to be able to store, track, and
display entire communication threads and the contacts that the
communications are from, kind of what you would see right here in
Google except not as robust. When a contact posts a follow-up to
communication1, the follow-up communication, communication2, will be
added to tblCommunications as a new record and a relationship between
communications 1 and 2 will be recorded in trxCommunicationThreads.

The problem is querying these tables in such a way where it will
display all related threads. The first layer is easy of course (i.e.
the first follow-up to an original communication), but it's the
follow-ups to the follow-ups that I can't quite figure out because it
would require some sort of recursive lookup. Is there a way to write
a recursive query? I can crunch through the trx table and create
thread relationships between communications with code, of course, but
it just seems like there should be a more direct way to do this.

I hope I am not being too elusive here. Please let me know if you
need further detail; although, it's hard to put this concept into
writing using only text.

Thanks to anyone that has any ideas. I have some thoughts of my own
but I thought I'd see what the rest of the community has to say about
this sort of thing before I got heavy into building it.

Bryan

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.