You will need two things
First, make sure mytable as a autoincrement column called ID
Next, place this SQL in the SQL View of a Query and run it:
SELECT Col_1,Col_2,IIF(DupKey=0,Col_3,Col3+TRIM$(STR$(ID))) as Column_3 FROM
(SELECT AA.*,
AA.ID - BB.ID as DupKey
FROM
(
SELECT A.*,B.ID,
IIF(DupCOunt=1,B.Col_3,B.Col_3+"Dup") AS Col3
FROM
(
SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
FROM mytable GROUP BY Col_1, Col_2, Col_3
) AS A, mytable AS B
WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
) as AA,
(
SELECT A.Col_1,A.Col_2,A.Col_3,A.DupCount,MIN(B.ID) as ID
FROM
(
SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
FROM mytable GROUP BY Col_1, Col_2, Col_3
) AS A, mytable AS B
WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
GROUP BY A.Col_1,A.Col_2,A.Col_3,A.DupCount
) as BB
WHERE AA.Col_1=BB.Col_1 AND AA.Col_2=BB.Col_2 AND AA.Col_3=BB.Col_3
) as AAA;
Give it a Try !!!
It will not make consecutive Dup numbers but it will assign unique Dup Numbers. This is too hard to get consecutive numbers without VB Code or another table
Create a new table that have all the columne in all your 5 tables, then create the needed SQL statment like SELECT * FROM Tb1 INTO TableWithAllColumns
eventually add some WHERE to exclude the duplicate rows from the insert.
This will work if your table doesent have similar rows.
If you need to merge similar rows together, there is no a simple way to tho this.
The best you can do is find, or write, a tool that can compare all the value in all the columns and act in the right way.
Best Answer
Just because [TownshipID] is populated (i.e. not null) and because there is a relationship defined between the two tables, these facts do not mean that the table [Townships] is open and positioned at any particular record. You must specifically direct Access to open the table and look for the related record. You do that in the Before Change Data Macro by using the LookupRecord action.
Within that action's group is where you place the SetField action.
Other comments:
Data Macros are best for validating data. They also have their place in keeping tables in sync as you are doing, but these conditions should be rare, especially if your tables are properly normalized.
It appears that Certificates.TownshipID is optional and can be null. I assume that means the county can be specified independently for the certificate when there is no township. Correct? If so, your technique is probably justified. Otherwise, there would be no need to also store the county with the certificate since it could always be retrieved via the Township relationship.
Since you already have a form where Certificate data is entered, you could also place relevant code in the form's module, for example in the TownshipID_AfterUpdate() event handler (or similar method) to retrieve and update the county information.