Match two tables without using join

[willybgw]

I am trying to select records from two tables where it is not likely there will be an exact match. The data from sales will be 1401 ELM STREET, from pifile it will be 1401 ELM.
Sales has 3,000 records, pifile has 58,000 records. The following query works fine, but takes about 20 minutes to return 2,700 records. Any suggestions to speed it up or make it more efficient.

SELECT PIFILE.PID, SALES.ADDRESS, PIFILE.[PROP ADDRESS], SALES.[MSALE DATE], SALES.[MLS SA], SALES.CONCESSIONS
FROM PIFILE, SALES
WHERE (((SALES.ADDRESS) Like [PIFILE.PROP ADDRESS] & "*"));

willybgw

 

[lespaul]

Is there ANY field that relates the two tables? An INNER JOIN will probably be faster than the cartesian join you are using.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[willybgw]

No that is the problem these are the only fields with any relationship.

willybgw

 

[MichaelRed]

you might TRY using the venerable "SOUNDEX" code with some hasty mods (to limit the string length and include numbers - although the latter might also need some thoughtful consideration.

Soundex code for VB(A) has been posted herein (Tek-Tips), find it using search.

You would also want to consider parsing the trash to seperate the parts of the field and join / search on the parts. e.g. seperate you examples into:

[tab]1401 | ELM | STREET

[tab][tab][tab] and

[tab]1401 | ELM

Then use soundex to convert "Elm" to a soundex code and join on both the "1401" and soundex("Elm")



MichaelRed

 

[randy700]

The following query works fine, but takes about 20 minutes

So, run that query and use it to update the tables. Put id fields in each table, populate the better of the two (use autonumber), then update the second table with the same field.

Once done, rebuild your query using the id fields and you should get much faster results.

Once complete, I'd strongly urge you to normalize your database.


Randy