see comments in-line below...

"Lee Ann" wrote in message
news
Fred & Jeff:

Thanks for the advice and I'll try my best to lay out what I have without
getting into too much detail. I'm not trying to solicit extra duty from
anyone, just trying to find a way to provide all the information to get
clearer answers. I have posted a couple of questions on this in recent
weeks
and each answer has led to more questions to the point that I doubt my
design
is correct.

I have a TblIncident:

TblIncident
IncidentID (PK)
IncidentNumber (this is actually a unique number which should never be
repeated - makes me believe it should be a PK)

If you are assured that IncidentNumber will always be unique, it would serve
fine as a PK. Save yourself the extra field.

IncidentDate
IncidentTime
SubjectID (FK)
LocationID (FK)
CaseworkerID (FK)
CallerName
CallerContactNumber

**Note: Each incident will include one or more subjects and one location.

You'll want to re-think your table structure here. If (one) incident could
have (many) subjects, you need a table for incidents, a table for subjects,
and a table that holds valid pairs. Thus, your [SubjectID(FK)] field is not
appropriate in your tblIncident. That third table might look something
like:

trelIncidentSubject
IncidentID (or your IncidentNumber)
SubjectID (a FK from your tblSubject)

If each incident can have no more than one location, your [LocationID] field
is fine.

(same logic applies for [CaseworkerID])

(... and why use [SubjectID] but not [CallerID]? What about the notion of
having a tblPerson, then using the PersonID as needed to indicate who the
Caseworker is, the Caller is, and (in the appropriate table), the Subject?)



TblLocation
LocationID(PK)
LocationName
LocationStreetAddress
LocationCity
LocationState
LocationContactNumber

If the location is a street corner, how are you defining
[LocationContactNumber]?


**Note: One location will be used per incident. We have particular
locations whose information will already be loaded into this table (this
will
not be information the user will be adding).

TblSubject
SubjectID (PK)
SubjectLastName
SubjectFirstName
SubjectMiddle
Additional identifying information will follow

**Note: Each subject entered will be involved in one incident at one
location. There may be more subjects involved in the same one incident.
It's conceivable that they will be involved in another incident at another
location on a different (later) date.

OK, it sounds like an incident has a date. Your table has an IncidentDate
field and an IncidentTime field. Save yourself the work and let Access do
more for you by using a date/time field, and storing IncidentDateTime in a
single field (or not, as your situation requires). Just be aware that you
can use Access date/time-related functions to extract the date-only or the
time-only portion of that combined field, as needed.


TblCaseWorker
CaseWorkerID (PK)
CaseWorkerLastName
CaseWorkerFirstName
etc.

**Note: One case worker will be involved in one incident at a time.

Are you saying that a case worker can only work on one case at a time? How
will you know if a case worker is "busy" when a new case comes up?


I was also contemplating another table where further information on the
subject was entered that was specific to only them. I'm not sure why I
feel
I need to do this and was looking at the number of fields that would be in
the one table (23) and felt that would be to excessive.

Are you saying that every subject would have an entry for every one of those
23 fields? Or are you saying that each subject MAY have zero, one or ... up
to 23 attributes? If the latter, think one-to-many again and set up a table
to hold the attributes (today, numbering 23, tomorrow, maybe 100?!), and a
third table to hold the valid pairs (Subject X Attribute). If a subject X
attribute combination is NOT in that third table, the subject doesn't have
that attribute!

(just thought of this ... if a subject can be involved in more than one
incident over time, are the attributes fixed for all time, or could they be
different for subject1 X incident2 that for subject1 X incident1? If the
attributes potentially change, then you need to connect the incident X
subject X attribute -- a different 'third' table)


I think where I'm running into the problems is not being really sure where
the FK fields should be in the related tables. I'm trying to think of the
"main" purpose of the DB and that is to capture these incidents. The
other
information (location, subject, case worker) is related to this main
(incident) table and it makes me want to put a FK from each of these
tables
into the TblIncident, but this doesn't seem to be working correctly for
me.

I'll suggest that you read up on normalization and relational database
design. This may help with the notions of primary and foreign keys.

My basic approach is that anytime a record in a table "belongs" to a record
in another table, I need a "foreign key" to point back to WHICH record.
That's where the 'relationship' comes in. Think "parent" and "child" ... if
you don't include a ParentID (FK) in the child table, how do you know whose
kid it is?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.