3-to-1 Primary keys

[Andrew]

A question to all the smart guys and girls out there!!
I'm trying to create a correspondance database for the office in which I work.
We get meeting requests, invitations for functions, and general
correspondance.
Currently, we have three unconnected databases.
Meetings
Invitations
Corresp

What I would like to do is assign a unique ID to each item of
correspondance, so I can build a query to search by ID.

Ideally, I'd love to have:
cXXX for correspondance,
mXXX for meeting requests and,
iXXX for invitations.
(where XXX is any unique number)

Is there any way to do this, or am I being a little too ambitious?

Thanks,

ANDREW.

 

[Jeff Boyce]

Andrew

You mention wanting to assign a unique ID "to each item of correspondence",
but then describe non-correspondence items (meetings, invitations). Are you
saying all three of these categories are "correspondence" (one of which is
named "correspondence")?

Where are you planning to store the items?

How are you planning to affix the "unique ID"?

I'd advise against putting more than one fact in a field -- your "c123,
m456, i789" idea mixes category and ID number in one field. Instead, use
two fields! One field for category, one field for ID.

 

[Andrew]

Hi Jeff,

Sorry, I didn't explain myself properly.
The Table labeled "Correspondence" is short for "general correspondence".
Letters "Dear Peter, how's the wife", etc.
And yes, I'm defining Meeting Requests & Invitations as correspondence.
Perhaps not the best use of words ever, but that's the terminology we use in
the office
Also, all of this correspondance is physical snail mail sent to our office.

I suspected that adding a letter to the primary key would be a little hard
to do, so never mind about that. What I really need, tho, is for each item of
mail that comes in to be given a unique number.

I've messed around with table relationships, but I really don't know how to
proceed...

Any help you could give would be MOST helpful.

Thanks heaps,

A very appreciative,
ANDREW.

 

[Jeff Boyce]

Andrew

Please re-read my first response. Putting more than one fact
(correspondence type, ID#) into one field is not good design. Whether or
not it might be difficult is besides the point. To handle two facts, use
two fields.

If you create a tblCorrespondence, to keep information about your physical
documents, what information will you keep, beside an ID and a type ("meeting
request", "Invitation", ...)?

I don't understand what you mean "messed around with table relationships" --
so far, I only see two tables, one for Correspondence, one for
CorrespondenceType.

 

[Andrew]

Jeff,

I'm really stupid.
What I should have done from the start is completely explain everything.

So:
What we currently have is three tables.
tblCorresp
tblInvite
tblMeeting

In each table, every detail about a given piece of mail is entered into the
relevant table. General Correspondance is entered into tblCorresp,
Invitations are entered into tblInvite, and Meeting Requests are entered into
tblMeeting.
Many fields in each table are required. Some examples are: The Sender's
surname, firstname, title, date the letter was recieved, date our responce
was sent, name of the function (in the case of an Invite), place of meeting
(in the case of a Meeting) etc...

Thus, every table has many different fields.

What I am trying to establish is whether it is possible to give each piece
of mail can be allocated a unique sequential ID#.

Example: If I enter a piece of General correspondence it is allocated ID#: 041
Then, if I enter an invitation after that, it is allocated ID#: 042

I have spent some time trying to get an index table working, where
information is duplicated from the three tables into tblIndex, where the
unique ID is generated. But it really doesn't work the way I had hoped.

Once again, am I being a little too ambitious?

Thanks once again for your time.
(I don't suppose you've got a PayPal account I could donate into? I'm
starting to feel a little guilty)


ANDREW.

 

[Jeff Boyce]

Andrew

You'll find most of the folks who post (and respond) here do so voluntarily.
And most probably have "day jobs"!

If I were setting up something like what I'm imagining you are doing, I'd
first look for every possible "matching" field. If every item of
correspondence (small "c") has a "From" and a "To", and a "DateSent", I'd
look to pull all those together into one table.

You want a unique ID across all items, so create a table that uses an
Autonumber (or some numbering routine of your own creation) as a primary
key. Every item gets recorded there first, with any/all fields that you've
determined are common to all types.

Then, in each of your "specialty" tables, you have fields that are unique to
that type of correspondence (small "c"), and a LongInt primary key (if
you've used Autonumber in the main table). This is related one-to-one to
the main table. That is, each item in the main table can belong in one and
only one row in one and only one categorizing table (e.g., Invitation,
Meeting, ...)

It might be a little complex to get the transition down between entering
data in your form that then goes to the main table, and that data that goes
to the "category" table, but it is doable.

 

[John Vinson]

On Mon, 10 Jan 2005 17:09:03 -0800, Andrew

PMFJI... but thought I'd just add a few thoughts.

Jeff,

I'm really stupid.

You're not knowledgable about the complex and arcane field of
relational design - that's a different statement than "stupid" which

What I should have done from the start is completely explain everything.

So:
What we currently have is three tables.
tblCorresp
tblInvite
tblMeeting

In each table, every detail about a given piece of mail is entered into the
relevant table. General Correspondance is entered into tblCorresp,
Invitations are entered into tblInvite, and Meeting Requests are entered into
tblMeeting.
Many fields in each table are required. Some examples are: The Sender's
surname, firstname, title, date the letter was recieved, date our responce
was sent, name of the function (in the case of an Invite), place of meeting
(in the case of a Meeting) etc...

Thus, every table has many different fields.

Well... many different REDUNDANT fields, it appears. Unless each
sender appears only once, or very very rarely more than once, you
should consider having a table of Correspondants with fields:

CorrespondantID
LastName
FirstName
Title

What I am trying to establish is whether it is possible to give each piece
of mail can be allocated a unique sequential ID#.

Example: If I enter a piece of General correspondence it is allocated ID#: 041
Then, if I enter an invitation after that, it is allocated ID#: 042

Only by (as suggested) using a single table for all correspondance.
This is an example of a rather advanced technique called
"Subclassing". What you might want to consider is to have a master
table Communications:

CommunicationID
CommunicationType <e.g. General, Invitation, MeetingInvite, Reply,...>
CorrespondantID <link to Correspondants; don't include any fields from
there>
CorrespondanceDate <date recieved, or sent in the case of replies;
this will allow for multiple replies>
InRe <the CommunicationID of related messages in a group, e.g. a Reply
might have the ID of the message being replied to; null for standalone
messages>

This table would be linked one-to-one to tables for Invitations or
Meeting Requests which would also have CommunicationID as the Primary
Key, together with fields pertinant to those special cases of
correspondance.

I have spent some time trying to get an index table working, where
information is duplicated from the three tables into tblIndex, where the
unique ID is generated. But it really doesn't work the way I had hoped.

Don't duplicate ANYTHING except the ID, and fill that in using
Subforms.

Once again, am I being a little too ambitious?

Nope. You're just learning, like the rest of us!

Thanks once again for your time.
(I don't suppose you've got a PayPal account I could donate into? I'm
starting to feel a little guilty)

ANDREW.

John W. Vinson[MVP]

 

[Jeff Boyce]

Andrew

John has, once again, done a masterful job of clarifying!

And I agree with his comment -- not knowing (but willing to learn) is in a
whole different class than knowing (but choosing not to) <g>!

As for papal, there are any number of folks out there who could use the
financial help -- pick one that means something to you and help them, if you
feel so-moved...

Regards

Jeff Boyce
<Access MVP>