UnitPrice = Price1 or Price2 or Price3

[jimbo89]

I have a customers table where the customers are assigned a price level. The products table has 3 levels of sale price. How do I write the query to make the unitprice = Products.PriceLevel(1,2,or3) based on Customers.PriceLevel(1,2,or3). That is if Customer 1 has been assigned PriceLevel 1, he/she is charged PriceLevel 1 from the Products table. etc.

Thanks

 

[jimbo89]

Thanks Michael, I assume this goes in the criteria section of the query. Right?

Jim

 

[Michael J Ross]

Sorry, Scratch what i posted earlier, I answered something different to what you asked I think.

What you are needing to do is pick up the correct unit price from your products depending on customer?

Add your two tables to a query and create a join between the two on price level. To do this drag from Customers.PriceLevel to Products.PriceLevel

 

[jimbo89]

I think I preferred the IIf statement. At least I understood that. The customers table has a field called PriceLevel with 3 choices. The Products Table has 3 fields - PriceLevel1 (CostPrice), PriceLevel2(some products marked up) and PriceLevel3(All products marked up). The Orders query that feeds the Orders form is in place but it's price field is UnitPrice. I want the UnitPrice to show the price charged to a particular customer based on his/her PriceLevel. So is something placed in the crieria of the orders query or is another query needed?

Thanks again.

 

[Michael J Ross]

Ah ok so i was right first time

So it is the iif, add an extra column to your query by typing or pasting the following into your query grid

UnitPrice:IIf(Customers.PriceLevel=1,1,IIf(Customers.PriceLevel=2,2,3))

 

[jimbo89]

Thanks again. I thought the IIf statement was the way to go, I remember doing something similar many years ago on Access95 but it totally left me. Great help, thanks again.

Jim

 

[jimbo89]

Aaargh, it's not working. Using the Northwind Sample Database as the example, which I am, I have added a field to the 'Customers' table (PriceLevel) and 3 fields to the 'Products' table (PriceLevel1;PriceLevel2;PriceLevel3). The Order Details Extended qyuery works the Orders Subform where the 'Unit Price' is displayed as the price of a given item. I want this Unit Price to show the Price which reflects the Customer's Price Level. I have tried changing the UnitPrice field in the OrderDetailsExtended query to "UnitPrice:IIf(Customers!PriceLevel=1,PriceLevel1,IIf(Customers!PriceLevel=2,PriceLevel2,PriceLevel3)).
I have several variations of the wording eg [PriceLevel1], [Products].[PriceLevel1] etc but all to no avail. Please point to where I am stuffing this up...

Thanks again

 

[jimbo89]

Forgot to add that I have also added the IIF statement to the criteria of the existing 'UnitPrice' field in the ODE query.

Thanks

 

[Michael J Ross]

To be honest now I've read your descriptions the iifs arent the way to go I don't think, it is joins, also the way yopu have set up the product table up doesn't sound right. What you need is another table called e.g. productprice, in it you will have fields

productid
pricecode
price

I don't have the Northwind db so I can't post an example, can you post a cutdown version with just from recoreds from the product and customer tables, the query and the form?

 

[The_Doc_Man]

Normally I take the role of pragmatist, but here I'm going to be a purist.

You should listen to Michael. Having three prices in the same row is what is called a "Repeating Group" and is improper for normalized databases. You are not supposed to do this. Instead you have a child table that shows the product's identifying code, the discount code, and the price.

So if customer A has discount code 0, you join the product table to the discount table where the discount code matches the customer's discount level. The ONLY exception I would make is that you COULD take the alternate approach... store the price with the product table, make all entries in the discount table become percentage reductions, use OUTER JOIN to get the reduction for discount codes, and if there is no entry for that combination of product and discount code, then there is no such discount and the reduction is zero. (This is where an NZ function would be appropriate to find out that you had a null come back from the outer join.)

What does this get you? By making the entry a discount that can be absent, you make it possible to have more - or less - than three discount codes. And it is easy to tell when a product is not subject to a discount. It will have NO matching discount entries. Which makes the table "sparse" - i.e. not populated to the maximum density possible for such a table. Anywhere that there would be no discount, you need no entry. Then, you can make discount codes as you like.

Alternative B: If the codes fall into groups, have a product-group code in your product table and link the discount codes to the product groups. Fewer entries needed in the discount tables.

 

[Dreamweaver]

I do this with a one to many join IE products -> Multi Pricing

And how I utilize it can be viewed on a number of screens Here

http://www.databasedreams.co.uk/dream_manager_developer/timebillingGallery.html

Look at the fields multi Hopefully it'll give ya a few ideas

I left the default price in the main table just to make my life simpler.

best wishes

mick

 

[jimbo89]

Until I asked this question, I considered myself to be at the intermediate level of access knowledge. I will now modify that to extreme beginner.....I am totally confused and think maybe I am not explaining myself too well. Each Product has 3 levels of price eg PriceLevel1 = $1.00; Level2 = $1.20; Level3 = $1.50. Customers are assigned a Level - 1,2 or 3. If they are a Level 1 Customer they will be charged $1.00. If they are a level2 customer, they are charged $1.20 for the same item and if they are a level3 customer, they are charged $1.50 for the same item. The way I read the (sub-table) bit, there would need top be 3 sub-tables? The price levels of the products are marked up and the discount is still an option from the Orders form. The unit price on the orders subform is dependant on that particular customers price level. I am sorry to be a pain but I remember doing something similar years ago and I'm sure it was an IIf statement either in the Order Details Extended Query or the Orders subform. I really haven't played with child tables before and am a little lost. Thanks again for all your help in advance and for the previous posts.

 

[Michael J Ross]

You would need only one sub table, take a look at the attached example and in the query qrypriceeg you will see how different prices are charged for the same product depending on the customer.

 

[jimbo89]

Thanks for the example Michael, I doi indeed see it now. But that means each product has to be entered 3 times (in my case), once each for each price level. I was hoping and iif function could be performed on the products table using the customer's criteria but oh well.

Thanks again

Jim

 

[neileg]

Thanks for the example Michael, I doi indeed see it now. But that means each product has to be entered 3 times (in my case), once each for each price level. I was hoping and iif function could be performed on the products table using the customer's criteria but oh well.

Thanks again

Jim

But you already have to populate three fields for each product. Hardly a major overhead.

 

[Michael J Ross]

If you are certain you want to do it, using iifs and setting your table out like a spreadsheet and not using subtables, see query1 in the attached.

 

[Dreamweaver]

Until I asked this question, I considered myself to be at the intermediate level of access knowledge. I will now modify that to extreme beginner.....I am totally confused and think maybe I am not explaining myself too well. Each Product has 3 levels of price eg PriceLevel1 = $1.00; Level2 = $1.20; Level3 = $1.50. Customers are assigned a Level - 1,2 or 3. If they are a Level 1 Customer they will be charged $1.00. If they are a level2 customer, they are charged $1.20 for the same item and if they are a level3 customer, they are charged $1.50 for the same item. The way I read the (sub-table) bit, there would need top be 3 sub-tables? The price levels of the products are marked up and the discount is still an option from the Orders form. The unit price on the orders subform is dependant on that particular customers price level. I am sorry to be a pain but I remember doing something similar years ago and I'm sure it was an IIf statement either in the Order Details Extended Query or the Orders subform. I really haven't played with child tables before and am a little lost. Thanks again for all your help in advance and for the previous posts.

I get what you wanna do You need a field in your customers table which can be set 1,2 or 3 then using my product form as a example add a table linked to the products.
with fields (Key) productID (FK To Products),(Key)Level, Price or something like that now when you select a product for an order get it to look at the customers level then just DLOOKUP The Level for the products multi pricing table for that product.

Remember you will already have the customers details on the order form so include there level there it'll make life simpler.

hope thats what ya looking for and is understandable.

Another thing to remember is what if the boss comes to ya later and says can we add level 4 & 5 doing it with IIf's means you'll have to go in and redesign the whole thing but using the method above means just adding the data to the products being assigned the additional levels and those customers defaults that may need updating without design work.

As an afterthought you could do it with a query using the table structure above just limit it by product and customers level that should give you one record but if your not 100% sure all products will have a pricing structure and include the customers level then you'll need a right join between products and products pricing.

hope it helps mick

 

[The_Doc_Man]

And if you designed the form correctly to do this, you would pretty close to the same number of keystrokes as the "repeating group" case.

 

[jimbo89]

Thanks to everyone that has responded to this qyuestion, I have taken it all on board and tried all of the suggestions. There are a few things that are still confusing me but I will work through them. I have downloaded the time/billing db and will look that over to see what is relevant to my db. Thanks for that. What I have done so far is an IIf statement in the Row Source of the Orders Form - which does exactly what I wanted but it does not calculate the Extended Price for some reason. I will continue to work on that and post back here whilst trying the other methods suggested here. Again, my sincere thanks to all.

Jim