Seaching Tables

[philljp390]

Hi all,


I'm not sure if this should just be done in a query, or VBA.

I have a table called Address and a table called Incidents.

I want to search through the fields Address 1, 2, 3, and 4 in the Incidents table to see if they match the fields Address 1 in the Address table. Each address in the Address table has a corrisponding Grid Reference, so the results would be:

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFrom Incidents Table &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFrom Address Table
|-----------------------------------------------| |---------------------|
&nbsp&nbsp&nbspAddress 1 - Address 2 - Address 3 - Address 4 - &nbsp&nbsp&nbsp&nbsp&nbsp&nbspGrid Reference


So when it matches the address it gives the Grid Reference

Any help appriciated.

 

[Jack Cowley]

If your tables are properly normalized and related then a query should do the trick. It would appear from your example that the adressess are not normalized, but I am not sure. If there can be multiple addressess for a record then they should be in a separate table rather than fields in your current table.

Jack

 

[philljp390]

There are 4 Address fields because it goes something like:

Address1 - Street Name
Address 2 - District
Address 3 - City
Addess 4 - County

How do u refer to a table and search a column its data in VBA?

 

[dcx693]

So are you saying that any of those 4 fields in the Incidents table could make the single column in the Address table? If so, this is possible with a query.

 

[philljp390]

Thing is, it will get more complicated because I want to give an accuracy level depending on how good the match is. Like if the street name is matched give it a 1, if only the county is matched it would be given a 4. (So areas given a '1' therefore have accurate grid references down to the particular street).

Any ideas on how to code this please?

 

[dcx693]

Easiest way would be to use a query with a custom VBA function that would assign your match a value based on accuracy level.

If you've never coded a custom VBA function, you should check out the online Access help, it's not that bad.

I'm guessing that your single field in your address table could contain any type of address information including street, city, district or county. You need to decide what kind of weighting you want to use. Investingate the use of the Instr() function or the Like operator for finding strings within other strings.

 

[philljp390]

Would u code the VBA as a Module? I have gotten the easy part to work with a query using the Like, but how do u refer to a table and search a column of its data in VBA?

 

[WayneRyan]

Phil,

Dim dbs As Database
Dim rst1 As RecordSet
Dim rst2 As RecordSet
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from Incidents Where IncidentID = " & Me.ID
Set rst1 = dbs.OpenRecordset(sql)
'
' Go through all incidents
'
While Not rst1.EOF and Not rst1.BOF
   sql = "Select * from Addresses Where Address ='" & rst1!Address1 & "'"
   Set rst2 = dbs.OpenRecordset(sql)
   If Not rst2.EOF and Not rst2.BOF Then
      MsgBox("We've found a match, level = 1")   
      Exit Sub
   End If
   sql = "Select * from Addresses Where Address ='" & rst1!Address2 & "'"
   Set rst2 = dbs.OpenRecordset(sql)
   If Not rst2.EOF and Not rst2.BOF Then
      MsgBox("We've found a match, level = 2")   
      Exit Sub
   End If
   sql = "Select * from Addresses Where Address ='" & rst1!Address3 & "'"
   Set rst2 = dbs.OpenRecordset(sql)
   If Not rst2.EOF and Not rst2.BOF Then
      MsgBox("We've found a match, level = 3")   
      Exit Sub
   End If
   sql = "Select * from Addresses Where Address ='" & rst1!Address4 & "'"
   Set rst2 = dbs.OpenRecordset(sql)
   If Not rst2.EOF and Not rst2.BOF Then
      MsgBox("We've found a match, level = 4")   
      Exit Sub
   End If
   Wend
MsgBox("No matches on any address.")

Wayne

 

[philljp390]

Thanks wayne,

but an error at the first hurdle:

got an error here:

Dim dbs As Database

"User-defined type not defined"

 

[Jack Cowley]

I assume you are using Access2000 where ADO is the native code and not DAO. To get DAO to work in Access2000 and above make the following changes:

Dim dbs As DAO.Database
Dim rs1 As DAO.Recorset
Dim rs2 As DAO.Recordset

From the Code Editor select Tools > References. Scroll down until you find Microsoft DAO 3.6 Object Library and check it.

hth,
Jack

Wayne - Thanks for doing all the hard work!

 

[philljp390]

Yes i am currently running Access 2000 to test this, but it will be running in Access 97. Will the first code work ok in 97?

 

[philljp390]

Another error here:

sql = "Select * from Incidents Where IncidentID = " & Me.ID

Method or Data member not found

 

[WayneRyan]

Phil,

The code is "native" to A97. Two things to point out are that
(a) the code doesn't do anything, it does not set anything in
the Incidents table or the Address table. And (b) there are
more efficient ways to make this work. If the Incidents table
had a sub-table for addresses, you would not need any
code at all. A very simple query could do this.

Thanks Jack, I specialize in "linear", one-dimensional code.

Wayne

 

[Jack Cowley]

Philljp390 -

Me.ID assumes you have a control on your form called ID that contains the Incident ID...

Also, look into normalizing your data as suggested by Wayne (the linear code guy) as it will make getting the answer you are looking for much easier...

Jack