more than one selling price for a product in ms access

[damola]

I designed a small database to manage some products.
Please how can i add more than one selling price for a product in ms access 2007 database i.e "unit price, pack price, retail & wholesale price.
thanks
will be grateful if you help.

[Oralloy]

@damola

Simply include a pricing table that refers back to your product table. Something like this:

CREATE TABLE pricing
(
  product_id INT PRIMARY KEY,
  unit_price CURRENCY NOT NULL,
  pack_price CURRENCY NOT NULL,
  CONSTRAINT FOREIGN KEY(product_id) REFERS product(id)
)

[NeoPa]

Simply add those fields to your Product table. They all have different names so needn't cause any confusion or anything existing to break.

[damola]

more clarity please on more than one selling price for a product in MS ACCESS

Thanks for your replies i really appreciate them.
Pls am still a little confused:
AM using MS ACCESS DATABASE
I have a price table consisting of pack price, unit price. and the products table, then i want to sell a product at a pack price, is it possible for the price to come up automatically in the orders table.
please will appreciate any help rendered
thanks

[NeoPa]

What I'm trying to say is that if your pricing structure is as simple as you've explained, there is not even any need for a separate table for prices anyway. Simply store the pricing data as fields in the Product table.

If you do want to keep a table with a one-to-one link to the Product table then this can easily be done :

SELECT tProd.ProdCode
     , tProd.ProdName
     , tPrice.Sell
     , tPrice.Cost
FROM   [Product] AS tProd
       INNER JOIN
       [Price] AS tPrice
  ON   tProd.ProdCode = tPrice.ProdCode

This should not be complicated this way without a good reason though.

[damola]

Am very sorry to bother you
What i mean is a single product can be are sold as unit, Dozen or Pack price.
How can i do it in such a way that when i click on customer's order, the customer wants a pack of sweet, the price will be different from a piece.
How can i automatically generate the price i want for a particular product in MS ACCESS DATABASE 2007
Am grateful for your assistance

[NeoPa]

I don't understand the problem. I have answered your question already.

If what you want to know is different from what has already been asked and answered then you really need to say what the question actually is. It almost seems that you want Access to determine which pack size is to be used without being told in any way. This could be because you have information you haven't shared with us yet (which would explain why it makes so little sense) or because you misunderstand what Access can do for you.

For now though, with the information I have available, There's no more I can do to help.