Starting from scratch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple one table DB that I would like to start over. I will bring
over the data after creating it. I need a Database that I can have Name,
(along with other personal info)and show that this person has violated one of
our rules, need the date and what type of violation. But after all the info
is in (which includes a picture of person) we need to go back to this person
and add other violations and dates, then be able to print a report showing me
the violations and dates along with their information.
Any ideas. Thanks in advance.
 
Mike, you need 3 tables here.

Person table (one record for each person):
PersonID AutoNumber primary key
Surname Text
FirstName Text
...

Violation table (one record for each kind of violation:
ViolationID AutoNumber primary key
ViolationName Text description of this violation.

PersonViolation table (one record for each time a person commits a
violation):
PersonID Number foreign key to Person.PersonID
ViolationID Number foreign key to Violation.ViolationID
ViolationDate Date/Time when this violation occurred.

Your interface will consist of a main form bound to the Person table, and a
subform bound to the PersonViolation table. In the subform, you can use a
combo box to select the violation committed. (The combo lists the violations
in the Violation table.) If a person commits another violation, you just add
another row to the subform, showing which violation and the date of the 2nd
offence on a 2nd row.
 
Mike,

Sounds like an interesting project--I use to work in private security (so I
know about property rules and violations). Nonetheless, what you need (at
minimum) are two tables of data; however, it appears like more would be in
order here. One table storing details about people, your violators. The
other table storing data about violations, when they occur, etc. Make sure
each table has an ID field, for example:

tblViolators
========
ViolatorID (AutoNumber)
LastName
FirstName

tblViolations
========
ViolationID (AutoNumber)
Date
ViolationTypeID (a third table for types of violations)
ViolatorID (You would create a relationship between this table and the
tblViolators here)

After creating the table structure, then you create a one-to-many
relationship between the two tables (tblViolators and tblViolations).
If you have more questions, feel free to send me an e-mail anytime
tshillamATcomcast.net (replace AT with @ symbol).

Best regards,

Todd
 
Back
Top