error 3022 indexed linking field

[Guest]

Good day to you all,
I have a form composed of a query linking various tables together. The
linking field is -Procedure_Tag- which I have indexed on each table and I
allow duplicates. The form view displays everything how I laid it out, but
when I fill out my first record, I get error 3022, stating:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship."
Each table has its own distinct primary key that is shared by no other
table. I appreciate any help that is given.
Thanks in advance.

 

[TC]

Show us the fields in each table. (We only need their name, not their
types & lengths.) Also show us which field(s) you have selected as the
primary key in each table.

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Hi TC,
Thanks for responding. These are the affected tables complete with all
their fields, though they are not all within the query which produces my
faulty form. The query is named Qry_THA3 and the form produced is frm_THA2:

subProcedure Tag: Procedure Tag (PK), Procedure Name, Procedure.

Tbl_THA: THA_number (PK), Procedure Tag (indexed, no duplicates), Date,
Area_Description, Assess_Performer, Physical_Hazards, Biological_Hazards,
Chemical_Hazards, Work_Other_Hazards, design, guards, Comm, isolation,
ventilation, automation, barriers, Other9a, Other9b, substitution, training,
Safe_Plan, Policy, Other10a, Other10b, Hard_hat, Eyewear, Safe_feet,
Respirator, Fall_protect, hearing, Vest, Coveralls Gloves, Other 11a, Other
11b, Hazard1 (there are 16 Hazard* fields).

Tbl_PhysHaz: Physical_Hazard _# (PK), Procedure Tag (indexed,no
duplicates), Lift_handle, Repetitive, Slip_trip, Moving_parts, Work_heights,
Pressure_sys, Vehicle, Electricity, Noise, Lighting, Vibration, Other1a,
Other1b, Other2a, Other2b, Lift_handle#, Repetitive#, Slip_trip#,
Moving_parts#, Work_heights#, Pressure_sys#, Vehicle#, Electricity#, Noise#,
Lighting#, Vibration#, Other1a#, Other1b, Other2a#, Other2b.

Tbl_BioHaz: Bio_Hazard# (PK), Procedure Tag (indexed,no duplicates), Mould,
Blood_fluid, Sewage, Other3a, Other3b, Other4a, Other4b, Mould#,
Blood_fluid#, Sewage#, Other3a#, Other4a#.

Tbl_ChemHaz: Chem_Hazard# (PK), Procedure Tag (indexed,no duplicates),
Compress_gas, Flame_combust, Oxidize, Toxic, Infect, Corrosive, Mist_vapour,
Dust, Other5a, Other5b, Other6a, Other6b, Compress_gas#, Flame_combust#,
Oxidize#, Toxic#, Infect#, Corrosive#, Mist_vapour#, Dust#, Other5a#,
Other6a#.

Tbl_Work/OtherHaz: Other_Hazards_# (PK), Procedure Tag (indexed,no
duplicates), Confined, Temp, Other7a, Other7b, Violence, Alone, Lights,
Other8a, Other8b, Confined#, Temp#, Other7a#, Violence#, Alone#, Lights#,
Other8a#.

Tbl_HazRating: Hazard_Rating_# (PK), Procedure Tag (indexed,no duplicates);
there are 16 each of the following fields: Haz_Decsrip*, Exposure* ,
Probability*, Conseq*, Total*, Rating*a, Rating*b, Rating*c.

In Qry_THA3, the primary key fields of each table are not involved in the
query but all the other fields are. The only exception is Procedure Tag from
subProcedure Tag table, which is the only field used from that table in the
query, and it links all the other tables together.
I hope that this information is comprehensive enough, if not let me know
what else is needed.
Thanks for the help,
J@50N-5ALTR

 

[TC]

Hi TC,
Thanks for responding. These are the affected tables complete with all
their fields, though they are not all within the query which produces my
faulty form. The query is named Qry_THA3 and the form produced is frm_THA2:


subProcedure Tag: Procedure Tag (PK), Procedure Name, Procedure.

Ok, so there are many Procedures, each identified by (unique) Procedure
Tag.
- Each Procedure has a unique Procedure Tag value.
- Two or more Procedures can not have the same Procedure Tag value.
Correct?

Tbl_THA: THA_number (PK), Procedure Tag (indexed, no duplicates), Date,
Area_Description, Assess_Performer, Physical_Hazards, Biological_Hazards,
Chemical_Hazards, Work_Other_Hazards, design, guards, Comm, isolation,
ventilation, automation, barriers, Other9a, Other9b, substitution, training,
Safe_Plan, Policy, Other10a, Other10b, Hard_hat, Eyewear, Safe_feet,
Respirator, Fall_protect, hearing, Vest, Coveralls Gloves, Other 11a, Other
11b, Hazard1 (there are 16 Hazard* fields).

That table structure means the following:
- Each THA is uniquely identified by THA_number.
- Two more more THAs can never have the same THA_number.
- Each THA relates to precisely *one*, or *zero*, Procedures.
- A single THA can not relate to *more than one* Procedure.
- A given Procedure may have zero, one, or more, THA's.
Correct?

Tbl_PhysHaz: Physical_Hazard _# (PK), Procedure Tag (indexed,no
duplicates), Lift_handle, Repetitive, Slip_trip, Moving_parts, Work_heights,
Pressure_sys, Vehicle, Electricity, Noise, Lighting, Vibration, Other1a,
Other1b, Other2a, Other2b, Lift_handle#, Repetitive#, Slip_trip#,
Moving_parts#, Work_heights#, Pressure_sys#, Vehicle#, Electricity#, Noise#,
Lighting#, Vibration#, Other1a#, Other1b, Other2a#, Other2b.

That table structure means the following:
- Each Physical Hazard is uniquely identified by Physical_Hazard_#.
- Two more more Physical Hazards can never have the same
Physical_Hazard_#.
- Each Physical Hazard relates to precisely *one*, or *zero*,
Procedures.
- A single Physical Hazard can not relate to *more than one* Procedure.
- A given Procedure may have zero, one, or more, Physical Hazards.
Correct?

Tbl_BioHaz: Bio_Hazard# (PK), Procedure Tag (indexed,no duplicates), Mould,
Blood_fluid, Sewage, Other3a, Other3b, Other4a, Other4b, Mould#,
Blood_fluid#, Sewage#, Other3a#, Other4a#.

That table structure means the following:
- Each Bio Hazard is uniquely identified by Bio_Hazard#.
- Two more more Bio Hazards can never have the same Bio_Hazard#.
- Each Bio Hazard relates to precisely *one*, or *zero*, Procedures.
- A single Bio Hazard can not relate to *more than one* Procedure.
- A given Procedure may have zero, one, or more, Bio Hazards.
Correct?

Tbl_ChemHaz: Chem_Hazard# (PK), Procedure Tag (indexed,no duplicates),
Compress_gas, Flame_combust, Oxidize, Toxic, Infect, Corrosive, Mist_vapour,
Dust, Other5a, Other5b, Other6a, Other6b, Compress_gas#, Flame_combust#,
Oxidize#, Toxic#, Infect#, Corrosive#, Mist_vapour#, Dust#, Other5a#,
Other6a#.

That table structure means the following:
- Each Chem Hazard is uniquely identified by Chem_Hazard#.
- Two more more Chem Hazards can never have the same Chem_Hazard#.
- Each Chem Hazard relates to precisely *one*, or *zero*, Procedures.
- A single Chem Hazard can not relate to *more than one* Procedure.
- A given Procedure may have zero, one, or more, Chem Hazards.
Correct?

Tbl_Work/OtherHaz: Other_Hazards_# (PK), Procedure Tag (indexed,no
duplicates), Confined, Temp, Other7a, Other7b, Violence, Alone, Lights,
Other8a, Other8b, Confined#, Temp#, Other7a#, Violence#, Alone#, Lights#,
Other8a#.

That table structure means the following:
- Each Work/Other Hazard is uniquely identified by Other_Hazards_#.
- Two more more Work/Other Hazards can never have the same
Other_Hazards_#.
- Each Work/Other Hazard relates to precisely *one*, or *zero*,
Procedures.
- A single Work/Other Hazard can not relate to *more than one*
Procedure.
- A given Procedure may have zero, one, or more, Work/Other Hazards.
Correct?

Tbl_HazRating: Hazard_Rating_# (PK), Procedure Tag (indexed,no duplicates);
there are 16 each of the following fields: Haz_Decsrip*, Exposure* ,
Probability*, Conseq*, Total*, Rating*a, Rating*b, Rating*c.

That table structure means the following:
- Each Hazard Rating is uniquely identified by Hazard_Rating_#.
- Two more more Hazard Ratings can never have the same Hazard_Rating_#.
- Each Hazard Rating relates to precisely *one*, or *zero*, Procedures.
- A single Hazard Rating can not relate to *more than one* Procedure.
- A given Procedure may have zero, one, or more, Hazard Ratings.
Correct?

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Ok, so there are many Procedures, each identified by (unique) Procedure
Tag.
- Each Procedure has a unique Procedure Tag value.
- Two or more Procedures can not have the same Procedure Tag value.
Correct?
Yes, that is correct.


That table structure means the following:
- Each THA is uniquely identified by THA_number. Correct.
- Two more more THAs can never have the same THA_number. Correct.
- Each THA relates to precisely *one*, or *zero*, Procedures. Correct.
- A single THA can not relate to *more than one* Procedure. Correct
- A given Procedure may have zero, one, or more, THA's.
Correct?
No. my goal is to have *one* or *zero* THA's for each Procedure.


That table structure means the following:
- Each Physical Hazard is uniquely identified by Physical_Hazard_#. Correct.
- Two or more Physical Hazards can never have the same
Physical_Hazard_#. Correct.
- Each Physical Hazard relates to precisely *one*, or *zero*,
Procedures.

No. Each Physical Hazard can relate to precisely *one*, *zero*, or more
Procedures.

- A single Physical Hazard can not relate to *more than one* Procedure.

No. A single Physical Hazard *can* relate to *more than one* Procedure.

- A given Procedure may have zero, one, or more, Physical Hazards.
Correct?
Correct.


That table structure means the following:
- Each Bio Hazard is uniquely identified by Bio_Hazard#. Correct.
- Two more more Bio Hazards can never have the same Bio_Hazard#. Correct.
- Each Bio Hazard relates to precisely *one*, or *zero*, Procedures.

No. Each Bio Hazard can relate to precisely *one*, *zero*, or more Procedures

- A single Bio Hazard can not relate to *more than one* Procedure.

No. A single Bio Hazard *can* relate to *more than one* Procedure.

- A given Procedure may have zero, one, or more, Bio Hazards.
Correct?



That table structure means the following:
- Each Chem Hazard is uniquely identified by Chem_Hazard#. Correct.
- Two more more Chem Hazards can never have the same Chem_Hazard#. Correct.
- Each Chem Hazard relates to precisely *one*, or *zero*, Procedures.

No. Each Chem Hazard can relate to precisely *one*, *zero*, or more
Procedures.

- A single Chem Hazard can not relate to *more than one* Procedure.

No. A single Chem Hazard *can* relate to *more than one* Procedure.

- A given Procedure may have zero, one, or more, Chem Hazards.
Correct?



That table structure means the following:
- Each Work/Other Hazard is uniquely identified by Other_Hazards_#. Correct.
- Two more more Work/Other Hazards can never have the same
Other_Hazards_#. Correct
- Each Work/Other Hazard relates to precisely *one*, or *zero*,
Procedures.

No. Each Work/Other Hazard can relate to precisely *one*, *zero*, or more
Procedures

- A single Work/Other Hazard can not relate to *more than one*
Procedure.

No. A single Work/Other Hazard *can* relate to *more than one* Procedure.

- A given Procedure may have zero, one, or more, Work/Other Hazards.
Correct?
Correct.


That table structure means the following:
- Each Hazard Rating is uniquely identified by Hazard_Rating_#. Correct.
- Two more more Hazard Ratings can never have the same Hazard_Rating_#. Correct.
- Each Hazard Rating relates to precisely *one*, or *zero*, Procedures. Correct.
- A single Hazard Rating can not relate to *more than one* Procedure. Correct.
- A given Procedure may have zero, one, or more, Hazard Ratings.
Correct?
Correct.

Thanks for the quick reply.