creating a query

[mandi]

Hi -
I have a table with the following fields
id
statement1
code1
statement2
code2
statement3
code3
statement4
code4
statement5
code5

I want to get all the records where code(1,2,3,4,or 5) is
equal to 1; however, I don't want the other fields to show
up if they are not equal to 1.

Example
id - joe
statement1 - XXXXX
code1 =2
statement2 - XXXXX
code2 =1
statement3 - XXXXX
code3 =4
statement4 - XXXXX
code4 =1
statement5 - XXXXX
code5 =4

I want to get id, statement2, and statment4 to show up
only (because their associated codes are equal to 1). How
do I do this in a query?

Thank you!

 

[John Vinson]

Hi -
I have a table with the following fields
id
statement1
code1
statement2
code2
statement3
code3
statement4
code4
statement5
code5

And what if you should ever need a SIXTH statement?

This table is improperly normalized, which is both limiting and also
makes your query much more difficult to write. I would strongly
suggest having TWO tables in a one to many relationship - the second
table would have fields ID (a link to your main table), StatementNo,
Statement and Code. Rather than adding five pairs of fields you would
add five *records*. With this, your query becomes trivially simple -
just use a criterion of 1 on the Code field.