Srowe said:
First table is the "Complaint " table. This table contains the fields that
are relevant to the complaint.
Field: Complaintnum (autonumber)(primary key)
CsummID (number) Court summary number
StatsId (number)
SubjId (number) subject id number
RepDate (date/time) reported date
Reptime (date/time) reported time
This table is for all the information relevant to the initial complaint.
"Subject" Table. Records all information of all subjects involved in the
complaint. The entry form for this inforation is a subform of the
complaint
form.
Fields:
SubjId (autonum)(primary key)
compaintnum (number)
s1status (text)
S1nme (text)
S1G1 (text)
S1G2 (text)
etc...
This table contains all the infomation pertainuing to each subject. I am
able to add as many subjects as needed to this subform. The table also
contains descriptors for each person along with address, telephone etc...
"Court" table
Fields:
Courtid (autonum)(primary key)
"Court Summary" Table
Fields:
CSummID (autonumber)(primary key)
SubjId (number)
Infonum (number)
There is a total of 40 tables contained within the database. It is an old
database used for a small Police Service that I am attempting to upgrade
to
make it more user friendly and compile info from different tables on to a
few
different forms so the info is easily located.
The complaint table is the table that relates to all other tables which I
believe would be a one to many relationship. The subject and complaint
tables are the main tables used and the forms associated to them are the
main
forms used for day to day operations.
I also have a search form that allows me to search for each subject that
contains all of their info as well as a picture. This form is based on the
"subject" table. It is like a main subject form that has a picture and
more
detailed descriptors. I am trying to put a subform on this form that will
list all of the complaints that pertain to each individual as he/she is
searched.
I could attach the whole database unfortuneatley it does contain some
actual
files on my woking copy that are sensitive and con not be shared.
I can list the other tables if needed. I'm not sure if it would help or
hinder you.
Your help is greatly appreciated.
Sorry it took a while for me to get back to you, Scott. Life intervenes
sometimes.
From what you've said, it seems to me that a single subject can be involved
in more than one complaint, and a single complaint can involve more than one
subject. If that's not true, tell me, and disregard all of what follows.
To represent the many-to-many relationship of Subjects to Complaints, you
need a table like the SubjectsIncidents table I described earlier. Because
I now know that one of the tables to be linked is called "Complaint", let's
change the linking table name from "SubjectsIncidents" to
"SubjectsComplaints". So the SubjectsComplaints table will have these
fields:
SubjectsComplaints
----------------------------
SubjID (Number/Long Integer)
ComplaintNum (Number/Long Integer)
Both fields must be required, and the table will have a primary key composed
of both fields. If there are any data that are related specifically to this
particular subject's involvement in this particular complaint, there should
also be fields for them in this table. So you wouldn't have fields here for
name, address, etc., since those are specific to the subject only, without
regard to the complain.
When we're done, you will no longer need the SubjID in table Complaint, nor
the Complaintnum field in table "Subject", and you'll remove them. However,
if you currently have meaningful data stored in those fields, you can't
remove them yet.
As I understand it, you have or want to have a form based on Complaint, for
the purpose of displaying, adding, and editing complaints. On this form,
you want a subform that will let you add or edit the subjects involved in
the complaint. Suppose we call that subform "sfComplaintSubjects". It
should be based on the table SubjectsComplaints, *not* on table Subject. It
will include controls for all the fields from SubjectsComplaints, but the
ComplaintNum field can be made invisible (by setting its Visible property to
No) and even set to a width of 0 so that it doesn't take up any space on the
form.
When you add sfComplaintSubjects to the Complaint form as a subform, set the
Link Master Fields and Link Child Fields properties of the subform control
to ComplaintNum. That will ensure that you only see and edit the subjects
for the current complaint, and any subject you add via the subform will be
automatically stamped with the current ComplaintNum.
On the subform, use a combo box to represent the SubjID field. Set the
RowSource property of the combo box to a query of the Subject table that
includes the SubjID and, probably a calculated field that is created from
the last name, first name, and (if appropriate) middle name. The combo
box's bound column will be the SubjID column, but you'll set that column's
width to zero so that what the user sees is the name column. If you need it
to, your combo box can also include some additional columns to allow you,
when choosing a subject, to distinguish among subjects who have the same
name.
Because the combo displays the name, not the SubjID, entries will be limited
to the subjects in the list. If I were you, I'd use the combo box's
NotInList event to ask if the user wants to create a new subject, and if so,
open a form (bound to the Subject table) to do so. I'd probably also use
something like the DblClick event of the combo box to open the Subject
Details form for the selected subject, so that the user can easily review
all the information about that subject.
For your Subject Details form (whatever you call it), you can also add a
subform based on SubjectsComplaints to show all the complaints in which that
subject has been involved. In this case, the Link Master and Link Child
Fields would be the SubjID, and the subform would be set up to hide the
SubjID field but show the ComplaintNum field.
