Field Selection

[edwin.villamayor]

Based on "date" I want to return values that are three periods beyond
that date. For example, "Num" 123 has a date of 8/1/8. I want to
return the values found in fields "9/1/8", "10/1/8" and "11/1/8" or d,
x and x.

Num date 1/1/8 2/1/8 3/1/8 4/1/8 5/1/8 6/1/8 7/1/8
8/1/8 9/1/8 10/1/8 11/1/8 12/1/8
123 8/1/8 x x x x x
x x x d x x x
456 6/1/8 x x e e e
e x e x x x x
789 3/1/8 d d e c c
c c c c c c c

Output:
Num Result1 Result2 Result3
123 d x x
456 x e x
789 c c c

Any help would be greatly appreciated.

Thanks.

 

[Lord Kelvan]

you cannot do that because your table structure is wrong

yout table structure should be

num date resultdate resulttype
123 8/1/8 1/1/8 x
123 8/1/8 2/1/8 x
123 8/1/8 3/1/8 x
123 8/1/8 4/1/8 x
etc

then you can build the query but because you have done it as a date is
the field name you cannot to it unless you do it manually each time
you want to run it

as a note a date should never be a column a range of dates should be
though

you can try

select num,iif([num]=123,[9/1/8],iif([num]=456,[7/1/8],iif([num]=789,
[4/1/8],null))) as result1,iif([num]=123,[10/1/8],iif([num]=456,
[11/1/8],iif([num]=789,[5/1/8],null))) as result2,iif([num]=123,
[11/1/8],iif([num]=456,[12/1/8],iif([num]=789,[6/1/8],null))) as
result3
from thetable

AS A NOTE
this will have to be changed for whatever period you are working with
and it very poor table design i woudl invest you time in making you
table structure sound rather than reporting.

Regards
Kelvan