I have a table (A) with a field in it with the address as a string.  There are 210,000+ records

 

In another table (, I have 3 fields that make up part of the basis of the address as per above (state, postcode, suburb).  This is an official list of postcodes and suburbs

 

What I would like to do is do some sort of pattern count that would identify where one of the suburbs appeared in the legacy field in Table A and replace the three separate fields (state, postcode, suburb) with the appropriate information.

 

i.e

 

Table A

Legacy Text = 28 Whatever Street, Brisbane QLD 4000

Suburb = {TBC}

State = {TBC}

Postcode = {TBC}

 

Table B

Suburb

State

Postcode

 

Link Table A to Table B and where a Suburb is found in the Legacy Text, insert Suburb, State, and Postcode as appropriate.

 

The address in the Legacy table can be a mix of data (i.e some don't have postcodes, have lots of rubbish written in etc etc.  So I need to only match where Suburb in Table B matches a word in Legacy Text in Table A.

 

I tried to do a join where PatternCount(Legacy Text; {Join Table B Suburb})  to no avail.

 

TIA

So I need to only match where Suburb in Table B matches a word in Legacy Text in Table A.

 

In order to do that, you need to split the words in Legacy Text into return-separated values and use the result as the matchfield in a relationship to the other table, opposite Suburb. Keep in mind, however, that a match on any word is a match:  for example, if a street name in Legacy Text happens to match a suburb name, you will have a false positive.

 

Note also that this assumes that each suburb is a single word.