Combining Fields in a Query into Two Fields

[Guest]

I have search the newsgroup and found some related examples, but not matching
to what I need to do.
I have created Query1 from a table, and I am trying to do a union query on
Query1. Some fields are data and some are text. However, I am creating an IIF
expression on each field to provide a text result. Also, if the Field has a 0
or N value, I don't want to include it in my new query.

My Query looks like this:

RecordID Field1 Field2 Field3 Field4 Field5
123 -1 N N 2 0
124 -1 G L 0 2
125 0 N N 1 2


My Resulting Query should look like this:

RecordID Result
123 Good
123 Press
124 Good
124 Gas
124 Liquid
124 Fahrenheit
125 Temp
125 Fahrenheit


For my SQL statement I have written the following:

SELECT RecordID,iif([Field1]=-1,"Good") FROM Query1 WHERE ([Field1]<>0)
UNION ALL
SELECT RecordID,iif([Field2]="G","Gas") FROM Query1 WHERE ([Field2]<>"N")
UNION ALL
SELECT RecordID,iif([Field3]="L","Liquid") FROM Query1 WHERE ([Field3]<>"N")
UNION ALL
SELECT RecordID,iif([Field4]=1,"Temp",iif([Field4]=2,"Press")) FROM Query1
WHERE ([Field4]<>0)
UNION ALL
SELECT RecordID,iif([Field5]=1,"Celsius",iif([Field5]=2,"Fahrenheit")) FROM
Query1 WHERE ([Field4]<>0);

Thanks in advance.

 

[John W. Vinson]

I have search the newsgroup and found some related examples, but not matching
to what I need to do.
I have created Query1 from a table, and I am trying to do a union query on
Query1. Some fields are data and some are text. However, I am creating an IIF
expression on each field to provide a text result. Also, if the Field has a 0
or N value, I don't want to include it in my new query.

My Query looks like this:

RecordID Field1 Field2 Field3 Field4 Field5
123 -1 N N 2 0
124 -1 G L 0 2
125 0 N N 1 2


My Resulting Query should look like this:

RecordID Result
123 Good
123 Press
124 Good
124 Gas
124 Liquid
124 Fahrenheit
125 Temp
125 Fahrenheit


For my SQL statement I have written the following:

SELECT RecordID,iif([Field1]=-1,"Good") FROM Query1 WHERE ([Field1]<>0)
UNION ALL
SELECT RecordID,iif([Field2]="G","Gas") FROM Query1 WHERE ([Field2]<>"N")
UNION ALL
SELECT RecordID,iif([Field3]="L","Liquid") FROM Query1 WHERE ([Field3]<>"N")
UNION ALL
SELECT RecordID,iif([Field4]=1,"Temp",iif([Field4]=2,"Press")) FROM Query1
WHERE ([Field4]<>0)
UNION ALL
SELECT RecordID,iif([Field5]=1,"Celsius",iif([Field5]=2,"Fahrenheit")) FROM
Query1 WHERE ([Field4]<>0);

An IIF statement has THREE arguments - you're giving it two. The first
argument is an expression which is True or False; the second is the
value to return if it's True; the third is returned if it's False. You
specify that the first UNION query SELECT should return "Good" if
Field1 is -1 - but what should it be if Field1 is (say) +3? It's
similar for the others.

What do you want to see if the fields have SOME OTHER value than the
one in the IIF?

John W. Vinson [MVP]

 

[Guest]

Thanks for your response. I forgot to explain more on the fields. The field
contents and type will be restricted to the values shown below.

Field1 is Yes/No field
Field2 will only have a "G" or a "N". It is a text field.
Field3 will only have a "L" or a "N". It is a text field.
Field4 will only have a 0, 1, or 2. It is a number field.
Field5 will only have a 0, 1, or 2. It is a number field.

Since I am excluding the No, "N", "N", 0, and 0 from fields 1-5
respectively, I am not adding the third criteria in the IIF expression.

My questions are:
1. Does it matter that I have different field types? However I am returning
my expressions as a Text field.
2. Am I in the right track?

Thanks again.

I have search the newsgroup and found some related examples, but not matching
to what I need to do.
I have created Query1 from a table, and I am trying to do a union query on
Query1. Some fields are data and some are text. However, I am creating an IIF
expression on each field to provide a text result. Also, if the Field has a 0
or N value, I don't want to include it in my new query.

My Query looks like this:

RecordID Field1 Field2 Field3 Field4 Field5
123 -1 N N 2 0
124 -1 G L 0 2
125 0 N N 1 2


My Resulting Query should look like this:

RecordID Result
123 Good
123 Press
124 Good
124 Gas
124 Liquid
124 Fahrenheit
125 Temp
125 Fahrenheit


For my SQL statement I have written the following:

SELECT RecordID,iif([Field1]=-1,"Good") FROM Query1 WHERE ([Field1]<>0)
UNION ALL
SELECT RecordID,iif([Field2]="G","Gas") FROM Query1 WHERE ([Field2]<>"N")
UNION ALL
SELECT RecordID,iif([Field3]="L","Liquid") FROM Query1 WHERE ([Field3]<>"N")
UNION ALL
SELECT RecordID,iif([Field4]=1,"Temp",iif([Field4]=2,"Press")) FROM Query1
WHERE ([Field4]<>0)
UNION ALL
SELECT RecordID,iif([Field5]=1,"Celsius",iif([Field5]=2,"Fahrenheit")) FROM
Query1 WHERE ([Field4]<>0);

An IIF statement has THREE arguments - you're giving it two. The first
argument is an expression which is True or False; the second is the
value to return if it's True; the third is returned if it's False. You
specify that the first UNION query SELECT should return "Good" if
Field1 is -1 - but what should it be if Field1 is (say) +3? It's
similar for the others.

What do you want to see if the fields have SOME OTHER value than the
one in the IIF?

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks for your response. I forgot to explain more on the fields. The field
contents and type will be restricted to the values shown below.

Field1 is Yes/No field
Field2 will only have a "G" or a "N". It is a text field.
Field3 will only have a "L" or a "N". It is a text field.
Field4 will only have a 0, 1, or 2. It is a number field.
Field5 will only have a 0, 1, or 2. It is a number field.

Since I am excluding the No, "N", "N", 0, and 0 from fields 1-5
respectively, I am not adding the third criteria in the IIF expression.

You misunderstand how criteria work. If you exclude N from ANY field,
Access will reject *that entire record*. A record in a query is either
included in the query in its entirity, or rejected in its entirity.

An IIF() function with two arguments *SIMPLY WILL NOT WORK*. It's
invalid syntax. If you have an IIF, it must have all three.

My questions are:
1. Does it matter that I have different field types? However I am returning
my expressions as a Text field.

Doesn't matter. The true/false portion of the IIF can use any
datatype, just so you're comparing it with an expression of the same
datatype - for instance, an IIF comparing a field with a Number
datatype to the strings "N" or "Y" will not work correctly (it will
always return the False branch).

2. Am I in the right track?

I'd do it differently, actually. You don't need the IIF's *AT ALL* if
you simply select the desired value or values. Try

SELECT RecordID,"Good" AS Expr1 FROM Query1 WHERE ([Field1]=-1)
UNION ALL
SELECT RecordID,"Gas" AS Expr1 FROM Query1 WHERE ([Field2]="G")
UNION ALL
SELECT RecordID,"Liquid" AS Expr1 FROM Query1 WHERE ([Field3]="L")
UNION ALL
SELECT RecordID,Choose([Field4],"Temp","Press") AS Expr1 FROM Query1
WHERE ([Field4] IN (1,2);

John W. Vinson [MVP]