This is just a simple query questio 1

[BLutgen]

This is just a simple query question and I can't get it for the life of me. The name of the query that I am working in is "Panel No Code" There are a bunch of fields in the query but the only ones that matter are:
[no code]
[no code2]
[no code3]
[no code4]
[no code5]
[no code6]
[no code7]
All of these fields share the same options. They are from the same table and they all have the exact same values possible in a pull down menu from a value list. I am using a form(named: PanelNoCodefrm) with an unbound combo-box(named: no code) to set the search criteria, which has the same value list as the above fields. So back in the query, in the Criteria line for the no code's I have the following:
[Forms]![PanelNoCodefrm]![no code]
I know that this is the correct code to go get the information...I just must not have it in the right spot. I have it in every Criteria box for all seven "no codes" I need the user to be able to put in a code number(ex. 1000), and have the query bring back any code 1000, no matter which "no code" field it is in. How do I set up my criteria to do this? Thanks in advance if you can help.

Brad

 

[jflachman]

Although there are solutions to what you want to do, I think the major problem is in your table structure.

I assume you want to be able to keep track of codes for each item. There may be up to 8 codes, but not likely exactly 8 codes for each item. Is that true? If so, I'd adjust your table structure to look like this...

Table 1
Item ID (indexed, unique)
Field 1
Field 2
Field 3

Linked one-to-many (on Item ID) with:

Table 2
Item ID
Code

This structure will allow you to have 0-infinite codes with each item. You then use a subform to add and display codes for each item.
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[BLutgen]

Do you really think this is easier than just fidning a way to work the query? This is a rather large already existing database where there are many things tied to those fields I would have to change. Everything else works ok, I just need to add in this query. Any other ideas on the query?

Brad

 

[jflachman]

OK, in your Query design, criteria all on one row is ANDed together. Therefore in each column you need your criteria on different rows.

No Code No Code1 No Code2
=[Forms]...
=[Forms]...
=[Forms]... Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[BLutgen]

Thanks a lot for the help. I knew it was something simple I was missing. I tried putting one in the Criteria and the rest in the or field buy never went each one down individually. Sometimes my brain just goes dead. If I have another field for Criteria that I want to use to narrow down the query, say like a certain model number, I just ad the criteria to all seven rows in that field right? That is what I did and it seems to be working, I just want to make sure that I am not missing something. Thanks again.

Brad

 

[jflachman]

Yes, remember that everything on one row is AND'd with everything else on that row across all columns. Rows are OR'd with each other. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt