Need some tips on the design of a database...(long)

[Rhett_Y]

Hello..

I am putting together a database that will track incidents for our city on
the 4th of july...And I started to read about normalization (this is
long..please bear with me...)

Here is how I have it setup.....
tblMain
IncidentNumber (PK)(AutoNumber)
IncidentDate
TagNumber
Address
CityStreet (lookup table that has all the street names)
City (look up table with main and surrounding cities)
ZipCode
RD
PaveAssociated (yes/no)
PaveNumber (lookup table with pave numbers)
Source (lookup table for source)
FireworkType (lookup table for firework source)
LocationType (lookup table for location type)
LastUpdated
CaseClosed
CaseClosedMemo
Completed Yes/No
Teams (lookup table of assigned team who is inputing the incident)

tblInformantInfo
IncidentNumber (setup relationship with incident number from tblMain)
Informant (yes/no)
InformantAnnon (yes/no)
TypeOfInformatant (lookup table for TOI)
LName
FName
Note

(this one we can have more then one person we contacted etc..can I have it
setup like this?)

tblActionTaken
IncidentNumber (setup relationship with incident number from tblMain)
ActionTaken (lookup table for action taken)
LName
FName
2LName
2FName
Note

(this table I am thinking I might get rid of and put booking/cite etc on the
main table...seems like I am entering some info twice ie names etc)

tblArrestHP
IncidentNumber (setup relationship with incident number from tblMain)
LName (there could be say up to ten people how would I handle this part?)
Fname
MInitial
Booking (yes/no)
BookingNumber
Cite (yes/no)
CiteNumber
Classification
Type
Note

All of these table are connected to the tblMain via the incident number ( I
am hoping I am doing that part right.........) Also my thought process for
the note and names was we could have an info from an informant that leads to
contacting another person that leads to a cite/arrest etc..

Question:

Did I set this up the table structure correctly if not.. what would you do
differently?

Question:

How can I setup a form for data entry when I have so many other forms that
are linked to the main. When I pick all the tables for the data entry..when I
preview it, the form is blank?

I am still learning and any help would greatly appreciated!

Thanks again
R~

 

[jahoobob via AccessMonster.com]

Looks pretty good! Just a few suggestions that may help some.
I would have a main form based on tblMain and subforms based on the other
tables. As for the Arrests, when you link the Arrests subform to the main
form via the IncidentNumber, you can enter an unlimited number of people
arrested and they will all be tied to the one incident. The same applies to
other tables where multiple names, etc. will be entered for one incident.
It seems redundant to have Informant Yes/No in tblInformantInfo as that is
all that would be entered in this table.
You could take out the names in tblActionTaken and add an Id there and then
create a table tblActionNames and tie its subform to tblActionTaken subform
as you tied the tblActionTaken subform to th tblMain form.
Hope this helps,
Bob

 

[Rhett_Y]

Bob

Thanks for the tips...... I am in the process of making the
changes......For the tables that have names..... On a form.... How can I have
it setup so the end user knows they can enter more then one name for an
incident on a form??

Thanks again for the tips!!! Very much appreciated..

R~

 

[jahoobob via AccessMonster.com]

Those subforms should have their Default view property set to Continuous and
whenever the user begins to enter data in the first line (record) another
blank line will display below it and continue in the same manner with every
line started.

Bob

Thanks for the tips...... I am in the process of making the
changes......For the tables that have names..... On a form.... How can I have
it setup so the end user knows they can enter more then one name for an
incident on a form??

Thanks again for the tips!!! Very much appreciated..

R~

Looks pretty good! Just a few suggestions that may help some.
I would have a main form based on tblMain and subforms based on the other

[quoted text clipped - 93 lines]

 

[Rhett_Y]

bob...

Ahhh subforms......

Thanks that makes sense.. I was getting a little lost with how to go about
this...

Thanks again
R~

Those subforms should have their Default view property set to Continuous and
whenever the user begins to enter data in the first line (record) another
blank line will display below it and continue in the same manner with every
line started.

Bob

Thanks for the tips...... I am in the process of making the
changes......For the tables that have names..... On a form.... How can I have
it setup so the end user knows they can enter more then one name for an
incident on a form??

Thanks again for the tips!!! Very much appreciated..

R~

Looks pretty good! Just a few suggestions that may help some.
I would have a main form based on tblMain and subforms based on the other

[quoted text clipped - 93 lines]

Thanks again
R~