Confused on a confusing iif statement

[writersoon]

I've created a db with 1 update table and 1 maketable query.

I ve created an iif statement but its not long enough for the info i need. The statement works, but where do i fit the rest in?

SELECT IIf([PO Type]="BO","Blkt Order",IIf([PO Type]="RM","PORM",IIf([PO Type]="WT","WHS Transfer",IIf([PO Type]="BL","Blkt Release",IIf([PO Type]="DO" And [NonStock]="N","NS Direct","Direct"))))) AS Comments, Review.*, * INTO [PO REVIEW ATTRIBUTES]
FROM PO AS Review;

This is what i need to do:

iif PO in column "PO Type", AND blank in column NonStock, AND column Inventory POS equals +/- 10% of column LinePt, THEN LINEPT ORDER IN COLUMN COMMENTS
And Inventory POS is greater than column LinePt AND greater than availability than LU IN COLUMN COMMENTS.

THANKS MUCH IN ADVANCE

 

[HarleyQuinn]

What have you tried so far?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

Have you considered a table of PO Type values and the substitute values which can be joined to the main table?

 

[writersoon]

Ive tried everything i can think of, but i just would rather one sql statement rather than code, ive tried coding it but i kept getting errors.

The db dosent contain tables just the headings because i have a automarco that imports a table from a shared network and loads the query and outputs as email.

i thing i tried doing was combine expressions into one column but i kept getting double columns.

 

[Remou]

It would be al lot easier if you created an extra PO Type table. I do not quite get all you say, but you can get some ideas from this:

SELECT 
   Iif([pot] = "Direct" 
      AND [nonstock] = "N","NS Direct",
   Iif([pot] = "PO" 
      AND [perc] <= 10 
      AND [nonstock] = "","LINEPT ORDER",[pot])) 
   & " " & [lu] AS comment, 
       po.* 
INTO [po review] 
FROM po 
INNER JOIN 
   (SELECT 
      id, 
      Switch([po type] = "BO","Blkt Order",
             [po type] = "RM", "PORM",
             [po type] = "WT","WHS Transfer",
             [po type] = "BL", "Blkt Release",
             [po type] = "DO","Direct", 
             [po type] = "PO","Po") AS pot, 
      ([inventory pos] / [linept] * 100) AS perc, 
      Iif([inventory pos] > linept 
            AND [inventory pos] > availability,"LU","") AS lu 
    FROM   po) AS potypetable 
ON po.id = potypetable.id

 

[writersoon]

Hey thanks...
I made another table, andI tried a few diffrent combinations,but i keep getting error clauses and from errors.

From your SQL I refomated the two table as follows:

table1(po review) is 5 colums

POType NonStock InventoryPos LinePt Availability
PO N 100 50 50
DO N 100 50 50
RM N 100 50 50
BL N 100 50 50
BO N 100 50 50
PO N 100 50 50
Table(pot) 2 is 1 column
Direct
Blkt Release
Blkt Order
PORM
Purchase Order


Thanks

 

[lespaul]

Ok, your problem is that table 2 needs two fields, the code and the description:
[tt]
code description
BO Blkt Order
RM PORM
PO PO
DO Direct
[/tt]

then in your query you can join table1 and table2 on the code and display the description:

SELECT T1.*, description FROM Table1 T1
INNER JOIN Table2 T2 ON T1.POType = T2.code

have you read Understanding SQL Joins? That may help you alot.

HTH



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[writersoon]

The extra table is what i was trying avoid because i just wanted to use the db to import, filter, and mail an excel file.

I adjusted the code from above but it kept counting the comments that i need inserted as PO Types:
SELECT
IIf([POType] = "DO"
AND [NonStock] = "N","NS Direct",
Iif([POType] = "PO"
AND [perc] <= 10
AND [NonStock] = "","LINEPT ORDER",[POType]))
& " " & [lu] AS comment,
p.*
INTO [po review]
FROM p
INNER JOIN pot ON p.POType=pot.POType
(SELECT
id,
Switch([po type] = "BO","Blkt Order",
[po type] = "RM", "PORM",
[po type] = "WT","WHS Transfer",
[po type] = "BL", "Blkt Release",
[po type] = "DO","Direct",
[po type] = "PO","Po") AS pot,
([inventory pos] / [linept] * 100) AS perc,
Iif([inventory pos] > linept
AND [inventory pos] > availability,"LU","") AS lu
FROM p INNER JOIN;

Table 1:
(is the same as above)
Table 2:
code description
BO Blkt Order
RM PORM
PO PO
DO Direct
BL Blkt Release

I am not sure if i am doing the perct wrong, but i am trying to Inventory POS equals +/- 10% OF column LinePt, (BUT NOT AS A PERCENT)THEN LINEPT ORDER IN COLUMN COMMENTS IFF NOT THEN "HIGHLIGHT"

Thanks I appreciate your input.