Automatic data entery

[Thomas Baughman]

Here is the problem. I have a table in which each record has an ID and one
of the field is crimes. So record number 1 committed say, 5 different
crimes, and record 2, committed 3, and record four didn't commit any. The
crimes field would list say 20 possible crimes and a record could have
anywhere from 0 to all of them checked.

How do I set it up so that I only enter the ID once in two table. One with
the ID number and other information and the second with the ID number and
each of the crimes associated with that number. What would be the simplest
way for a user to enter this?


ID Name State
1 John NY
2 Mary FL


ID Crime
1 murder
1 robbery
1 battery
2 car theft

 

[Rolls]

You have two entity tables (tblPerson, tblCrime). There is a many:many
relationship between them. To resolve the ambiguity you need a relationship
table that will contain the PK (PersonID, CrimeID) values from the entity
tables as FK fields in the relationship table.

On your frmPerson you'll select a unique person. This is the parent. In an
attached subform (continuous form) sfrmCrimes you'll be able to select from
the child table tblCrimes as many as you need, creating a record for each
unique combination of person and crime.

tblPerson (entity)
PersonID (PK)
PersonName

1 Dillinger
2 Manson

tblCrimes (entity)
CrimeID (PK)
CrimeName

1 Bank Robbery
2 Murder
3 Jaywalking

tblPerson-Crimes (relationship)
PersonID (FK)
CrimeID (FK)

1 1
2 2
2 3