How can I set this up

[Guest]

Hi all..

I currently have a database setup that has about 35 fields in it. What I
would like to do is break this up into the following areas:

1.incident,Incident Number, location,time,date, etc
2.contact person, contact person type, IE first-last name,address,phone, etc
3.attending person IE first-last name, address,action taken (drop down list)

How would I go about starting over (not worried about data) and having three
tables linked?

The way I am looking at this is one incident can have a contact person
and/or attending person..... I want to keep track of how many incidents I
have. I don't know how I would setup the relationship part of it.

So when I do a form I will be able to use a tabbed format and be able to
enter the info in the above three tables from one form? Is this possible?

Thanks
R~

 

[Guest]

1.incident,Incident Number, location,time,date, etc

2.contact person, contact person type, IE first-last name,address,phone, etc
3.attending person IE first-last name, address,action taken (drop down list)

You could combine the contact and attending persons into one table. It
looks like you have the same information for both, so would make sense to
just put them into the same table.

Then you have incidents, and people:

tbl_Incidents
IncidentID (PK)
IncidentInfo

tbl_People
PersonID (PK)
PersonInfo

Then you need a table that connect people with the incident. To make it
most flexible, use a linking table that would allow you to assign any number
of people as contacts or attending persons. You would probably have a field
that could indicate whether they are a contact or an attending, and perhaps a
text field where you could indicate any details about that particular person
with a particular incident.

tbl_IncidentPeople
IncidentPeopleID (PK)
IncidentID (FK)
PersonID (FK)
IncidentPeopleInfo

You could then create a form where you could choose an incident, and have
subforms that would list the contacts and attending persons. You could
create another form where you could choose an attending person, and show the
incidents they have attended.

 

[Jeff Boyce]

So, by your design, an [AttendingPerson] could only take one action. If
that isn't true in your situation, you'll need to design your table
structure to allow an incident to have multiple [ActionTaken] records, each
with it's own [AttendingPerson].

I'm with mnature, too. A single "Person" table could be used, and you'd
only need the PersonID to fill in either [ContactPerson] and/or
[AttendingPerson].

Regards

Jeff Boyce
Microsoft Office/Access MVP