Validate table 1 to table 2

[mrdx07]

validate 1st table to 2nd table if 1st table records exist in 2nd table the record will not be recorded.(validate that say " the record is already exist in table2)

table1 and table2 has same filds

table1 fld
Week
Material
Material Description

table2 fld
Week
Material
Material Description

the thing is table1 should not have same records in table2 , need to validate. if i insert records in table1 the records should not be in table2. "Already exist in table2"



<<<<<<< T_T>>>>>>>>

 

[G81]

What is unique? The "Week" field? or a combination of all 3 fields?

How are you inserting the data, and what decides which table is used for the insert?

 

[mrdx07]

All 3 field shoud be unique to 2nd table.
inserting data via access table form. or any method just to validate the 1st table to 2nd table

 

[Galaxiom]

Your table structure is fundamentally flawed. These two tables should be a single table with another field to designate whatever attribute is currently used to separate the records into two tables.

Then the problem would not exist.

 

[G81]

I agree. You seem to be fighting against something which you shouldn't really need to be. Another problem you have is if any of these fields are free text, one spelling mistake, a different letter case, or an extra space would mean the fields in the 2 tables are not identical.

You might want to explain what you are trying to accomplish, then someone might have some ideas for a table structure.

If not, and you are sure there's no free input into the fields, maybe look at using a form, and a button with runs a query in vba such as:

"SELECT * FROM table2 WHERE Week = '" & txtWeek & "' AND Material = '" & txtMaterial & "' AND MaterialDescription = "' & txtMatDesc & "'"

Then if there aren't any results from the recordset (If rs.BOF and rs.EOF) then you are free to do an insert query. If there are results you can do all sorts of things, like display them, delete them, show a message on the form, etc.

 

[Galaxiom]

Another problem you have is if any of these fields are free text, one spelling mistake, a different letter case, or an extra space would mean the fields in the 2 tables are not identical.

Correct except for the difference in case. Access is case insensitive.

 

[mrdx07]

Thanks for the nice advice. I really appreciate it.