How to make this query working

[senatorman]

Hello everybody,



I'hope that here are people they can and will help me with my problem with Access

I sell products, and i will make that Access calculate the sellingprice. The profitmarge depence at the price i've pay
I have two tabels, the productlist and the margelist like

Productlist

Art.No	Description	Price
ART1	Notebook-A	€ 200,-
ART2	Notebook-B	€ 225,-
ART3	Notebook-C	€ 350,-
ART4	Keyboard-1	€ 8,-
ART5	Keyboard-2	€ 35,-

Profitmarge

Price-minimal	Price-maximal	profit
€ 0	€ 25	15%
€ 25	€ 250	10%
€ 250	€ 1000	8%

I'll make a query with these two tables, and can't find the good expression(s) i need so that the query calculate the sellingprice dependent my purchase-price

I hope i exlain my problem correctly and you can understand.

Greetings Senatorman

[NTC]

the easiest way for a novice to approach this is to design a query in steps; begin with product list and then add 3 calculated fields which multiply the 3 different profit %...... then continue to add fields to identify which profit amount is to be used; for example have a calculated field Under25: iif([Price]<=25,1,0) then make a calculated field for Over25 etc.... you will end up with a set of fields with 1s and 0s by which then you can use logic to determine which profit calc should be used and so your last calculated field is Final Price. It can take several fields of Iif statements to handle all your variations - but it is easily tested and well within the reach of the novice once you become familiar with the Iif method.

[senatorman]

Thanks for your helpfull answer, but for my big database (40000 products and 9 different profitmarges), i think your way is to devious and maybe not the best way to keep our database as small as possible.

Is there a formule/expression to use, that directly reconize the purchase-price and return the sellingprice with the current marge in the producttabel?

[NTC]

well your Price List should have an additional field: ProfitMarginClass - with a value 1-3 (or 1-9 if you have 9 margins). then your profit margin table needs this same field. Then you can easily join the 2 together on this field value......

as it is now you will need to put together a query with a join that has >= and <= values as part of the sql. It must be written in sql view as you cannot accomplish this using query design which will only allow a simple join.

[senatorman]

ok, this way looks easier and keep the database less complexer.
I am not totaly a newbie with access, but working with large expressions and sql is not my specialty. I still learning working with expressions

can you give/make for me a sql example based on the two tables in my first post?