IIf Statemen In Query

[DS]

I have an IIf statement in a Query. I was wondering, is there an easier
way to write or do this?

Expr1: IIf([PriceLevel]=1,[Price1],IIf([PriceLevel]=2,[Price2],
IIf([PriceLevel]=3,[Price3],IIf([PriceLevel]=4,[Price4],
IIf([PriceLevel]=5,[Price5],IIf([PriceLevel]=6.[Price6],
IIf([PriceLevel]=7,[Price7])))))))*[Quantity]

As you can see. I have 7 Price levels. The level is determined by the
Menu. Each Price is attached to the Item in the Items Table. Any help
would be appreciated.

Thanks
DS

 

[Van T. Dinh]

Write a Public UDF (User-Defined Function) in VBA module using the SELECT
CASE statement and use this function in your Query. The function should be
something like:


Public Function fnPrice(intPriceLevel as Integer) As Currency

Select Case intPriceLevel
Case 1
fnPrice =
Case 2
...
End Select
End Function

 

[John Vinson]

I have an IIf statement in a Query. I was wondering, is there an easier
way to write or do this?

Expr1: IIf([PriceLevel]=1,[Price1],IIf([PriceLevel]=2,[Price2],
IIf([PriceLevel]=3,[Price3],IIf([PriceLevel]=4,[Price4],
IIf([PriceLevel]=5,[Price5],IIf([PriceLevel]=6.[Price6],
IIf([PriceLevel]=7,[Price7])))))))*[Quantity]

As you can see. I have 7 Price levels. The level is determined by the
Menu. Each Price is attached to the Item in the Items Table. Any help
would be appreciated.

Thanks
DS

Two answers:

1. Use the Choose() function instead of nested IIF's:

Expr1: Choose([PriceLevel], [Price1], [Price2], [Price3], [Price4],
[Price5], [Price6]) * [Quantity]

2. The real answer: normalize your tables! If you have a One (item) to
Many (prices) relationship, model it as a one to many relationship by
getting rid of your PriceN fields, and instead creating a Prices table
with fields ItemNo, PriceLevel, and Price. Join this table to your
query on ItemNo and PriceLevel to pick up that price.

John W. Vinson[MVP]

 

[DS]

Write a Public UDF (User-Defined Function) in VBA module using the SELECT
CASE statement and use this function in your Query. The function should be
something like:


Public Function fnPrice(intPriceLevel as Integer) As Currency

Select Case intPriceLevel
Case 1
fnPrice =
Case 2
...
End Select
End Function

Thanks
I'll give it a try!
DS

 

[DS]

I have an IIf statement in a Query. I was wondering, is there an easier
way to write or do this?

Expr1: IIf([PriceLevel]=1,[Price1],IIf([PriceLevel]=2,[Price2],
IIf([PriceLevel]=3,[Price3],IIf([PriceLevel]=4,[Price4],
IIf([PriceLevel]=5,[Price5],IIf([PriceLevel]=6.[Price6],
IIf([PriceLevel]=7,[Price7])))))))*[Quantity]

As you can see. I have 7 Price levels. The level is determined by the
Menu. Each Price is attached to the Item in the Items Table. Any help
would be appreciated.

Thanks
DS

Two answers:

1. Use the Choose() function instead of nested IIF's:

Expr1: Choose([PriceLevel], [Price1], [Price2], [Price3], [Price4],
[Price5], [Price6]) * [Quantity]

2. The real answer: normalize your tables! If you have a One (item) to
Many (prices) relationship, model it as a one to many relationship by
getting rid of your PriceN fields, and instead creating a Prices table
with fields ItemNo, PriceLevel, and Price. Join this table to your
query on ItemNo and PriceLevel to pick up that price.

John W. Vinson[MVP]

Thanks. This is a real tuffie...
I tried Normalizing my tables as such...but that created a problem
further down the road when I tried to put an order in.

First you choose the Menu..which says what price level you will get.
Next come the Menu Catagories...then the Menus...and then Finally the
Item is placed in a Subform which holds the Order Details, and thats
where I couldn't get the item to go in....because I trly believe the
prices should have their own table, maybe I'm missing something?
Thanks
DS

 

[Douglas J. Steele]

Thanks. This is a real tuffie...
I tried Normalizing my tables as such...but that created a problem further
down the road when I tried to put an order in.

First you choose the Menu..which says what price level you will get.
Next come the Menu Catagories...then the Menus...and then Finally the Item
is placed in a Subform which holds the Order Details, and thats where I
couldn't get the item to go in....because I trly believe the prices should
have their own table, maybe I'm missing something?
Thanks

Yes, prices should definitely have their own table. However, having their
own table doesn't mean that there should be 7 different price rows in one
table. Instead, if you have 7 different prices, you should have 7 different
rows in the price table.

 

[DS]

Yes, prices should definitely have their own table. However, having their
own table doesn't mean that there should be 7 different price rows in one
table. Instead, if you have 7 different prices, you should have 7 different
rows in the price table.

Right, maybeI'm confusing this whole thing. It should be easier than
this, I think the problem I'm having is that I made a form with an
option group, where you pick Price 1, 2 etc. and tied it back to the
table. Am I making sense? Or is 14hrs a day working on a database too
much !!!!!
Thanks
DS

 

[John Vinson]

Right, maybeI'm confusing this whole thing. It should be easier than
this, I think the problem I'm having is that I made a form with an
option group, where you pick Price 1, 2 etc. and tied it back to the
table. Am I making sense? Or is 14hrs a day working on a database too
much !!!!!

Remember - you're close to the database (maybe too close), we're not.

How are your prices stored? What table? How is that table related to
your other tables?

John W. Vinson[MVP]

 

[DS]

Remember - you're close to the database (maybe too close), we're not.

How are your prices stored? What table? How is that table related to
your other tables?

John W. Vinson[MVP]

OK. Still having Problems.
I have a...
MenuTable On the Form..,Pick a Menu, Price Level

MenuCatTable On the SubForm..,Pick a Menu Cat

ItemsTable Opens New Form, add Items

PriceTable Have Different Price Levels, Controlled by Menu

Each Menu can have Many MenuCats.....Each MenuCat can have many Menus
Each MenuCat can have Many Items......Each Item Can Have Many Menu Cats
Each Item has 7 Prices, globally choosen when you pick the menu.

Is this any clearer?
Thanks John
DS

 

[John Vinson]

OK. Still having Problems.
I have a...
MenuTable On the Form..,Pick a Menu, Price Level

The Form IS ABSOLUTELY IRRELEVANT at this stage.

Data is stored in Tables, not in Forms. Relationships apply to Tables,
and only secondarily to Forms. Designing your table to fit a Form
rather than vice versa is all but certain to give you a bad design.

MenuCatTable On the SubForm..,Pick a Menu Cat

ItemsTable Opens New Form, add Items

PriceTable Have Different Price Levels, Controlled by Menu

Again... it seems that each record of the PriceTable refers to one
Item, and has fields named Price1, Price2, Price3, ... , Price7?

If so - *that is the source of your problems*. This design is WRONG.

PriceTable should have fields ItemID (whatever that might be, again -
I can't see your fieldnames), PriceLevel, and Price. You would select
a price by selecting a RECORD from this table; not by selecting a
FIELD from a wide/flat table.

Each Menu can have Many MenuCats.....Each MenuCat can have many Menus
Each MenuCat can have Many Items......Each Item Can Have Many Menu Cats

Now I have no trace of a notion what a MenuCat might be (though if
it's a menu of varieties of catfood, I might be interested; my little
old lady cat Jasmine is getting VERY choosy about what she'll eat).

Each Item has 7 Prices, globally choosen when you pick the menu.

Is this any clearer?

Not really. Again... Forms are *JUST WINDOWS*. They don't contain any
data, they don't have relationships, they are *purely secondary*. If
you get the table structures and relationships wrong, then the Form
will be much more difficult.

Again - please post:

TableName
FieldName <Datatype> <Primary Key>
FieldName <datatype>
FieldName <datatype>

for the relevant tables and fields.

John W. Vinson[MVP]

 

[DS]

The Form IS ABSOLUTELY IRRELEVANT at this stage.

Data is stored in Tables, not in Forms. Relationships apply to Tables,
and only secondarily to Forms. Designing your table to fit a Form
rather than vice versa is all but certain to give you a bad design.




Again... it seems that each record of the PriceTable refers to one
Item, and has fields named Price1, Price2, Price3, ... , Price7?

If so - *that is the source of your problems*. This design is WRONG.

PriceTable should have fields ItemID (whatever that might be, again -
I can't see your fieldnames), PriceLevel, and Price. You would select
a price by selecting a RECORD from this table; not by selecting a
FIELD from a wide/flat table.




Now I have no trace of a notion what a MenuCat might be (though if
it's a menu of varieties of catfood, I might be interested; my little
old lady cat Jasmine is getting VERY choosy about what she'll eat).




Not really. Again... Forms are *JUST WINDOWS*. They don't contain any
data, they don't have relationships, they are *purely secondary*. If
you get the table structures and relationships wrong, then the Form
will be much more difficult.

Again - please post:

TableName
FieldName <Datatype> <Primary Key>
FieldName <datatype>
FieldName <datatype>

for the relevant tables and fields.

John W. Vinson[MVP]

John as requeted:

MENUS
MenuID <Primary Key>
MenuName

MENUCATS
MenuCatID <Primary Key>
MenuCatName

ITEMS
ItemID <Primary Key>
ItemName

PRICES
PriceID <Primary Key>
Price
PriceLevel
ItemID

MODS
ItemID
ModID

PREPCAT
PrepID <Primary Key>
PrepName

PRINTERS
PrinterID <Primary Key>
PrinterName

Thanks
DS