Re: Query Me This -- two references to another tables field

We would only be interested in how they _initially_ were referred,
regardless of what their first contact with the organization was (so there
would, in fact, only be one possible initial referral source), but we would
want to know what their first contact with the organization was.

I would say that about 50% of the contacts are NOT referred by someone else
(they find the organization on their own), or, they are existing contacts
from a flat-file DB that is being ported to Access, which did not collect
this information (some of which are 6 - 7 years old) -- when the DB is ready
to go, we will attempt to collect this original referral information for as
many of our existing contacts as possible, but you know how people are about
responding to a survey at times, so we may very well not get this
information. What is your opinion on that percentage of total contacts (400
of about around 800 at this point, and growing quickly) in terms of blanks
if all of this is tracked in the basic contact table?

Also, we want to gather a number of different pieces of information about
the referral, even if self-referred --
1. Type of first contact with the organization
2. Who referred them, if it is currently a contact in the DB
3. How they found us, if there was no person referring them
4. Date of first contact with organization
5. Notes about the first contact

I actually started out with all this in the tblContact, but removed it and
began working with a separate table at the suggestion of someone in this NG.
Since I am not yet truly adept at append, join, etc., I have erred
consistently on the side of more rather than less tables.

Thanks again for your help,
Carol


"Steve Schapel" <schapel@xxxxxxxxxxx> wrote in message
news:eKdBQ9ceFHA.4016@xxxxxxxxxxxxxxxxxxxxxxx
> Carol,
>
> I agree that it is often a moot point whether to move to a 1:1
> 'sub-typing' table. One of the things to consider is the number of
> "sub-types" involved, and the proportion of records which would
> otherwise end up with blanks in a significant number of fields.
> However, the total number of fields in a table, and concerns about
> "cluttering up" the table, are not really valid considerations. On the
> basis of what I know so far about your database, I think I would
> probably still try to keep all the Contacts information in the one
> table. But, as I said, a moot point. When you say they are "referred
> for different things", do you mean that the same contact can be referred
> for more than one thing? And if so, can the referral source for each
> "thing" be different? If so, it may be correct to have a separate table
> for this referral data,... but this would be a one-to-many relationship,
> and would be different from the table structure as you have described it.
>
> --
> Steve Schapel, Microsoft Access MVP


.

Relevant Pages