Searching a database in MS ACCESS

[Guest_imported]

I am designing a database where you can have more than one field for each piece of information, eg someones qualifications. How can I search my database from one field and at the same time looking in all four or so fields of which I have my data in. I tried a filter but it didn't include the rest of the data from the other fields. Can you help me here.<br><br>Thanks Jonathan

 

[AoxomoxoA]

Jonathan,<br><br>Not sure what exactly you mean by multiple fields in the database, I am presuming that you have a single table which contains repeating field information as in the following structure:<br>&nbsp;&nbsp;&nbsp;&nbsp;Person(PersonID, Qualif1, Qualif2, Qualif3..)<br>An example record may then look like this...<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JohnSmith, BA, MBA, MS, Phd <br>You would like to find out if the word MBA exists in either of the 4 fields. If so, the best way is through a Select Query with an criteria for each of those fields. Use an OR criterion(criteria on different rows for each of the columns).<br><br>If I've read you wrong, then please e-mail me with more details and I would be happy to offer you suggestions<br><br>

 

[Guest_imported]

You read it right, and I am going to try what you have suggested, thanks very much<br><br>Jonathan

 

[Guest_imported]

One more thing, if I was to do a select query how would I do this on a form because I would have liked to have a filter search for the rest of the form which doesn't contain more than one field of data and then do the select query for the rest of it, is this possible?<br><br>Hope you can help<br><br>Jonathan

 

[Guest_imported]

Okay lets put it this way, I want a form which acts like a search engine. So when you type in information it will show a near or exact match, that is why I used a filter to start off with. Two things:<br><br>1 - can you incorpreate the filter with the query, and if so how?<br><br>2 - how do you create the query so that the user can type in the criteria on the form and in turn will check all four fields?<br><br>Does this explain things better<br><br>Thanks

 

[Blitz]

<br>Well I can show you how to do #2 but I would have a hard time putting it all in words. If you email me at <A HREF="mailto:seth@mpihoa.com">seth@mpihoa.com</A> I can send you a file that will show you how.

 

[AoxomoxoA]

Jonathan,<br><br>Use a parametric query. In the criteria row of each of the fields you want to search for, type in something surrounded by [ ], for example...[Enter a qualification]. When the query is run, the user will be prompted to enter a value and this value will then become the actual criterion value for the query. Make sure that you type it in exactly the same way in all the fields you are searching for.<br><br>To have this implemented as a form, create a form based on the query. Every time the form is opened the query will be run and the user can type in the actual value (MS, MBA etc as discussed in our hypothetical example).<br><br>However in order to rerun the query for a different criteria, you need to requery the form. To do this...<br>1. Create a command button on the form header or footer<br>2. In the click event of the command button, write the following line of code:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.Requery<br>Then whenever the user clicks on the button, the query will be re-run, the user can type in new criteria and the form will redisplay the values with the new criteria<br><br>This is a crude, but workable solution. Relational database design frowns on repeating fields in a single table, the table has to be split into two with the repeating fields implemented in a separate table and linked on a common field.<br><br>In order to effect more elegant search UIs such as a combo box, a little more coding is required. Not sure what level of VBA coding you have.

 

[Guest_imported]

Thanks, i'll try that

 

[Paladin2]

Something you might want to htink about is making a table for the multiple occuring fields, then each is only an occurance of the table and you can have as few or as many as needed for each instance.&nbsp;&nbsp;This also makes the data easier to manupilate and query as well as saving space in the dataase.