data comparison (wildcards?) (1 Viewer)

[crosmill]

I have two tables, where the data should be the same, however, both tables have been inputted independently of each other.
I need a way to compare the data in each table that will allow for these discrepencies.
eg. Company Name
Table 1
British Telecomunications

using "British *" will return from Table 2

British Telecomunications
British Telecom
British Gas
British everything

I want it to be able to recognise the similarities. I know it's a lot to ask form Access, but I'm hoping someone will be able to help.

 

[KDg]

gonna be tough, it depends on how different the entries are - there's nothing to stop you picking up "British Telecom*" that should get both, but matching "BT" and "British Telecom" etc is a bit more complex. If you really need that too then post back as i think it will need some code

HTH

 

[Rich@ITTC]

Hi crosmill

Dare I ask ... if you have two tables that basically have the same data then surely one of these tables should be deleted (as it only contains what the other table already has, in theory).

If you are designing a database from scratch this situation (the same data being stored in more than one table) is something to definitely avoid (for the very reasons you are now struggling with ... erroneous data, different versions of the same name, etc). If it is a database you have inherited then that's a bigger problem as first you have to iron out the discrepancies and only then could you combine the tables leading to the deletion of one of the two (but if you are going to use this database long term it is definitely worthwhile!).

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-28-2001).]

 

[crosmill]

Rich, thanks, the two tables both contain separate data so I can't just delete one.
the data has come from two seperate sources, hence the decrepencies and duplications!

KDg, thanks also, I'm not too bothered about things like "BT", I can do that manually, it's a ball ache I know but really I just want to get most of the dup's out of the way so I don't have to spend three weeks doing shit!
I have some experience of code but not a masive amount, so if you have a soulution I'd love to here it.

Thanks Guy(s)/Gal(s)

 

[Fornatian]

Just an idea - likely to be flawed but worth a read.

why not build an update query with Criteria as:
Like *British Tele*
and update row to
"British Telecommunications"

You would need to do this many times I suppose and the idea may fall foul of human assumptive errors.

The main problem with sorting this problem out is that you have set tolerance levels of how close data is to other data before you select it for automatic conversion.

I think if I was doing this task I would output the table to Excel and sort the data A-Z then put a formula in a column at the end of the dataset suchas:

=If(B3=B2,"","New Occurrance")

By dragging the formula down the dataset it will highlight each time a record value changes so you can see if it needs altering.

Then all you need to do is convert back into an Access table by re-importing the spreadsheet.

Ian

[This message has been edited by Fornatian (edited 09-28-2001).]

 

[crosmill]

Thanks Fornatian, as there are only likley to be a few instances of each duplication I think *Brit* etc will be a bit of work, but I think ploughing through it in excel might be the way.

Thanks everyone!