.source = multipe tables with innner join How

[vttech]

I have a form called "frmImmunizations" that has four subforms

frmHepB_Immunization, frmMMR_Immunization, frmTd_Immunization and
frmVaricella_Immunization

they each populate an individual table

frmHepB_Immunization --> tblHepB_Immunization
frmMMR_Immunization --> tblMMR_Immunization
frmTd_Immunization --> tblTd_Immunization
frmVaricella_Immunization --> tblVaricella_Immunization


I want to use a command Button called cmdOK that is located on form "frmImmunizations"
to populate the contents of text boxes and combo boxes that are located in the forms

txtGivenHepB, cboAntiProHepB --> tblHepB_Immunization
txtGivenMMR, cboAntiProMMR --> tblMMR_Immunization
txtGivenTD, cboAntiProTD --> tblTd_Immunization
txtGivenVaricella, cboAntiProVaricella --> tblVaricella_Immunization


Each of these tables have the same Fields which are

PatientID, Given, Due and AntibodyPro


this is my code I used to connect the database

Private Sub cmdOK_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim varSelected As Variant


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "C:\Documents and Settings\cspence\Desktop\PTDBVer1\PTDBVer1.mdb"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockOptimistic
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With

End Sub

for the .Source will I have to change this to a join to select all four tables?? If yes I started the SQL statement below but

I am lost on how to inner join for than two table tables. Can someone help me finish the SQL statement and tell me if the

statement I have so far has the correct syntax??

.Source = " SELECT tblHepB_Immunization.Given, tblHepB_Immunization.AntibodyPro, tblMMR_Immunization.Given, 

tblMMR_Immunization.AntibodyPro, tblTd_Immunization.Given, tblTd_Immunization.AntibodyPro, tblVaricella_Immunization.Given, 

tblVaricella_Immunization.AntibodyPro FROM tblHepB_Immunization INNER JOIN tblMMR_Immunization ON 

tblHepB_Immunization.PatientID = tblHepB_Immunization.PatientID INNER JOIN ??

Newbie in search of knowledge

 

[cmmrfrds]

Since the data is the same in all the tables with just a different type of value it is probably better to have 1 table with a type indicator.

Make another table to hold the 4 types and then add the type to the first table to differentiate the 4 types of values. This will be easier in the long run.

The inner join won't work since all 4 tables would need the same value in order for it to be returned in the query. The alternative is to "Union All" the 4 tables, but this seems too cumbersome.

 

[vttech]

see what you are talking about. I was trying to avoid breaking the Normalization rules but in the end I did.

I redesigned my tables so that know one hold the items

ImmunizationID PrimaryKey AutoNumber
PatientID Foreign Key
ImmunizationTypeID Foreign Key
DateGiven Date
DateDue Date
AntibodyPro Date

Yes know I just have to make sure that

rs("ImmunizationTypeID") = Forms!frmPatientDemographics!frmPatientImmunization!cboImmunizationType

will add the contents of a combo box to the table

Newbie in search of knowledge