Hi everyone,
I regularly import data into my DB and when it comes in there are always odd pairs of duplicate records. The import process is automated using SQL statements in VBA. At the moment I discard these duplicate records using indexes.
However I would now like to accept them which I have done, run a duplicates check which I can do with a duplicates query and then alter the second record of every duplicate to make it unique. The check for duplicates is done across three fields (NODE/LOOP/ADDRESS) and all I need to do is to add 0.1 to the third field (ADDRESS) on the second of each duplicate record to make it unique. Example below:
On IMPORT:
Table9
After duplicate check and value amendment:
Table9
Does anyone know a simple way in VBA to achieve this? I am familiar with update queries but not sure how to force it to only ever amend the second record of each duplicate as above?
Thanks
I regularly import data into my DB and when it comes in there are always odd pairs of duplicate records. The import process is automated using SQL statements in VBA. At the moment I discard these duplicate records using indexes.
However I would now like to accept them which I have done, run a duplicates check which I can do with a duplicates query and then alter the second record of every duplicate to make it unique. The check for duplicates is done across three fields (NODE/LOOP/ADDRESS) and all I need to do is to add 0.1 to the third field (ADDRESS) on the second of each duplicate record to make it unique. Example below:
On IMPORT:
Table9
Node | Loop | Address | Zone No | Device Text | Device Type | Device Info |
---|---|---|---|---|---|---|
1 | 1 | 1 | 1 | Corridor 999 Exit | Call Point | 6 |
1 | 1 | 2 | 1 | Corridor 999 Exit | Optical Smoke | 23 |
1 | 1 | 2 | 1 | Corridor 999 Exit | Sounder | 12 |
1 | 1 | 3 | 1 | Corridor 999 Adj Ramp | Optical Smoke | 23 |
1 | 1 | 4 | 1 | Corridor 037 Adj Ramp | Sounder | 12 |
Table9
Node | Loop | Address | Zone No | Device Text | Device Type | Device Info |
---|---|---|---|---|---|---|
1 | 1 | 1 | 1 | Corridor 999 Exit | Call Point | 6 |
1 | 1 | 2 | 1 | Corridor 999 Exit | Optical Smoke | 23 |
1 | 1 | 2.1 | 1 | Corridor 999 Exit | Sounder | 12 |
1 | 1 | 3 | 1 | Corridor 999 Adj Ramp | Optical Smoke | 23 |
1 | 1 | 4 | 1 | Corridor 037 Adj Ramp | Sounder | 12 |
Thanks