Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Unhappy Unanswered: lookup in form populates all records

    I have just rearranged a database into more tables using Analyser, as I needed more fields than 255. So far so good, I have the query produced by Analyser and use that with the original forms.

    The original table had 2 lookup fields. One works fine. The other: I can enter new data into the lookup field in the query, but when I do it via a form it puts the same new data in every record. I have tried different join types in the query but it does it with them all.

    Any ideas? Thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: lookup in form populates all records

    Originally posted by fredachapman
    I have just rearranged a database into more tables using Analyser, as I needed more fields than 255. So far so good, I have the query produced by Analyser and use that with the original forms.

    The original table had 2 lookup fields. One works fine. The other: I can enter new data into the lookup field in the query, but when I do it via a form it puts the same new data in every record. I have tried different join types in the query but it does it with them all.

    Any ideas? Thanks.
    I am not sure I understand Lookup Field in your context. In Table Design there is a tab that allows you to use a Comobox or Listbox to populate the options of a field.

    You can also place a unbound textbox on a form and lookup a record, using the content within a query or filter of the attached table/view.

    Is one of these the case?

    How did you get more than 255 field in a table? Got to be a survey?

  3. #3
    Join Date
    Nov 2002
    Posts
    5

    Re: lookup in form populates all records

    Originally posted by Robt917
    I am not sure I understand Lookup Field in your context. In Table Design there is a tab that allows you to use a Comobox or Listbox to populate the options of a field.

    You can also place a unbound textbox on a form and lookup a record, using the content within a query or filter of the attached table/view.

    Is one of these the case?

    How did you get more than 255 field in a table? Got to be a survey?
    It's a Combo box; gets the values from another table.

    Sorry if I was unclear; I used Table Analyzer to divide a table when I needed to add new fields which would have taken it over 255. That was bad enough (I had to take the database home as it's nearly 10,000 records and I couldn't edit the registry on the network, but that's another story .....)

  4. #4
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: lookup in form populates all records

    Originally posted by fredachapman
    It's a Combo box; gets the values from another table.

    Sorry if I was unclear; I used Table Analyzer to divide a table when I needed to add new fields which would have taken it over 255. That was bad enough (I had to take the database home as it's nearly 10,000 records and I couldn't edit the registry on the network, but that's another story .....)
    So you made a continuous form and one of the fields is a combobox that when you change the data it changes the data for every record in the table?

    The comboBox appears to be unbound, meaning that the CONTROL SOURCE property is empty. It needs to have its corresponding field entered from the linked table/query.

    It that is not it, post the SQL statement for the query for the form's control source.

  5. #5
    Join Date
    Nov 2002
    Posts
    5

    Re: lookup in form populates all records

    Originally posted by Robt917
    So you made a continuous form and one of the fields is a combobox that when you change the data it changes the data for every record in the table?

    The comboBox appears to be unbound, meaning that the CONTROL SOURCE property is empty. It needs to have its corresponding field entered from the linked table/query.

    It that is not it, post the SQL statement for the query for the form's control source.
    Hi Rob, Happy Easter Sunday (it's getting up time here) the comboboxes are bound to the correct field in the query. I now know that although entering data into one record in the form or the query populates all records in the query but not in the table, so the table data is ok and any queries I ran off it would presumably be ok too, but I would like to fix it if I can because data inputters would get very confused. Thank for your help and here's the SQL:

    SELECT [Imms & Vacs].RecordID, [Imms & Vacs].[Patient Details_ID], [Patient Details].Serial, [Patient Details].Surname, [Patient Details].Firstname, [Patient Details].DOB, [Patient Details].Gender, [Patient Details].Street, [Patient Details].Area, [Patient Details].Postcode, [Patient Details].PhoneNo, [Patient Details].[Verified from file], [Patient Details].[Date verified], [Patient Details].[Verified/entered by], [Imms & Vacs].Referral_ID, Referral.GP, Referral.Consultant, Referral.DateRef, Referral.VAC_ID, Referral.Reason1, Referral.Reason2, Referral.Reason3, Referral.Reason4, Referral.Reason5, Referral.Reason6, Referral.VaccinReac, Referral.Allergy, [Medical History].PMC_ID, [Medical History].PMC_ID2, [Medical History].PMC_ID3, [Medical History].PMC_ID4, [Medical History].PMC_ID5, [Medical History].PMC_ID6, [Medical History].MC_ID, [Medical History].MC_ID2, [Medical History].MC_ID3, [Medical History].MC_ID4, [Medical History].MC_ID5, [Medical History].MC_ID6, [Imms & Vacs].DipTitre1, [Imms & Vacs].DipTitre1Date, [Imms & Vacs].TetTitre1, [Imms & Vacs].TetTitre1Date, [Imms & Vacs].MeaslesTir1, [Imms & Vacs].MeaslesTitre1Date, [Imms & Vacs].MumpsTitre1, [Imms & Vacs].MumpsTitre1Date, [Imms & Vacs].RubeTitre1, [Imms & Vacs].RubTitre1Date, [Imms & Vacs].PolioATire1, [Imms & Vacs].PolioATitre1Date, [Imms & Vacs].PolioBTire1, [Imms & Vacs].PolioBTitre1Date, [Imms & Vacs].PolioCTire1, [Imms & Vacs].PolioCTitre1Date, [Imms & Vacs].Pert1, [Imms & Vacs].Pert1Date, [Imms & Vacs].PnuT_1_IgG, [Imms & Vacs].PnuT_1_IgG1, [Imms & Vacs].PnuT_1_IgG2, [Imms & Vacs].PnuT1Date, [Imms & Vacs].PNUS1, [Imms & Vacs].PNUS4, [Imms & Vacs].PNUS5, [Imms & Vacs].PNUS6b, [Imms & Vacs].PNUS9v, [Imms & Vacs].PNUS14, [Imms & Vacs].PNUS18c, [Imms & Vacs].PNUS19f, [Imms & Vacs].PNUS23f, [Imms & Vacs].PNU1Date, [Imms & Vacs].Elisa1date, [Imms & Vacs].MenT1ElisaA, [Imms & Vacs].MenT1ElisaC, [Imms & Vacs].MenT1QPG, [Imms & Vacs].MenT1SBT, [Imms & Vacs].MenT1Date, [Imms & Vacs].OldMeasles1, [Imms & Vacs].OldMeasles1Date, [Imms & Vacs].DipTitre2, [Imms & Vacs].DipTitre2Date, [Imms & Vacs].TetTitre2, [Imms & Vacs].TetanusT2, [Imms & Vacs].MeaslesTir2, [Imms & Vacs].MeaslesT2Date, [Imms & Vacs].MumpsTitre2, [Imms & Vacs].MumpsT2Date, [Imms & Vacs].RubeTitre2, [Imms & Vacs].RubT2Date, [Imms & Vacs].PolioATire2, [Imms & Vacs].PolioAT2Date, [Imms & Vacs].PolioBTire2, [Imms & Vacs].PolioBT2Date, [Imms & Vacs].PolioCTire2, [Imms & Vacs].PolioCT2Date, [Imms & Vacs].Pert2, [Imms & Vacs].Pert2Date, [Imms & Vacs].MenT2ElisaA, [Imms & Vacs].MenT2ElisaC, [Imms & Vacs].Elisa2Date, [Imms & Vacs].MenT2QPG, [Imms & Vacs].MenT2SBT, [Imms & Vacs].MenT2TDate, [Imms & Vacs].PnuT_2_IgG, [Imms & Vacs].PnuT_2_IgG1, [Imms & Vacs].PnuT_2_IgG2, [Imms & Vacs].PnuT_2Date, [Imms & Vacs].PNUS1_2, [Imms & Vacs].PNUS4_2, [Imms & Vacs].PNUS5_2, [Imms & Vacs].PNUS6b_2, [Imms & Vacs].PNUS9v_2, [Imms & Vacs].PNUS14_2, [Imms & Vacs].PNUS18c_2, [Imms & Vacs].PNUS19f_2, [Imms & Vacs].PNUS23f_2, [Imms & Vacs].PNU1Date_2, [Imms & Vacs].OldMeasles2, [Imms & Vacs].OldMeaslesT2Date, [Imms & Vacs].DipTitre3, [Imms & Vacs].DipT3Date, [Imms & Vacs].TetTitre3, [Imms & Vacs].TetanusT3Date, [Imms & Vacs].MeaslesTir3, [Imms & Vacs].MeaslesT3Date, [Imms & Vacs].MumpsTitre3, [Imms & Vacs].MumpsT3Date, [Imms & Vacs].RubeTitre3, [Imms & Vacs].RubT3Date, [Imms & Vacs].PolioATire3, [Imms & Vacs].PolioAT3Date, [Imms & Vacs].PolioBTire3, [Imms & Vacs].PolioBT3Date, [Imms & Vacs].PolioCTire3, [Imms & Vacs].PolioCT3Date, [Imms & Vacs].Pert3, [Imms & Vacs].Pert3Date, [Imms & Vacs].MenT3ElisaA, [Imms & Vacs].MenT3ElisaC, [Imms & Vacs].Elisa3Date, [Imms & Vacs].MenT3QPG, [Imms & Vacs].MenT3SBT, [Imms & Vacs].MenT3TDate, [Imms & Vacs].PnuT_3_IgG, [Imms & Vacs].PnuT_3_IgG1, [Imms & Vacs].PnuT_3_IgG2, [Imms & Vacs].PnuT3Date, [Imms & Vacs].PNUS1_3, [Imms & Vacs].PNUS4_3, [Imms & Vacs].PNUS5_3, [Imms & Vacs].PNUS6b_3, [Imms & Vacs].PNUS9v_3, [Imms & Vacs].PNUS14_3, [Imms & Vacs].PNUS18c_3, [Imms & Vacs].PNUS19f_3, [Imms & Vacs].PNUS23f_3, [Imms & Vacs].PNU1Date_3, [Imms & Vacs].OldMeasles3, [Imms & Vacs].OldMeaslesT3Date, [Imms & Vacs].HepBT1, [Imms & Vacs].HepBTDate1, [Imms & Vacs].HepBT2, [Imms & Vacs].HepBTDate2, [Imms & Vacs].HepBT3, [Imms & Vacs].HepBTDate3, [Imms & Vacs].HepBT4, [Imms & Vacs].HepBTDate4, [Imms & Vacs].HepBT5, [Imms & Vacs].HepBTDate5, [Imms & Vacs].HepBT6, [Imms & Vacs].HepBTDate6, [Imms & Vacs].HepBT7, [Imms & Vacs].HepBTDate7, [Imms & Vacs].OldPert, [Imms & Vacs].OldPertDate, [Imms & Vacs].HibT1, [Imms & Vacs].HibTDate1, [Imms & Vacs].HibT2, [Imms & Vacs].HibTDate2, [Imms & Vacs].HibT3, [Imms & Vacs].HibTDate3, [Imms & Vacs].Dip1Date, [Imms & Vacs].Dip2Date, [Imms & Vacs].Dip3Date, [Imms & Vacs].Tet1Date, [Imms & Vacs].Tet2Date, [Imms & Vacs].Tet3Date, [Imms & Vacs].WC1Date, [Imms & Vacs].WC2Date, [Imms & Vacs].WC3Date, [Imms & Vacs].Polio1Date, [Imms & Vacs].Polio2Date, [Imms & Vacs].Polio3Date, [Imms & Vacs].HibImm1Dat, [Imms & Vacs].HibImm2Dat, [Imms & Vacs].HibImm3Dat, [Imms & Vacs].HibImm4Dat, [Imms & Vacs].HibImm5Dat, [Imms & Vacs].Measles1Dat, [Imms & Vacs].Mumps1Dat, [Imms & Vacs].Rubella1Dat, [Imms & Vacs].Measles2Dat, [Imms & Vacs].Mumps2Dat, [Imms & Vacs].Rubella2Dat, [Imms & Vacs].MR1, [Imms & Vacs].MR2, [Imms & Vacs].MMRIDat, [Imms & Vacs].MMRIIDat, [Imms & Vacs].BCGDat, [Imms & Vacs].MenCDate, [Imms & Vacs].MenCDate2, [Imms & Vacs].MenCDate3, [Imms & Vacs].MenCDate4, [Imms & Vacs].HepBImm1, [Imms & Vacs].HepBImm2, [Imms & Vacs].HepBImm3, [Imms & Vacs].HepBImm4, [Imms & Vacs].HepBImm5, [Imms & Vacs].HepBImm6, [Imms & Vacs].PnuImm1, [Imms & Vacs].Vaccine1, [Imms & Vacs].PnuImm2, [Imms & Vacs].Vaccine2, [Imms & Vacs].PnuImm3, [Imms & Vacs].Vaccine3, [Imms & Vacs].PnuImm4, [Imms & Vacs].Vaccine4, [Imms & Vacs].FluImm, [Imms & Vacs].[Additional data], [Imms & Vacs].PnuT_4_IgG, [Imms & Vacs].PnuT_4_IgG1, [Imms & Vacs].PnuT_4_IgG2, [Imms & Vacs].PnuT4Date, [Imms & Vacs].PNUS1_4, [Imms & Vacs].PNUS4_4, [Imms & Vacs].PNUS5_4, [Imms & Vacs].PNUS6b_4, [Imms & Vacs].PNUS9v_4, [Imms & Vacs].PNUS14_4, [Imms & Vacs].PNUS18c_4, [Imms & Vacs].PNUS19f_4, [Imms & Vacs].PNUS23f_4, [Imms & Vacs].PNU1Date_4
    FROM [Medical History] LEFT JOIN (([Imms & Vacs] LEFT JOIN [Patient Details] ON [Imms & Vacs].[Patient Details_ID] = [Patient Details].ID) LEFT JOIN Referral ON [Imms & Vacs].Referral_ID = Referral.ID) ON [Medical History].ID = [Imms & Vacs].[Medical History_ID];

    Freda

  6. #6
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: lookup in form populates all records

    Originally posted by fredachapman
    Hi Rob, Happy Easter Sunday (it's getting up time here) the comboboxes are bound to the correct field in the query. I now know that although entering data into one record in the form or the query populates all records in the query but not in the table, so the table data is ok and any queries I ran off it would presumably be ok too, but I would like to fix it if I can because data inputters would get very confused. Thank for your help and here's the SQL:

    SELECT [Imms & Vacs].RecordID, [Imms & Vacs].[Patient Details_ID], [Patient Details].Serial, [Patient Details].Surname, [Patient Details].Firstname, [Patient Details].DOB, [Patient Details].Gender, [Patient Details].Street, [Patient Details].Area, [Patient Details].Postcode, [Patient Details].PhoneNo, [Patient Details].[Verified from file], [Patient Details].[Date verified], [Patient Details].[Verified/entered by], [Imms & Vacs].Referral_ID, Referral.GP, Referral.Consultant, Referral.DateRef, Referral.VAC_ID, Referral.Reason1, Referral.Reason2, Referral.Reason3, Referral.Reason4, Referral.Reason5, Referral.Reason6, Referral.VaccinReac, Referral.Allergy, [Medical History].PMC_ID, [Medical History].PMC_ID2, [Medical History].PMC_ID3, [Medical History].PMC_ID4, [Medical History].PMC_ID5, [Medical History].PMC_ID6, [Medical History].MC_ID, [Medical History].MC_ID2, [Medical History].MC_ID3, [Medical History].MC_ID4, [Medical History].MC_ID5, [Medical History].MC_ID6, [Imms & Vacs].DipTitre1, [Imms & Vacs].DipTitre1Date, [Imms & Vacs].TetTitre1, [Imms & Vacs].TetTitre1Date, [Imms & Vacs].MeaslesTir1, [Imms & Vacs].MeaslesTitre1Date, [Imms & Vacs].MumpsTitre1, [Imms & Vacs].MumpsTitre1Date, [Imms & Vacs].RubeTitre1, [Imms & Vacs].RubTitre1Date, [Imms & Vacs].PolioATire1, [Imms & Vacs].PolioATitre1Date, [Imms & Vacs].PolioBTire1, [Imms & Vacs].PolioBTitre1Date, [Imms & Vacs].PolioCTire1, [Imms & Vacs].PolioCTitre1Date, [Imms & Vacs].Pert1, [Imms & Vacs].Pert1Date, [Imms & Vacs].PnuT_1_IgG, [Imms & Vacs].PnuT_1_IgG1, [Imms & Vacs].PnuT_1_IgG2, [Imms & Vacs].PnuT1Date, [Imms & Vacs].PNUS1, [Imms & Vacs].PNUS4, [Imms & Vacs].PNUS5, [Imms & Vacs].PNUS6b, [Imms & Vacs].PNUS9v, [Imms & Vacs].PNUS14, [Imms & Vacs].PNUS18c, [Imms & Vacs].PNUS19f, [Imms & Vacs].PNUS23f, [Imms & Vacs].PNU1Date, [Imms & Vacs].Elisa1date, [Imms & Vacs].MenT1ElisaA, [Imms & Vacs].MenT1ElisaC, [Imms & Vacs].MenT1QPG, [Imms & Vacs].MenT1SBT, [Imms & Vacs].MenT1Date, [Imms & Vacs].OldMeasles1, [Imms & Vacs].OldMeasles1Date, [Imms & Vacs].DipTitre2, [Imms & Vacs].DipTitre2Date, [Imms & Vacs].TetTitre2, [Imms & Vacs].TetanusT2, [Imms & Vacs].MeaslesTir2, [Imms & Vacs].MeaslesT2Date, [Imms & Vacs].MumpsTitre2, [Imms & Vacs].MumpsT2Date, [Imms & Vacs].RubeTitre2, [Imms & Vacs].RubT2Date, [Imms & Vacs].PolioATire2, [Imms & Vacs].PolioAT2Date, [Imms & Vacs].PolioBTire2, [Imms & Vacs].PolioBT2Date, [Imms & Vacs].PolioCTire2, [Imms & Vacs].PolioCT2Date, [Imms & Vacs].Pert2, [Imms & Vacs].Pert2Date, [Imms & Vacs].MenT2ElisaA, [Imms & Vacs].MenT2ElisaC, [Imms & Vacs].Elisa2Date, [Imms & Vacs].MenT2QPG, [Imms & Vacs].MenT2SBT, [Imms & Vacs].MenT2TDate, [Imms & Vacs].PnuT_2_IgG, [Imms & Vacs].PnuT_2_IgG1, [Imms & Vacs].PnuT_2_IgG2, [Imms & Vacs].PnuT_2Date, [Imms & Vacs].PNUS1_2, [Imms & Vacs].PNUS4_2, [Imms & Vacs].PNUS5_2, [Imms & Vacs].PNUS6b_2, [Imms & Vacs].PNUS9v_2, [Imms & Vacs].PNUS14_2, [Imms & Vacs].PNUS18c_2, [Imms & Vacs].PNUS19f_2, [Imms & Vacs].PNUS23f_2, [Imms & Vacs].PNU1Date_2, [Imms & Vacs].OldMeasles2, [Imms & Vacs].OldMeaslesT2Date, [Imms & Vacs].DipTitre3, [Imms & Vacs].DipT3Date, [Imms & Vacs].TetTitre3, [Imms & Vacs].TetanusT3Date, [Imms & Vacs].MeaslesTir3, [Imms & Vacs].MeaslesT3Date, [Imms & Vacs].MumpsTitre3, [Imms & Vacs].MumpsT3Date, [Imms & Vacs].RubeTitre3, [Imms & Vacs].RubT3Date, [Imms & Vacs].PolioATire3, [Imms & Vacs].PolioAT3Date, [Imms & Vacs].PolioBTire3, [Imms & Vacs].PolioBT3Date, [Imms & Vacs].PolioCTire3, [Imms & Vacs].PolioCT3Date, [Imms & Vacs].Pert3, [Imms & Vacs].Pert3Date, [Imms & Vacs].MenT3ElisaA, [Imms & Vacs].MenT3ElisaC, [Imms & Vacs].Elisa3Date, [Imms & Vacs].MenT3QPG, [Imms & Vacs].MenT3SBT, [Imms & Vacs].MenT3TDate, [Imms & Vacs].PnuT_3_IgG, [Imms & Vacs].PnuT_3_IgG1, [Imms & Vacs].PnuT_3_IgG2, [Imms & Vacs].PnuT3Date, [Imms & Vacs].PNUS1_3, [Imms & Vacs].PNUS4_3, [Imms & Vacs].PNUS5_3, [Imms & Vacs].PNUS6b_3, [Imms & Vacs].PNUS9v_3, [Imms & Vacs].PNUS14_3, [Imms & Vacs].PNUS18c_3, [Imms & Vacs].PNUS19f_3, [Imms & Vacs].PNUS23f_3, [Imms & Vacs].PNU1Date_3, [Imms & Vacs].OldMeasles3, [Imms & Vacs].OldMeaslesT3Date, [Imms & Vacs].HepBT1, [Imms & Vacs].HepBTDate1, [Imms & Vacs].HepBT2, [Imms & Vacs].HepBTDate2, [Imms & Vacs].HepBT3, [Imms & Vacs].HepBTDate3, [Imms & Vacs].HepBT4, [Imms & Vacs].HepBTDate4, [Imms & Vacs].HepBT5, [Imms & Vacs].HepBTDate5, [Imms & Vacs].HepBT6, [Imms & Vacs].HepBTDate6, [Imms & Vacs].HepBT7, [Imms & Vacs].HepBTDate7, [Imms & Vacs].OldPert, [Imms & Vacs].OldPertDate, [Imms & Vacs].HibT1, [Imms & Vacs].HibTDate1, [Imms & Vacs].HibT2, [Imms & Vacs].HibTDate2, [Imms & Vacs].HibT3, [Imms & Vacs].HibTDate3, [Imms & Vacs].Dip1Date, [Imms & Vacs].Dip2Date, [Imms & Vacs].Dip3Date, [Imms & Vacs].Tet1Date, [Imms & Vacs].Tet2Date, [Imms & Vacs].Tet3Date, [Imms & Vacs].WC1Date, [Imms & Vacs].WC2Date, [Imms & Vacs].WC3Date, [Imms & Vacs].Polio1Date, [Imms & Vacs].Polio2Date, [Imms & Vacs].Polio3Date, [Imms & Vacs].HibImm1Dat, [Imms & Vacs].HibImm2Dat, [Imms & Vacs].HibImm3Dat, [Imms & Vacs].HibImm4Dat, [Imms & Vacs].HibImm5Dat, [Imms & Vacs].Measles1Dat, [Imms & Vacs].Mumps1Dat, [Imms & Vacs].Rubella1Dat, [Imms & Vacs].Measles2Dat, [Imms & Vacs].Mumps2Dat, [Imms & Vacs].Rubella2Dat, [Imms & Vacs].MR1, [Imms & Vacs].MR2, [Imms & Vacs].MMRIDat, [Imms & Vacs].MMRIIDat, [Imms & Vacs].BCGDat, [Imms & Vacs].MenCDate, [Imms & Vacs].MenCDate2, [Imms & Vacs].MenCDate3, [Imms & Vacs].MenCDate4, [Imms & Vacs].HepBImm1, [Imms & Vacs].HepBImm2, [Imms & Vacs].HepBImm3, [Imms & Vacs].HepBImm4, [Imms & Vacs].HepBImm5, [Imms & Vacs].HepBImm6, [Imms & Vacs].PnuImm1, [Imms & Vacs].Vaccine1, [Imms & Vacs].PnuImm2, [Imms & Vacs].Vaccine2, [Imms & Vacs].PnuImm3, [Imms & Vacs].Vaccine3, [Imms & Vacs].PnuImm4, [Imms & Vacs].Vaccine4, [Imms & Vacs].FluImm, [Imms & Vacs].[Additional data], [Imms & Vacs].PnuT_4_IgG, [Imms & Vacs].PnuT_4_IgG1, [Imms & Vacs].PnuT_4_IgG2, [Imms & Vacs].PnuT4Date, [Imms & Vacs].PNUS1_4, [Imms & Vacs].PNUS4_4, [Imms & Vacs].PNUS5_4, [Imms & Vacs].PNUS6b_4, [Imms & Vacs].PNUS9v_4, [Imms & Vacs].PNUS14_4, [Imms & Vacs].PNUS18c_4, [Imms & Vacs].PNUS19f_4, [Imms & Vacs].PNUS23f_4, [Imms & Vacs].PNU1Date_4
    FROM [Medical History] LEFT JOIN (([Imms & Vacs] LEFT JOIN [Patient Details] ON [Imms & Vacs].[Patient Details_ID] = [Patient Details].ID) LEFT JOIN Referral ON [Imms & Vacs].Referral_ID = Referral.ID) ON [Medical History].ID = [Imms & Vacs].[Medical History_ID];

    Freda
    Wow! That is the longest query statement I have ever read.

    Need to make a couple orientation changes to your tables so they relate from the core data to the detail data. The core table is the Patient Details even though the task may be working with [Imms & Vacs]. So all the linked data pertaining to the patient would flow from the [Patient Detail].ID

    Add
    To [Referral] the field [Patient Detail_ID]
    To [Medical History] the field [Patient Detail_ID]

    You no longer need
    [Imms & Vacs].Referral_ID
    [Imms & Vacs].[Medical History_ID]

    Your SQl is this

    SELECT [Medical History].*, [Imms & Vacs].*, Referral.*, [Patient Detail].*
    FROM (([Patient Details] LEFT JOIN [Imms & Vacs] ON [Patient Details].ID = [Imms & Vacs].[Patient Details_ID]) LEFT JOIN [Medical History] ON [Patient Details].ID = [Medical History].[Patient Details_ID]) LEFT JOIN Referral ON [Patient Details].ID = Referral.[Patient Details_ID];

    However!!!

    This would be a fair amount of redesign but it will save you space, processor speed, and a lot of time in the future since [Imms & Vacs] probably will change over time.

    The [Imms & Vacs] table can be set up with 5 fields:

    IV_ID autonumber key
    [Patient Details_ID] Long index
    IV_Group Integer major catagories: PolioAT,PolioBT,Measles1,Measles2
    IV_Step Integer Imm1,Imm2,Imm3
    IV_Date date

    You need two PickList for the IV_Group and IV_Step. Attach as ComboBoxes under lookup in the Table Tab design view.

    Code AutoNumber
    Desc Text

    On your form where all of the fields from the [Imms & Vacs] table appear replace it with a subform of the new [Imms & Vacs] design.

    If you want more info on how to do this let me ask.

  7. #7
    Join Date
    Nov 2002
    Posts
    5
    Rob, thanks, you are a star, I will have a go at this on Tuesday a.m.

    Freda

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •