Table comp[arison for duplicate and data increments

[osimini1]

dBforums > PC based Database Applications > Microsoft Access > Compairing tables for duplicate records and increment


LinkBack Thread Tools Search this Thread Display Modes

#1 (permalink) Today, 00:40
osimini
Registered User Join Date: Jun 2008
Posts: 23

Compairing tables for duplicate records and increment

--------------------------------------------------------------------------------
I believe somebody may have done this. I have two tables to compare for duplicates. Say 'tblA" and "tblB". Both tables have the same unique key (link). I want to cmpair both tables and if duplicate record is found, I will like to keep the record in tblA and increment the record in tbl with next available number. For example here is the sample table data: Please can some body direct me for solution

tblA:
id Field1 Field2
A10109 MC A10109
A10109 MC A10109Z
A10109 MC A10109Z2
A10109 MC A10109Z3
A10109 MC A10109Z4
A10113 MC A10113Z
A10113 MC A10113Z2
A10140 MC A10140Z
A10175 MC A10175Z
A10175 MC A10175Z2
A10217 MC A10217Z
A10223 MC A10223Z

tblB:
ID Field1 Field2
A10109 CO A10109Z1
A10109 CO A10109Z2
A10109 CO A10109Z3
A10113 CO A10113Z1
A10113 CO A10113Z2
A10175 CO A10175Z1
A10175 CO A10175Z2
A10175 CO A10175Z3
A10175 CO A10175Z4

 

[Andrzejek]

"Both tables have the same unique key" - which 'unique' key are you talking about? The only unique field is Field2.

"if duplicate record is found" in which table? In both?

"increment the record in tbl with next available number" - what do you mean by 'next available number'? And in which table: A or B?

With your sample data - which records are 'duplicates'?

Have fun.

---- Andy

 

[osimini1]

I am sorry not to identify the unique. Unique key is the "ID". Here are the fields. ID, Field1, Field2. For example field2 in both tblA and tblB may have the same data like ID=A10109, field2 = A10109Z3.Where thi is found, I want to keep the data in tblA as is and change field2 data in tblB to any available "Z"(Z1,z2,Z3,....Zn)suffix that is not found in tblA. for example like A10109Z3 will change or increase to A10109Z4. I hope this will help.

 

[Andrzejek]

Unique key is the "ID" - no, it is not. Not in tblA or tblB. It may be in another table, but it is NOT unique in those tables. But that's besides the point.

ID=A10109, field2 = A10109Z3:
tblA:
id Field1 Field2
A10109 MC A10109Z3

tblB:
ID Field1 Field2
A10109 CO A10109Z3 - change it to A10109Z4

"A10109Z3 [in tblB] will change or increase to A10109Z4"

So now you have :
tblA:
id Field1 Field2
A10109 MC A10109Z4 - exsisting record

tblB:
ID Field1 Field2
A10109 CO A10109Z4 - renamed record

You are back where you started - the same values in both tables.
Now what?

Have fun.

---- Andy

 

[osimini1]

I am sorry for not explaining myself very well. All records in both tables are linked to ID field such as A10109. This field may have many rows of data. Here are the fields. ID, Field1, Field2. For example based on Id field, field2 in both tblA and tblB may have the same data where ID=A10109, field2(tblA & tblB) = A10109Z3.Where this is found, I want to keep the data in tblA as is and change field2 data in tblB to any available "Z"(Z1,z2,Z3,....Zn)suffix that is not found in tblA. for example like A10109Z3 will change or increase to A10109Z4.

Thanks for your help.

 

[Andrzejek]

I see we are going to go round and round....

A10109Z3 is in both tables with ID of A10109. Because of that - in table B "A10109Z3 will change or increase to A10109Z4"

Now we have - again - the same record in both tables: A10109Z4 with ID of A10109. So we are back where we started from. And what if A10109Z4 is already in tblB with ID of A10109? Do you make two records with A10109Z4?

You need to specify your requirements, give an example of what you have when you start, and the example of what you want to have when you are done.

Have fun.

---- Andy