Is it possible with one sql query?

[btetsola]

Hello

I've been trying to put together several tables/queries in order to pull out data from the truncated table below. I was wondering if a Sql 'IIF' with 'switch' type query could do it in one go.

Parameters: If the [Actual Date] is Null and the [Month Slippage] is >0 then retrieve the row, if false, [Actual Date] is not Null and the [Month2 Slip]>0 then retrieve row.

Base Date Previous Date Current Date Actual Date Month Slippage Month2 Slip Previous DateArch Current DateArch Actual DateArch Month Slippage
14/01/13 05/06/13 10/08/13 2 2 05/06/13 10/08/13 17/07/13 2
16/02/13 13/05/13 11/07/13 26/09/13 2 13/05/13 11/07/13 26/09/13 2
17/02/13 25/02/13 29/02/13 0 3 25/02/13 16/09/13 06/12/13 0
19/02/13 12/03/13 19/04/13 26/04/13 1 1

Thank you

Benson

 

[dhookom]

Benson,
Welcome to Tek-Tips. Consider using TGML to format your sample data. I this example, I used the code tags to the data lines up. Also, your field names I believe contain spaces so we don't know where one name ends and another begins. Consider wrapping your names in []s so we understand. I'm not sure what you mean by "retrieve the row". Are you expecting to filter out specific records?

Base       Previous   Current     Actual       Month 
14/01/13   05/06/13   10/08/13         2	 2
16/02/13   13/05/13   11/07/13  26/09/13	 2
17/02/13   25/02/13   29/02/13         0	 3
19/02/13   12/03/13   19/04/13  26/04/13	 1

Duane
Hook'D on Access
MS Access MVP

 

[btetsola]

Hello Duane

Thanks for that. Sorry about the formatting issue. I'm afraid I'm not familiar with TGML formatting.

Yes I am trying to filter out certain records based on the multiple 'iif' criteria I mentioned.

Once again thank you for all your help

Benson

 

[Andrzejek]

When I look at your requirements, I see:
[pre]
[Actual Date] is Null and the [Month Slippage] > 0
[Actual Date] is not Null and the [Month2 Slip] > 0
[/pre]
So how about:
[tt]
SELECT ... From ... WHERE
([Actual Date] is Null AND [Month Slippage] > 0)
OR
([Actual Date] is not Null AND [Month2 Slip] > 0)
[/tt]

Have fun.

---- Andy

 

[btetsola]

Thanks for that Andy, I'll give it a whirl.