Remove duplicate information in different fields

[Garry]

Hi all

I have an address file with 50k records

The fields are Add1, Add2, - Add6, Postcode

The postcode field has such information but this information is also
duplicated in various add fields

How do I go about removing postcode data from add fields so the do not print
on address labels twice

thanks for any input, Garry

 

[Michel Walsh]

I would make the test, before printing:

if postcode = add1 OR postcode = add2 OR ... OR postCode = add6 then
' do nothing
else
' print the postcode
end if



You can also update the table (untested, make a backup before running such a
massive update) :

UPDATE adds
SET add1=iif(add1=postcode, null, add1),
add2=iif(add2=postcode, null, add2),
....


It assumes the add_ fields can take a null value.




Hoping it may help,
Vanderghast, Access MVP

 

[Garry]

Thankyou Michel I will put it to the test

One more thing, what if the add fields contain say "London" or "Edinburgh"
and then the postcode in the same field

Cheers, Garry

 

[Michel Walsh]

You have to use the operator LIKE, in that case:

UPDATE adds
SET add1=iif( add1 LIKE "*" & postalCode & "*", Replace(add1, postalCode,
"" ), add1) ,
add2=iff( add2 LIKE "*"& postalCode & "*", Replace(add2, postalCode,
"" ), add2 ),
...


Note that you can end up with a zero length string, rather than with a NULL,
as it was previously suggested with a simple = test.




If you prefer to test at the printing step:


if eval(" (add1 & add2 & add3 & add4 & add5) LIKE '*' & postalCode & '*'
") then
'do nothing, already printed
else
'print the postal code
end if




Vanderghast, Access MVP