Price lookup

[taylor]

Hi,

I am creating a db that handles orders.

My problem is that the products being sold are so at different prices depending on the customer. (e.g I am selling 100 burgers to Mcdonalds for £50 whereas to Burger King I am selling them for £35).

Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.

I have setup a relationship between the two tables based on product name

How to I go about looking up the different prices depending on which customer I have placed an order for?

can it be done in a table or do I need to create a query to sort it all out.

Hope that makes sense,

thanks

 

[ajetrumpet]

My problem is that the products being sold are so at different prices depending on the customer. Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.

This looks OK to me, I would probably do the same thing here.

I have setup a relationship between the two tables based on product name

If I were setting this up the way you have it already, I would eliminate this relationship and just use the 2nd table as a LOOKUP table.

How to I go about looking up the different prices depending on which customer I have placed an order for?"

An ideal scenario for this would be to have an orders form, where you're entering your order data. And use "DLookup" (in the "AfterUpdate" Event of the "ProductName" control) as a controlsource for the control in which you enter your product price data. Doing this of course will automatically lock the control, and will not allow you to enter any other data into it. But it is efficient...

= DLookup("[pricefieldname]", "lookuptable", 

"lookuptable.pricefieldname = [FormControlThatHoldsTheProductName]"

 

[GaryPanic]

is there only two customers - if so

Productid
ProductName
ProductAprice
ProductBprice

thena a few smarts on your form if customer is A priced then have ProductA visible.true and ProductBvisible=false

but this does depend on your customer base

If you are only going to have two classes of customer prices A/B then this would work
your cusotmer table would need a product code
- however if the product floats then this won't work

what i mean by this is Macdonalds burgers are 50 and BK is 35
which would be Mac = a price and BK = Bprice
then if you flip it for ketchup
Mac = b price and burger king = A price

this would be bedlam..

so as long as your client will always be able to have Products at A price or B price for all your products - otherwise you ahve to get into a real pickle over
if product is gerkins then prices are x and if lettuce then Y -

you'll end up shooting someone

taking the approach above and if it needs to be semi flexible you coudl have products split into class (type) and have a noteation field on their with if class 1 then B price to apply and if class 2 then A price etc -- but this is a real nightmare

easier if all products are either A or B and your client is entitled to only A or B through out


g

 

[GaryPanic]

look at those typos...
g

 

[taylor]

Thanks for the replies.

Firstly, I have loads of customers (100ish). Secondly, I have loads of products and every customer pays differently for each product so its not the case of just having 'price band A' and 'price band B'.

ajetrumpet your solution sounds like it could work, Ill give it a go.

I'll attach the database if you fancy a look. It may help clarify what is going on.

 

[Simon_MT]

Perhaps, on each client have a pricing structure. On a separate Table have:

ProductID
PricingID
UnitCost


PK = ProductID and PricingID.

The Products UnitCost could then be determined by the Client's PricingID.

My situation requires flexible discounts so sales personnel do there own invoices!

Simon

 

[ajetrumpet]

Taylor,

I'm not trying to recreating your database, but here are my suggestions...

** Consolidate the information (fields) from the "Order Overview" table and the "Ordered Products" table. All of this information can be kept in one table, since it all relates to orders that are being placed. I see why you have it separated, and it does make sense, but having the fields in all one table will help you in the following ways...

1) You can eliminate all of the relationships associated with the tables and just keep the one-to-many connecting "Order Overview" and "Customers" ON "CustID"

2) You will reduce the complexity of the queries that you write when you want to retrieve this information.

** You can also eliminate the one-to-many that you have between your "product group" table and your "products" table. The GROUP field of "Products" is a lookup column anyway, so the information you need is already in the "products" table. That takes the place of the relationship.

AND, to answer your original question about the lookup of the price...

You have everything set up just fine to do this. It can be done with cascading combo boxes on a form. I assume you're going to use a form for order entry at some point?? I noticed you just have tables in your database now.

The cascades will work with your setup. Do you know how to do it?? There are plenty of examples on this site of the different ways. You should do a search for the subject and see what you can come up with...

Here are a couple links to get you started...
http://www.access-programmers.co.uk/forums/showthread.php?t=134735
http://www.access-programmers.co.uk/forums/showthread.php?t=134313

 

[taylor]

Won't consolidating 'order_overview' and 'ordered_products' cause data in the databse to be duplicated.. eventually clogging it up?

Here is a newer database which has a nice form built so you can see how the ordering takes place.

Simply select your Customer, then the Order, then the products appear below.

If you notice when you actualy look at the 'Items' section on the form you can see all of the products that are in the chosen order. My aim is for the 'Price' to automaticly appear there rather than the user having to enter anything.

Will the 'cascading combo boxes' solution automatically find the corresponding price?

 

[ajetrumpet]

Taylor,

From what I see on the form, you're going to need cascades to populate the price. Because if someone is going to repeat an order, and they pick an ORDER ID from the first drop down on the last subform, how will they know what's on that order id, unless they can pick from a filtered list in the second drop down??

Anyway, did you check out those threads that I provided above?? They have sample databases in them that you can get the code you need from for cascading...

Also, here's the link from Microsoft that will tell you how to populate the "price" text box upon picking a product...

http://support.microsoft.com/kb/319482/en-us