IF, Then, else

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

Guest

Hi,

I am trying to design (write) a query to pull inventory cycle count data
for metric reporting . There is field is my query that contains values of
A,B, C or Blank. I want to change these values and sum:
When A , then 4
When B , then 2
When C then 1
else 1.

Below is my SQL. It does not like my logic....

SELECT dbo_TIDCAFAC.FACILITY,
Count(dbo_TIDCAFAC.CATALOG_ID) AS CountOfCATALOG_ID
Sum( Case when tidcafac.abc_analysis_code = 'A' then
count(tidccwhs.loc_zone) * 4
when tidcafac.abc_analysis_code = 'B' then count(tidccwhs.loc_zone) * 2
when tidcafac.abc_analysis_code = 'C' then count(tidccwhs.loc_zone) * 1
else count(tidccwhs.loc_zone) * 1
End )

FROM dbo_TIDCAWHS INNER JOIN (dbo_TIDCCWHS INNER JOIN dbo_TIDCAFAC
ON (dbo_TIDCCWHS.CATALOG_ID = dbo_TIDCAFAC.CATALOG_ID)
AND (dbo_TIDCCWHS.FACILITY = dbo_TIDCAFAC.FACILITY))

ON (dbo_TIDCAWHS.WHSE = dbo_TIDCCWHS.WHSE)
AND (dbo_TIDCAWHS.LOC_BIN = dbo_TIDCCWHS.LOC_BIN)
AND (dbo_TIDCAWHS.LOC_TIER = dbo_TIDCCWHS.LOC_TIER)
AND (dbo_TIDCAWHS.LOC_SECTION = dbo_TIDCCWHS.LOC_SECTION)
AND (dbo_TIDCAWHS.LOC_ROW = dbo_TIDCCWHS.LOC_ROW)
AND (dbo_TIDCAWHS.LOC_ZONE = dbo_TIDCCWHS.LOC_ZONE)
AND (dbo_TIDCAWHS.QTY_TYPE = dbo_TIDCCWHS.QTY_TYPE)
AND (dbo_TIDCAWHS.Q_LEVEL = dbo_TIDCCWHS.Q_LEVEL)
AND (dbo_TIDCAWHS.CATALOG_ID = dbo_TIDCCWHS.CATALOG_ID)
AND (dbo_TIDCAWHS.FACILITY = dbo_TIDCCWHS.FACILITY)

WHERE (((dbo_TIDCCWHS.QTY_TYPE) In ("PR","AL","SU","TP","AC"))
AND ((dbo_TIDCAFAC.CATALOG_STATUS) Not In ("RECODED","OBSOLETE"))
AND ((dbo_TIDCAFAC.STOCK_TYPE) Not In ("X")))
GROUP BY dbo_TIDCAFAC.FACILITY
HAVING (((dbo_TIDCAFAC.FACILITY)="MFS"));

Cleve (access beginner 101)
 
Hi Cleve,

I think that you may have more joy if you use a instant if rather than
the if then else structure. It works like this IIf(condition,
true_value, false_value) you can nest these if desired.

Good luck,
 
Create another field in the query, and then Sum the field

IIf([FieldName]="A",4,IIf([FieldName]="B",2,1))

You don't need the C=1, it can join all the other cases that are equal 1
 
I would create a small table of Codes and Values. You can then include this
table in your query to perform a "lookup" to get the values.
 
Nick , Thanks for the quick response on the IIF . But you have a beginner
here.

Your response has me going in the correct direction, But How I have a
problem with nesting .... I Not clear on the correct text . I get errors when
trying to nest using else ... Additional help requested.....
 
Ofer Cohen
Thanks
Your example helps me relate with the response from Nick.

This works
--
Cleve Hamrick


Ofer Cohen said:
Create another field in the query, and then Sum the field

IIf([FieldName]="A",4,IIf([FieldName]="B",2,1))

You don't need the C=1, it can join all the other cases that are equal 1

--
Good Luck
BS"D


Cleve said:
Hi,

I am trying to design (write) a query to pull inventory cycle count data
for metric reporting . There is field is my query that contains values of
A,B, C or Blank. I want to change these values and sum:
When A , then 4
When B , then 2
When C then 1
else 1.

Below is my SQL. It does not like my logic....

SELECT dbo_TIDCAFAC.FACILITY,
Count(dbo_TIDCAFAC.CATALOG_ID) AS CountOfCATALOG_ID
Sum( Case when tidcafac.abc_analysis_code = 'A' then
count(tidccwhs.loc_zone) * 4
when tidcafac.abc_analysis_code = 'B' then count(tidccwhs.loc_zone) * 2
when tidcafac.abc_analysis_code = 'C' then count(tidccwhs.loc_zone) * 1
else count(tidccwhs.loc_zone) * 1
End )

FROM dbo_TIDCAWHS INNER JOIN (dbo_TIDCCWHS INNER JOIN dbo_TIDCAFAC
ON (dbo_TIDCCWHS.CATALOG_ID = dbo_TIDCAFAC.CATALOG_ID)
AND (dbo_TIDCCWHS.FACILITY = dbo_TIDCAFAC.FACILITY))

ON (dbo_TIDCAWHS.WHSE = dbo_TIDCCWHS.WHSE)
AND (dbo_TIDCAWHS.LOC_BIN = dbo_TIDCCWHS.LOC_BIN)
AND (dbo_TIDCAWHS.LOC_TIER = dbo_TIDCCWHS.LOC_TIER)
AND (dbo_TIDCAWHS.LOC_SECTION = dbo_TIDCCWHS.LOC_SECTION)
AND (dbo_TIDCAWHS.LOC_ROW = dbo_TIDCCWHS.LOC_ROW)
AND (dbo_TIDCAWHS.LOC_ZONE = dbo_TIDCCWHS.LOC_ZONE)
AND (dbo_TIDCAWHS.QTY_TYPE = dbo_TIDCCWHS.QTY_TYPE)
AND (dbo_TIDCAWHS.Q_LEVEL = dbo_TIDCCWHS.Q_LEVEL)
AND (dbo_TIDCAWHS.CATALOG_ID = dbo_TIDCCWHS.CATALOG_ID)
AND (dbo_TIDCAWHS.FACILITY = dbo_TIDCCWHS.FACILITY)

WHERE (((dbo_TIDCCWHS.QTY_TYPE) In ("PR","AL","SU","TP","AC"))
AND ((dbo_TIDCAFAC.CATALOG_STATUS) Not In ("RECODED","OBSOLETE"))
AND ((dbo_TIDCAFAC.STOCK_TYPE) Not In ("X")))
GROUP BY dbo_TIDCAFAC.FACILITY
HAVING (((dbo_TIDCAFAC.FACILITY)="MFS"));

Cleve (access beginner 101)
Click to expand...
Click to expand...
 
Back
Top