How to Combine Four Fields into One with a Query

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

I have a query that has four columns in it, along with others, that I
need to reduce into one.

The data could look like this:

PL1 PL2 PL3 PL4
20P 20P
40P
50P 50P 50P
30P 30P
(Could be null)
40P
10P

That is, each record will only have one value, never two different ones
in each field.

But I need a field to combine them into one. I want to get the PLAdj
field below:

PL1 PL2 PL3 PL4 PLAdj
20P 20P 20P
40P 40P
50P 50P 50P 50P
30P 30P 30P
(Should be null)
40P 40P
10P 10P

Nesting IIf statements would be a little much. Is there a function for
this type of thing?

Thanks,

Matt
 
I have a query that has four columns in it, along with others, that I
need to reduce into one.

The data could look like this:

PL1 PL2 PL3 PL4
20P 20P
40P ....
Nesting IIf statements would be a little much. Is there a function for
this type of thing?
Click to expand...

The Switch() function can be used here:

PLAdj: Switch(Not IsNull([PL1]), [PL1], Not IsNull([PL2]), [PL2], Not
IsNull([PL3]), [PL3], Not IsNull([PL4]), [PL4], True, Null)

either as a calculated field or in an Update query if you actually add
the PLAdj field to your table.

This might be a good idea - these four fields are improperly
normalized on two counts already!

John W. Vinson[MVP]
 
Back
Top