one to many relationship and forms creation

[GMartin via AccessMonster.com]

I have seriously strayed from the correct path and hope someone can help.
What I have is this:

tblCases tblVic
pkeycaseno pkeyVic
outcome fkeycaseno
incident date age

tblInjuries tblListInjury
AllIjury pkeyInjury
fkeyVic InjuryType
fkeyInjury

tblMedical tblListMedical
Allmedical pkeyMedical
fkeyVic MedicalType
fkeyMedical

tblCases stores initial case information and has approximately 8 more fields.
I the tblListInjury has the type of injury that can occur. This will
restrict the data selection that the user will be able to choose from.
tblLisMedical and another 2 other tables in same format are just like
tblListInjury and is used to restrict data entry.

Relationships are set up from tblistInjury to tblIjuries and tblVic to
tblInjuries and tblListMedical to tblMedical and tblVic to tblMedical. The
same holds true for the other tables as well.

Can I instead of using separate join tables, just create a join table and
list all the fkeyIjury, fkeyMedical, fkeyXXX under 1 join table? Or is there
a simpler way to accomplish what I am trying to do? Because seperate join
tables for the many to many is creating quite a few tables. Another issue is
with form and subform creation. My hopes is to have the main form with
tblCases and subform with tblVic. TblVic will have subforms of injuries and
medical where the user can select in datasheet view more than one injury per
victim.

Thanks for the help and I hope I have been clear enough.

 

[Dennis]

Why are you creating more tables? You shouldn't be creating ANY tables other
than your initial data tables. Are you talking about creating QUERIES?

 

[GMartin via AccessMonster.com]

Sorry if I was not too clear. I currently have the tblListInjuries <-
tblInjuries->tblVic joined because tblinjuries list my type of injuries and
tblvic list vics that are a part of the tblcase. tblListMedical<-tblMedical-

tblVic are joined and etc for the others. Can I have only one joined table

instead of a seperate one for each tblListInjuries tblListMedical etc? Can
the tblInjuries be changed to join (tblInjuries, tblMedical, etc <-
tblInjuries->tblVic)? If so, what is the best way to create the subform so
that the user can select each injury that applies to the vic and each medical,
etc that applies to the vic?

Why are you creating more tables? You shouldn't be creating ANY tables other
than your initial data tables. Are you talking about creating QUERIES?

I have seriously strayed from the correct path and hope someone can help.
What I have is this:

[quoted text clipped - 34 lines]

Thanks for the help and I hope I have been clear enough.

 

[Dennis]

Look, you don't JOIN tables. You RELATE tables by commone key values. You can
use JOIN in SQL query syntax to extract related table data (from multiple
tables) into a single entity that can source a form or report (or even
another query).

I quite simply don't understand your terminology.

Sorry if I was not too clear. I currently have the tblListInjuries <-
tblInjuries->tblVic joined because tblinjuries list my type of injuries and
tblvic list vics that are a part of the tblcase. tblListMedical<-tblMedical-

tblVic are joined and etc for the others. Can I have only one joined table

instead of a seperate one for each tblListInjuries tblListMedical etc? Can
the tblInjuries be changed to join (tblInjuries, tblMedical, etc <-
tblInjuries->tblVic)? If so, what is the best way to create the subform so
that the user can select each injury that applies to the vic and each medical,
etc that applies to the vic?

Why are you creating more tables? You shouldn't be creating ANY tables other
than your initial data tables. Are you talking about creating QUERIES?

I have seriously strayed from the correct path and hope someone can help.
What I have is this:

[quoted text clipped - 34 lines]

Thanks for the help and I hope I have been clear enough.

 

[Rod Plastow]

Hi Dennis,

Don't be too hard on him; this is a New Users board after all.

The question seems to be (quite apart from any comments on the integrity of
the design): can the number of intersection/cross reference/join tables
(whatever name you use) that resolve many-to-many relationships be reduced.
My answer is no; for each many-to-many relationship you require at least one
such table. These tables proliferate in a fully normalised design.

Regards,

Rod

 

[GMartin via AccessMonster.com]

Sorry to have confused anyone by using wrong terminology. I was actually
referring to junction tables not joins as I previously had stated. Rod, I
appreciate your response. Yes, I am a new user and yes, I have been reading
material on Access from books and this forum.