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!

 

[Andrew L.]

In query design, put [id] in the first column. then put the following in the next 5 columns:-

Iif([code1]=1,[statement1],"")
Iif([code2]=1,[statement2],"")

.... and so on, up to [statement5].

hth
Andrew L.

 

[Andrew L.]

In query design, put [id] in the first column. then put the following in the next 5 columns:-

Iif([code1]=1,[statement1],"")
Iif([code2]=1,[statement2],"")

.... and so on, up to [statement5].

hth
Andrew L.

 

[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.