| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > SIngle field entry for " what re you looking for" that will search 6 fields in database to find values that comntain parts of the info and return the records
Using MS Access - SIngle field entry for " what re you looking for" that will search 6 fields in database to find values that comntain parts of the info and return the records
Expert: Scottgem - 11/4/2009
Question
I have a very simple database that has a table that contains 10 fields. I would like to set up a search where the person can enter a value in a field ( eg.. what are you looking for? (value Mechanical) and then with the push of a button (search) the system will look at the information ( mechanical) and if there is like information in a record in any of 6 fields return the record for them in a form view that I have set up that they can look at the information from.
The information that is being entered for the lookup does not have to be an identical match in each field, just contain the information.
Or is there an easier way that I am not seeing?
Answer
The easier way would require a redesign of your database. If you have data fields in your table that refer to similar data, that is know as a repeating group and violates normalization rules. I suspect you should have TWO tables. Table one would have information specific to the entity you are tracking. Table two would look like this:
RecordID (Primary Key Autonumber)
ParentID (Foreign Key to identify record from Table One)
DataType (To identify the type of data; i.e. the field names for those six fields)
Value
From there it is easy to create a query that will search through the Value field and filter the form for you. Something like this:
SELECT DISTINCT ParentID, Value
FROM table2
WHERE Value Like "*" & Forms!formname!controlname & "*";
Where formanme is the name of your form and controlname the name of the control where you enter the search string.
The RecordSource behind your form would have a WHERE clause like this:
WHERE PK IN(SELECT ParentID FROM queryname;)
where queryname is the name of the first query.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
This will return
Add to this Answer Ask a Question
|
|
