How can I Link / Relate two tables using partial word match?

[izt_is]

Hey guys, I need some help here.

Basically right now I have 2 tables, 1 consits of a database I created with all the Postal Codes of my City for all the houses, searchable by their Building Name, Street Name etc.

This Query / Table is known as Search by Building Name; and contains he following fields:
Building Name
Postal Code
Street Name
No of Units
Address Type


Now i have this other compile table that is missing Postal Codes, But has Building Name and Street Name. This table is known as Test; and contains the following fields:
Building Name
Street Name
Test Number


Now basically I want to relate the Test Table with my Query Table, through the Building Name and Street Name fields, such that I can Query the Postal Codes easily.

Problem is, I have no idea how to make ACCESS 2007 do patial word mapping / search of Building / Street names from the Test table with that of Building / Street Names in the Query Table.

Does anyone know an SQL code string or some function in Access 2007 that can help me with this?

Thanks in advance!

 

[khawar]

What partial characters you want to match

 

[izt_is]

Basically Just building Name from table 1 with building name from table 2.

Problem is Building Names from table 2 aren't spelt exactly as in Table one.

For Example, The Kovan could be The Covan or just Kovan

 

[wiklendt]

sounds like you need to normalise a little...

 

[izt_is]

sounds like you need to normalise a little...

Normalise what do you mean?

My tables are structured correctly.

I just need to know how to do a partial mapping as seen in my above posts.

Any ideas ? Thanks!

 

[wiklendt]

Normalise what do you mean?

My tables are structured correctly.

I just need to know how to do a partial mapping as seen in my above posts.

Any ideas ? Thanks!

when i want to match abritrary strings, i make a query, then have a form with a text control. the query references the control thus:

Like "*" & Forms!frmSearch.txtSearchVal & "*"

(you can put that into each column you want. be aware, that the Like() oparand doesn't return nulls... so if you apply it to all your columns, they all have to have data in them - i hvaen't found a workouround yet, partly b/c i'm lazy but mainly b/c i'm too busy with other things)

the trick is that you can't reference the text box that you type into directly, you need to set the value of that textbox to another.

the textbox on the form that i call "txtSearchVal" is hidden. the one i type into is called "txtSearch".

and here's some code that makes it all flow - it goes into the txtSearch "on change" event, giving you real-time filtration ;

Private Sub txtSearch_Change()

    Dim vStrSearch As String
    
    vStrSearch = txtSearch.Text
    txtSearchVal.Value = vStrSearch
    
    Me.lstPatients.Requery

End Sub

this code was supplied by someone on this forum, but at the time i didn't save who i got it from. i cannot take the credit for this. i will someday soon trawl through my old threads and find who it was!

you can display the results of that query either in a listbox or a subform, which you have to remember to requery, as i've done (Me.lstPatients.Requery).

(edit...)
so, the end result is... if i have two records, one with kosovo, the other with cosovo, then i can search for "ovo" and it will return both....

hm. the only thing i just realised, though is that you are wanting to match different fields, whereas i just showed you a search for different records... ooops.

also, i mentioned normalisation because normalisation usually means you can avoid spelling errors, like you mentioned with kovan/covan - if each building had its own record in a tblBuildings table, you could simply select one of them from a drop-down in your form... but i don't know enough about your design to know how often these names will be repeated (at least once, judging from your post - and possibly more if it's a DB to track, say, computers in a complex of commercial buildings...)