DUPLICATE DATA BEING ENTERED INTO A TABLE

[Kevsim]

I have a Form with 3 combo boxes, each combo box is based on a set list. The Form is based on a Query. The Query is based on 3 tables, combo box 1 provides data for table 1, combo box 2 provides data for table 2, combo box 3 provides data for table 3.
By doing it this way, only set data can be entered into the respective tables. My problem is as follows – If I select data from combo box 1 for table 1 and the data already exists, when I enter all data and go to update the Query, an error message appears telling me there is duplicate data. If there is duplicate data going to be being entered, you do not know which table is being referred too. Should it be table 1, can this be ignored when updating then pass on to table 2, should table 2 have duplicate data, pass on to table 3. If table 3 has duplicate data the program runs as normal.
I do not want duplicate data in the tables but still want set data to be entered.
I would appreciate any help.
kevsim

 

[mdav2]

Is there any validation preventing any of the data being entered on any of the tables (ie duplicate values).

Reading what you have put doesn't make any sense to me. If you could post exact details of the table designs, query design and form then it may be easier to fix this.

 

[Kevsim]

mdav
Thank you for your reply.
My structure is as follows-
Table 1- T1ID, T1_Contents. Table 2- T2ID, T1ID, T2_Contents. Table 3- T3ID, T2ID, T3_Contents. Which link to the main table, Table 4- T4ID, T3ID, T4_Contents.
I have 3 set lists which contain standard Products, Assemblies and Parts, L1, contents for Table 1, L2, contents for Table 2, L3, contents for table 3. The reason for the 3 lists being, they only contain standard information, one of each Product, Assembly and Part but require the correct linking through the tables. Table 1 is for Products, Table 2 Assemblies for the product selected in Table 1, there could be hundreds of assemblies. Table 3 Parts for the Assemblies selected from Table 2 and associated with the product selected in Table 1, there could be hundreds of parts. When the data has been liked to tables 1, 2, and 3 it is then further linked to Table 4 which contains data associated with the selection of data from Tables 1, 2 and 3, there could be thousands of cobinations in this table. I can add data to Table 1, then switch to Table 2, add data, switch to Table 3 add data, this part works OK. What I am trying to achieve is instead of updating 1 table at a time, I do it all from a Query, Query 1, which includes all the tables. In Query 1, Select L1 for the Product which goes to Table 1, L2 for the Assemblies which goes to Table 2 and L3 for the Part, Which goes to Table 3, when the Query is updated, if any of the Tables already have the data, the input to that table is ignored, for example, the data exists in Table 1, it then switches to Table 2, if the data does not exist in Table 2 it is then entered, then switches to Table 3.
Table 1 contains one of each type of Product, Table 2 contains many Assemblies which could be associated with the Product, Table 3 many Parts which could be associated with each Assembly. I could have drop down boxes in the Query to check if the data exists and if not a button to open the table and update, I was trying to avoid this.
Hoping this explains the situation and you can assist.
kevsim

 

[JoanneM]

Hi kevin

Phew that last one was one to read slowly

heres my two pennorth worth

What you are doing here screams out for a junction table which Holds T1_id, T2_id, T3_id: All being required and this can be added to f you want to prevent duplicates
make it a composite key, then when you do a validation
once populated
The end result would be a query containing all four tables

T1_id linked to junctionTbl on T1_id
T2_id linked to junctionTbl on T2_id
T3_id linked to junctionTbl on T3_id

This would then produce a dataset that would, if we drill all the way down

All parts need to perform assembly
All Assembly processes needed for the product
All the products in the list that have had data entered

So a report could then group on product
Sub group on assembly
And list parts needed for each assembly process.

As it appears you only have 3 fields to deal with I would suggest the composite key approach in the junction table and quick msg informing of -ve or +ve result of input.

regards jo

I hope this is on the right track

 

[Kevsim]

JoanneM – Thank you for the information, greatly appreciated. There is still a little problem, I am still just finding my way around Access and still finding problems.
Your explanation of a Junction Table, I looked in the manuals and could not find any reference to it, could you please explain on how do it . Also what is a Composite key?
I do not expect you to do it for me but would appreciate a little more info to get me on my way.
Once more, thank you for the info already provided.
Kevsim