Search across multiple tables

[dfsabrown]

Hi

i was wondering if it is at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.

I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.

i have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.

so I just think that its not possible, could anyone advise?

many thanks

Sarah

 

[CJ_London]

but there are a limited number of rows on a query design so i run out of rows.

There are but if you switch to SQL view there is no limit (within reason)

A better route may be to use VBA to search each table and append the required results (tablename and recordID) to a temporary table

 

[dfsabrown]

Hi CJ_London
thanks so much for your response to this a year ago, I am finally getting around to adding this search as the database has been further developed. I have created a query with the fields i require but the results are incorrect and i get multiple results and it just doesnt really work.

Do you have any VBA code which would search my tables and return the results in a quick east format? I am trying a union query with the 2 different table but having trouble adding a search term to this.

Basically i need to have a search form (already created) which when a user enters their search term it search 2 different tables and provide the results in an easier way, like a table, if possible with just a few fields from the 2 table.

many thanks in advance for your and anyones help!

Dfsabrown

 

[CJ_London]

Sorry I don't have any vba code to do this but you could try something like this - NOT tested! It looks for string matches, you'll need to adapt it if you require matches on other datatypes. Note also there is no protection against spaces in table and field names

function findstring(byval str as string) as boolean
[COLOR=seagreen]'this assumes you have a table called tblFind with two fields TblName[/COLOR]
[COLOR=seagreen]'and RecID for the name of the table and the ID of the record where a match is found. [/COLOR]
[COLOR=seagreen]'If a match is found anywhere, findstring returns true otherwise false[/COLOR]
 
Dim db as database
dim tdef as tabledef
dim fld as field
 
findstring=false
set db=currentdb
 
for each tdef in db.tabledefs
    for each fld in tdef.fields     
        if fld.type=dbtext then
            db.execute("INSERT INTO tblFind (TblName, RecID) SELECT " & tdef.name & ", ID FROM " & tdef.name & " WHERE " & fld.name & " like '*" & str & "*'")
            if db.recordsaffected<>0 then findstring=true
        end if
    next fld
next tdef
set db=nothing
end function

 

[dfsabrown]

thanks very helpful

 

[jdraw]

db.execute("INSERT INTO tblFind (TblName, RecID) SELECT " & tdef.name & ", ID FROM " & tdef.name & " WHERE " & fld.name & " like '*" & str & "*'")

I think this line in CJ's post should really be

db.execute("INSERT INTO tblFind (TblName, RecID) SELECT " & fld.name & ", ID FROM " & tdef.name & " WHERE " & fld.name & " like '*" & str & "*'")

Good luck with your searching.

 

[CJ_London]

JDraw - no it is table name, so you know which table the record came from, but you have a point, so I would add another field FldName to tblFind and populate it with fld.name in the same query

 

[jdraw]

Ok. I see the logic, just saw there wasn't a field name in the select.