Thanks for the quick response - regarding the point you bring up reference
the officerbadgenumber in the forceused table, I had posted my table design a
couple weeks ago and based on the fact that there could be more than one
officer involved, sometimes using more than one type of force, the tables
were set up that way. Hopefully that is correct for that scenario.

Your reply makes me wonder if I'm looking at the forms incorrectly. When
needed, I expect each of these fields to be used (i.e., there will never be
an incident where no officer is involved, no suspect, no type of force,
etc.). I guess that's why I'm looking at putting everything on a form for
the user to input the data. With your second paragraph, it would seem most
forms/subforms are done for people to "view" the data already in the records.


Your advice is appreciated and I'll give it a shot.

"Daryl S" wrote:

Lee Ann -

A subform is used to help show the 'many' side of a one-to-many
relationship. For many databases, this is a straight-forward relationship.
It gets more complicated with the many-to-many relationships such as those in
your database. While the theory still holds, you have to adjust it to your
needs.

For example, you might want to show suspects with all their incidents. The
suspect table would be the recordsource for the main form, and Incident
information would be used in the subform. This Incident information would
normally be a query based on the IncidentSuspect table joined to the Incident
table.

You could also show Officers on a main form with Incidents on a subform this
way, and many other one-to-many relationships.

I have a feeling you want to show everything about an incident on one form.
In that case, the Incident table would be used for the main form, and you may
have several subforms - one for the officers, one for the suspects. The
suspect subform would probably have a recordsource of a query based on
TblIncidentSuspect joined with TblSuspect, and would be linked to the main
form through the IncidentNumberID. This suspect main form may include a
subform of its own to show the charges against the suspects. This
sub-subform would probably have as its record source a query that joins the
tblCharges to the tblIncidentSuspectCharges, and would be linked to the
IncidentSuspect subform based on the IncidentSuspectID field. Similarly, the
Officer subform might have a subform of it's own to show the Force Used.
This data would come from a query based on the the TblForceUsed joined to the
TypeOfForce table. Trying to do this will point out that you need to change
your TblForceUsed table - instead of OfficerBadgeNumber, you need the
IncidentOfficerID as the foreign key.

Hope that helps!

--
Daryl S


"Lee Ann" wrote:

Hello,

I'm trying to figure out the concept of embedding subforms into main forms.
I have main tables, 4 junction tables, and a few look-up tables (not all
look-ups are listed below). If I'm reading it correctly, the junction tables
which relate to the main tables should be in the form of a subform placed
within the main form they relate to? I'm lost on the fact that I believe I
have a subform which appears should be on another subform which should then
be on a form. If this is correct, the form looks extremely busy and not user
friendly, and just plain incorrect. Is it true, based on the relationships,
that the PK and FK on the related tables should be joined between the subform
and main form?

These are the tables I have:

TblIncident
IncidentnumberID (PK)
misc. fields

TblSuspect
SuspectID (PK)
misc. fields

TblOfficer
OfficerBadgeNumberID (PK)
OfficerLastName
OfficerFirstName

TblIncidentOfficer
IncidentOfficerID (PK)
IncidentNumberID (FK)
OfficerBadgeNumberID (FK)

TblIncidentSuspect
IncidentSuspectID (PK)
IncidentNumberID (FK)
SuspectID (FK)

TblIncidentSuspectCharges
IncidentSuspectChargesID (PK)
IncidentSuspectID (FK)
ChargesID (FK)

TblForceUsed
ForceUsedID (PK)
OfficerBadgeNumber (FK)
TypeofForceID (FK)

TblCharges
ChargesID (PK)
Charges

Thanks in advance for any assistance.