MULITPLE IF STMNT EASIER

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5], [CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9], [TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
 
You could put it all in the first expression list. Something like:

IIf([CATEG_1]="STEP UP" or [CATEG_2]="STEP UP" or [CATEG_3]="STEP UP","YES",
"NO")

--Grey
 
Yes, I do know how to make it easier.

The first step would be to restructure your tables to normalize the data.
Then you could query only one field to find if one of the categories
contained "Step Up".

tblEmployee
EMPNUM
SDATE
EDATE

tblEmployee_Category
EMPNUM
CategoryID

tlkpCategory
CategoryID
CategoryName
 
Hi,


or

CBool( "STEP UP" IN ( Categ_1, Categ_2, Categ_3, Categ_4, ...,
Categ_10 ) )



Hoping it may help,
Vanderghast, Access MVP


GreySky said:
You could put it all in the first expression list. Something like:

IIf([CATEG_1]="STEP UP" or [CATEG_2]="STEP UP" or [CATEG_3]="STEP
UP","YES",
"NO")

--Grey

jeanne said:
Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5],
[CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9],
[TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
Click to expand...
Click to expand...
 
This table is downloaded directly from a system. So I am unsure how to break
it out into different tables without manually doing that. Since this will be
a weekly report the download will be done weekly. Is there an automatic way
to "normalize" a table?

[MVP] S.Clark said:
Yes, I do know how to make it easier.

The first step would be to restructure your tables to normalize the data.
Then you could query only one field to find if one of the categories
contained "Step Up".

tblEmployee
EMPNUM
SDATE
EDATE

tblEmployee_Category
EMPNUM
CategoryID

tlkpCategory
CategoryID
CategoryName

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

jeanne said:
Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5], [CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9], [TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
Click to expand...
Click to expand...
 
This table is downloaded directly from a system. So I am unsure how to break
it out into different tables without manually doing that. Since this will be
a weekly report the download will be done weekly. Is there an automatic way
to "normalize" a table?
Click to expand...

Not trivial but not too hard. It's sort of a tossup whether it will be
beneficial; if you're going to have to overwrite all your data weekly,
it's going to be a bit of a chore!

If this is the only query you need, you've gotten some good
suggestions about how to search across the 18 fields (and I learned
about the neat CBool() function, thanks Vanderghast!). But if you have
other search needs it may be worth doing the normalization.

To do so, have an empty set of normalized tables as suggested by
Steve. You can run a couple of queries in succession: a Delete query

DELETE * FROM tblEmployee_Category;

to empty out the table, followed by an Append query based on a
"Normalizing Union" query:

uniAllCategories

SELECT yourtable.EmpNum, tlkpCategory.CatgoryID
FROM yourtable INNER JOIN tlkpCategory ON yourtable.CATEG_1 =
tlkpCategory.CategoryName
UNION
SELECT yourtable.EmpNum, tlkpCategory.CatgoryID
FROM yourtable INNER JOIN tlkpCategory ON yourtable.CATEG_2 =
tlkpCategory.CategoryName
UNION
SELECT yourtable.EmpNum, tlkpCategory.CatgoryID
FROM yourtable INNER JOIN tlkpCategory ON yourtable.CATEG_3 =
tlkpCategory.CategoryName
UNION
<etc. for all 18 fields>

appFillEmployee_Category

INSERT INTO tblEmployee_Category
SELECT EmpNum, CategoryID FROM uniAllCategories;

You can run both the Delete query and appFillEmployee_Category from a
Macro or a small VBA module with just a mouseclick.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
What am I doing incorrect?

CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)="YES" ) AS STEPUP
FROM TOTALS;



Michel Walsh said:
Hi,


or

CBool( "STEP UP" IN ( Categ_1, Categ_2, Categ_3, Categ_4, ...,
Categ_10 ) )



Hoping it may help,
Vanderghast, Access MVP


GreySky said:
You could put it all in the first expression list. Something like:

IIf([CATEG_1]="STEP UP" or [CATEG_2]="STEP UP" or [CATEG_3]="STEP
UP","YES",
"NO")

--Grey

jeanne said:
Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5],
[CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9],
[TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
Click to expand...
Click to expand...
Click to expand...
 
Hi,

SELECT CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)
AS STEPUP
FROM TOTALS;



and FORMAT the Boolean to "Yes/No", or "True/False" , or "On/Off" from the
properties of the columns, in the query designer, or in the control in the
form you will use to display the result..


Hoping it may help,
Vanderghast, Access MVP


jeanne said:
What am I doing incorrect?

CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)="YES"
) AS STEPUP
FROM TOTALS;



Michel Walsh said:
Hi,


or

CBool( "STEP UP" IN ( Categ_1, Categ_2, Categ_3, Categ_4, ...,
Categ_10 ) )



Hoping it may help,
Vanderghast, Access MVP


GreySky said:
You could put it all in the first expression list. Something like:

IIf([CATEG_1]="STEP UP" or [CATEG_2]="STEP UP" or [CATEG_3]="STEP
UP","YES",
"NO")

--Grey

:

Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5],
[CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9],
[TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP
UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
Click to expand...
Click to expand...
Click to expand...
 
HI,



missed a ) :



SELECT CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,
CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10) )
AS STEPUP
FROM TOTALS;


Michel Walsh said:
Hi,

SELECT CBOOL( "STEP UP"

IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)
AS STEPUP
FROM TOTALS;



and FORMAT the Boolean to "Yes/No", or "True/False" , or "On/Off" from
the properties of the columns, in the query designer, or in the control in
the form you will use to display the result..


Hoping it may help,
Vanderghast, Access MVP


jeanne said:
What am I doing incorrect?

CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)="YES"
) AS STEPUP
FROM TOTALS;



Michel Walsh said:
Hi,


or

CBool( "STEP UP" IN ( Categ_1, Categ_2, Categ_3, Categ_4, ...,
Categ_10 ) )



Hoping it may help,
Vanderghast, Access MVP


You could put it all in the first expression list. Something like:

IIf([CATEG_1]="STEP UP" or [CATEG_2]="STEP UP" or [CATEG_3]="STEP
UP","YES",
"NO")

--Grey

:

Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5],
[CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9],
[TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP
UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
Click to expand...
Click to expand...
Click to expand...
 
THANKS !!!!!!!!!!!!!!!!!!!!!!!!




Michel Walsh said:
HI,



missed a ) :



SELECT CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,
CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10) )
AS STEPUP
FROM TOTALS;


Michel Walsh said:
Hi,

SELECT CBOOL( "STEP UP"

IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)
AS STEPUP
FROM TOTALS;



and FORMAT the Boolean to "Yes/No", or "True/False" , or "On/Off" from
the properties of the columns, in the query designer, or in the control in
the form you will use to display the result..


Hoping it may help,
Vanderghast, Access MVP


jeanne said:
What am I doing incorrect?

CBOOL( "STEP UP"
IN(CATEG_1,CATEG_2,CATEG_3,CATEG_4,CATEG_5,CATEG_6,CATEG_7,CATEG_8,CATEG_9,CATEG_10)="YES"
) AS STEPUP
FROM TOTALS;



:

Hi,


or

CBool( "STEP UP" IN ( Categ_1, Categ_2, Categ_3, Categ_4, ...,
Categ_10 ) )



Hoping it may help,
Vanderghast, Access MVP


You could put it all in the first expression list. Something like:

IIf([CATEG_1]="STEP UP" or [CATEG_2]="STEP UP" or [CATEG_3]="STEP
UP","YES",
"NO")

--Grey

:

Does anyone know how to write this easier?

SELECT [EMPNUM], [SDATE], [EDATE], [CATEG_1], [TOTAL_1], [CATEG_2],
[TOTAL_2], [CATEG_3], [TOTAL_3], [CATEG_4], [CATEG_5], [TOTAL_5],
[CATEG_6],
[TOTAL_6], [CATEG_7], [TOTAL_7], [CATEG_8], [TOTAL_8], [CATEG_9],
[TOTAL_9],
[CATEG_10], [TOTAL_10], IIf([CATEG_1]="STEP
UP","YES",IIf([CATEG_2]="STEP
UP","YES",IIF([CATEG_3]="STEP UP","YES",IIF([CATEG_4]="STEP
UP","YES",IIF([CATEG_5]="STEP UP","YES",IIF([CATEG_6]="STEP
UP","YES",IIF([CATEG_7]="STEP UP","YES",IIF([CATEG_8]="STEP
UP","YES",IIF([CATEG_9]="STEP UP","YES",IIF([CATEG_10]="STEP
UP","YES","NO")))))))))) AS STEPUP
FROM TOTALS;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top