Design query in Access 2010 to search multiple fields from a single parameter input

[CTVT]

I have an Access 2010 DB that has multiple fields with various information in them. An example. Item 1 Item 2 Item 3 ~~~~ item 25. All the information in these fields have numerous data in them from a chrome plating shop. They could include, but not limited to, bumpers, radiators, wheels, posts, hood, valve covers, bezels, handles, brackets, grill, etc. The list is very extensive. Each record has a name, company, type of auto, or other associated with the Item 1 through Item 25 field. What I need to do is design a query so a parameter can be used as an input to search the 25 fields for any one of the items in the field. Example. What item do you need? What is name of company, customer, or type of auto associated with the part chromed. If you wanted a bumper for a 1960 chevy corvette, everything is easy to find except which field 1~25 has the word or word in a phrase = to bumper. I have tried many ways to design the query by design view or SQL and just can not get it to work properly. I have very successfully found what I needed to find the item if it is in Item 1, however is it is in item 2~item 25 I can not find it. I have tried using the same criteria on the other fields ( item 2 ~ 25) but when I do this the AND logic seems to eliminate the item I need to find. The items were not entered in any specific order when the database (Access 2000) was first set up for it. The employee might have entered a bumper as the first item one day and maybe entered a grill as the first item the next day. As a result Item 1 of one record does not line up with Item 1 of another record, however Item 5 might. The database has about 5,000 records and I do not even want to think of a redesign as I do this on a voluntary basis for my friend. Any help, examples, or other info much appreciated.

[pbaldy]

Well, the design is incorrect (not normalized). That said, if you don't want to fix it you'd have to put each field's criteria on a separate line in design view. In SQL view, that changes AND to OR.

[orange]

CTVT,

Take a look at this sample code.
and this thread

[CTVT]

I did put the search criteria on separate lines. When I did that the logic fell through. I searched for a radiator on a ford car, looking for the radiator in Item 1, Item 2, etc. The results found more radiators but it also showed cars like pontiac, plymouth, etc.
I think that is because the next line down OR looks for a radiator but does not look for only a ford. Since it found a radiator under the OR logic it put it on the screen. Only the car (ford) that had a radiator in Item 1 displayed properly. I think I have a can of worms here. Is there any way I can send you a screen shot of my logic in the database?

[pbaldy]

You should be able to attach it here in the "Go Advanced" area. You certainly have a can of worms because of the design flaw.

[CTVT]

You should be able to attach it here in the "Go Advanced" area. You certainly have a can of worms because of the design flaw.

Here is the image of how my logic in the query looks.
Any suggestion or help much appreciated..
Do you think there would be any easy??? way to change and modify this database to get what my friend needs?
The problem is that each Item 1 to Item 25 field has so much different data in them. Almost a hodgepodge
of items. However, in talking to him he states that there is no set type of item that would be stored in them.
They have about 200-400 different types of item they receive and chrome plate. Item 1~~25 are the fields that
store these items. It may be beyond me to figure out the best method to fix this Elephant. Customer names, companies,
phone numbers, addresses, make, model, year, type of finish, quality, all are easy to search and manipulate. It's
just those darn Item fields with a multitude of data that give the problem.

Thanks for any assistance

[orange]

Did you look at the links I suggested?

[CTVT]

Did you look at the links I suggested?

Yes but they did not seem to be what I was looking for.
Thanks for your info.
I am corresponding with another person on the forum and I think I am working my way through it.
Again thanks.

[pbaldy]

Personally I'd probably fix the design, but not knowing the app I can't say how involved that would be. The quick solution is to repeat the make/model/year criteria on each row where there's an item criteria. In other words, in your example they would be repeated on all 4 rows.

[CTVT]

Personally I'd probably fix the design, but not knowing the app I can't say how involved that would be. The quick solution is to repeat the make/model/year criteria on each row where there's an item criteria. In other words, in your example they would be repeated on all 4 rows.

I tried what you suggested and it still gave me other makes than what I asked for (Ford). I don't even want to say this but I may need to redesign the whole thing. I can see tables of customers, vehicles, parts, finish, order detail, and of course they also do non-automotive plating as well. That might need a nonauto table. Then there is the matter of moving all the data from about 5000 records to a new DB. Is there a website or really good book you can recommend to help me study
this out before I proceed? A big problem that started this was a DOS based database designed about 12 years ago that was imported into Access 2000. If I remember it only let you use one table for the database. And it would let you see only what would fit on a single screen (about a 12" monitor at the time.). No scrolling or min/max windows. Thanks for all your help and info.

[pbaldy]

What did the query look like in design view when it did that?

[CTVT]

What did the query look like in design view when it did that?

I attached a screen shot of how the query looked after your suggestion on the 23rd. When I ran it I got more items but also it did not limit the make to a ford.
It found dodge, pontiac, etc. which is fine but it needs ti limit the search to the exact criteria. Access is sometimes very hard to figure in its exact logic of how
it looks at, and in what order, when it checks criteria.

[pbaldy]

I would expect that to work, presuming there aren't other item fields. Can you attach the db here?

[CTVT]

I would expect that to work, presuming there aren't other item fields. Can you attach the db here?

Actually I have been checking and modifying the DB to explain to you what I am having trouble with. As I was checking through the
various queries and forms, I could not find anything really wrong. I must have changed something that corrected a mistake.
If you would like I can still send you the DB if you want to look at it and see if I have any serious problems (other than its overall design).
Let me know.
Thanks so very much for your help and advise.

[orange]

Do a compact and repair, then zip a copy of your database and attach.