Capturing cc's in a correspondence table

[Guest]

Hi, I hope someone can help with this....

We have a table of customer contacts, a table of our company's folk, and a
table of resellers. I am trying to put together a table which pulls this
information together to capture correspondence between the groups.

The correspondence table has a date for each transaction, a type (EMAIL,
PHONE, etc), notes about what was said, a field for what customer is being
considered, and 3 potential FROM fields which look up their data from the
various tables (FROM US, FROM A RESELLER, FROM THE CUSTOMER) it also has 3
potential to fields (TO US, TO THE RESELLER, TO THE CUSTOMER) also linked to
the tables.

My dilemma is that we often cc correspondence to several resellers. It's
awkward to put them all in the field TO THE RESELLER separated by commas, in
fact, I can't get a form to allow me to select multiple resellers. Clearly I
am missing something here.

Also. I'd like to just have a single field called FROM that includes all the
potential people the email might be from (resellers+customers+us) and a
single field called TO (same idea).

I wait eagerly for any clarity you good folks can provide.

Thank you
Diane Meriwether

 

[Ed Warren]

Your problem is at the point where you have "3 potential from fields".
They should be pulled out into a 'related' table so you can have 0 to n FROM
values. (a 1:m relationships)

Below is just enough to get pointed in the right direction, you will of
course need more tables and relationships for a complete application.

Each 'correspondence' has some stuff that relates to it specifically.
Then it has a 'list of correspondants'
Some are of a type from
some of a type to
some of a type cc

So you could have a table: Correspondence
with fields like
Correspondence ID (key) Correspondence_Notes Correspondence_Date
Correspondence_Subject ..... etc. (stuff uniquely about the correspondence)

Then a table:

Correspondence_Correspondants
With fields like
CCID (Key) CorrespondenceID (Foreign Key) Correspondant_Type (To, From,
CC, BCC, etc) CorrespondantID (Foreign Key from a list of possible
people.. vendors, resellers, customers, etc)

Now you can build a main form with the Correspondence stuff in it and a
subform with the Correspondant stuff in it and have as many to's, from's,
cc's, bcc's as you want.

Hope this helps

Ed Warren

 

[Guest]

OH now I am starting to understand! Thanks! It's starting to make sense.
Here's a picture of the relationships
http://www.thenwhen.com/relationships.gif
But I'm still getting stuck with the form part...

What I'd like to be able to do in the subform is for the user to start
typing an email address and then have access complete the deal - storing the
correct CorrespondantID. I wa able to drop in 3 unbound combo boxes and use
the lookup feature to populate the CorrespondantID field using the wizard.
Not only is this klunky but I can only get it to work as a single form, not a
datasheet.

One solution would seem to be to base the combo box on a query that combines
the email address from all three tables but I can't figure out how to do
that. Perhaps there is a even better solution?

Thank you again and in advance for your help!

I am one grateful newbie!

 

[Ed Warren]

Looking at your relationships I could not see what the key fields for the
relationships are on the various tables
It looks like the ID from customers, brgPersonnel, and resellers all tie to
the correspondence_ID.
If so this will not work.

I think you want something like:

Table: People
PersonID LastName FirstName Postition Type (Reseller, BRG Personnel,
Customer) DateAdded

Table:
ResellerRegion
RegionID
... region stuff

ResellersIn????
ResellersInID
RegionID
... other stuff

ResellerPeople
ResellerInID PersonID ... anything pertaining to that person-reseller
relationship

Correspondence
Correspondence_ID
Correspondence_Date
..... other stuff like you have it.

CCorrespondants
CorrespondantID (PK)
CorrespondenceID (FK)
PersonID(FK)
CCTypeID(FK)

CCType (used as a lookup table for the cctype in ccorrespondants)
CCTypeID(PK)
CCText (something that has meaning to a human)

then to set up relationships:

Each piece of correspondence has many correspondants

Correspondence 1-->M CCorrespondants (link on CorrespondenceID)


Each Person can be none to many CCorespondants
People 1 -->M CCorrespondants (link on PersonID)

Some People can be Resellers (but not all people are resellers and a person
is either a reseller or not a reseller, it is not clear if the same person
can be a reseller for many reseller offices)

Here is a rare time to use a 1:1 relationship or you can just add a combobox
dropdown to select which reseller they represent, if they can represent more
than one reseller then you will need to use the table approach.

People 1 --> 1(m) PeopleReseller (link on PeopleID)
ResellersIn???? 1-->M PeopleReseller (link on ResellerInID)
ResellerRegion 1-->M ResellersIn??? (Link on ResellerID)

Forms required

ResellerRegion (based on ResellerRegion table) (used for maintenance of
reseller regions)
People (based on People table) (used for maintenance of people). Will use a
subform with a combobox to select the reseller, or a field in the table with
a comobox (using the reseller table as a lookup source)
CCType (based on the CCType table and used to maintain the CCTypes)
CCorrespondants (based on the CCorrespondants table, used as a subform in
the correspondence form) (linked on the correspondenceID field)
I would normally hide the CorrespondenceID textbox by setting it's
visible property to false, that way it is there to use as a link but the
user never sees it).
Use a combobox to enter the person (lookup source Person table) and a
combobox to enter the CCType (lookup source CCTYPE)
Correspondence (based on the Correspondence table, use the CCorrespondants
form as a subform (linked on the CorrespondenceID field)

Hope this is more help than hinderance.

Ed Warren.