Matching Data

[Darrenc]

My problem is this:

I have a large table with about (8000+) records and a smaller table (2000+) records.
The large table has been exported from an ACT! database.
The smaller table has 4 fields that i need to add to the larger table, and then i need to import the updated records back into the Act! database.

I created a simple select query and matched the tables with the only 2 criteria that match the 2 tables, this was "Company" and "PostCode".

This should have been ok, but instead of updating 2000+ records it only updated 1000. The reason for this is because some of the company names weren’t an exact match, "company ltd" and "company limited" etc.

If i just linked "postcode" to "postcode" there are quite a few different companies that have the same postcode.

Is there a way of trying to match just the first 5 characters of the company name but leaving the "company" field intact?

Or is there a different way to go about this?

Thanks

Darren

 

[reclusivemonkey]

Is there a way of trying to match just the first 5 characters of the company name but leaving the "company" field intact?

CompanyInt5: LEFT([Company],5)

The above in your query gives you the first five characters from Company. You can then match on this and still use Company Name seperately.

 

[M8KWR]

in your query use expr1:left([table name]![company field name],5)

this will extract the first 5 characters of the company field.

In the criteria use left([other table name]![company field name],5)

then this will cross reference the 2 company name fields on the first 5 characters.

Do not link the 2 fields to one another like the postcode field, but do keep the postcode field linked.

This is just another way of doing the link between fields, but the drag and drop facility is quicker in the design window, but this way just give you more flexibility over what you need to match on

Hope this helps.

Any issues then please let me know.

 

[Darrenc]

Thanks M8KWR, worked a treat.