search entire table and all fields

[Casmurbax]

I have a table that has the following: Joint Account, Employer, Employer1...Employer20, subemployer1, subemployer2... subemployer5, addemployer1, addemployer2... addemployer5, removeemployer1, removeemployer2....removeemployer5.

It might be possible that any of the columns with the word "employer" in them have the same number in them. The Joint Account column might have the repeating number in it as well.

I want to be able to push a button on a form open a input box and enter a 7 digit employer number and search through the whole table and return all the rows where that number particular number is.

I have tried building a query using "like" in the criteria but you can only use that 8 times.

I have searched through this forum, but I am unable to find something that I could use. As I m not a seasoned access user, I do not quite know what to do with some of the other "search" questions I have seen.

Thank you in advance for any help that could be given or pointing in the right direction.

John

 

[gromit]

Hi John -

Wow, you have some seriously "non-normalized" tables there. If you have the option to re-do the table so that it is normalized, it would probably help with this and a lot of other potential problems.

Is the use of the LIKE clause necessary? If the Employer Number is the whole field, then you can just search for matches on the appropriate 7 digit number.

Otherwise, try building up separate queries in smaller pieces, then you can use a summary query to gather those together (or even additional summaries).

Again, try to normalize the table if at all possible.

Hope that helps,

- g

 

[Casmurbax]

Hi Gromit,

thank you for the reply.

I have seen that answer "normalize" before, what does that mean? How do I go about to normalize my table?

As far as starting over, heck I can do that.

I do not need to use the Like clause, that was something that I saw on another post and thought I would give that a try.

Thank you for your help.

John

 

[gromit]

Normalizing the data means organizing your tables so that there is a meaningful structure and so that there is not unnecessary repetition.

Basically, each table in a database should be independent and consist of just the information about the specific records in that table. For example, if your database contains information about employers, employees and projects, then each of those items probably should be a separate table.

The way information is shared between tables is done by JOINS which is just a way of matching up the two tables. One very common idea you'll see are primary keys and foreign keys. A primary key is a unique index used to identify records in a table. Almost always, the best way to do this is to have an autonumber field that generates a unique number for each record. I.e. each employer has an EmployerID. Generally, it is best not to have any other meaning associated with the primary key (i.e. resist the urge to use some other piece of data, such as a tax id, as the primary key). The primary key for a table is always a field in that same table.

A foreign key is just a index to a record in another table. For example, you might have an Employee table (with its own primary key, e.g. EmployeeID). But you might want to link Employees with an employer, so an employee record might include EmployerID as a foreign key. But here it is just a number (not autonumber) and not even necessarily unique. An employee might have multiple employers (well for a temp agency or something).

For your post, the columns Employer1, Employer2, ... etc are an immediate indication that the db (database) is not normalized. The column should never be used to imply information. All the foreign keys to employers should be in the same column. Sometimes you have two tables (e.g. Employers and Employees) and may need a third table (e.g. TempAssignments) to specify the relationship between them.

You can't really normalize until you think a little about the data in your tables and how it will be used. Take a look at some of the following information and see how you might apply it.

Here are two links to normalization:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88
http://www.samspublishing.com/articles/article.asp?p=30646&rl=1

If you still have questions on normalizing, please feel free to post again (although I would recommend the Tables forum).

And just a note - the LIKE clause for a query is used for partial matches on text data: E.g. trying to find the name of a company from a word in the name, that sort of thing. But good on you for experimenting!

- g

 

[Casmurbax]

Gromit,

thank you for your help and suggestions.

I will look at the links you sent me and and implement them in a new table.

Thank you again for your time.