please help : same product same ID BUT different Prices

[movaheditabrt]

Hi guys!
I'm going to design an access database for an LPGas company.
This company sells LPGas and LPGas cylinders.
Suppliers:
1.AlfaCylinder supplies it's cylinders(not the same kinds of cylinder as ME's)
2.MECylinder supplies it's cylinders(not the same kinds of cylinder as Alfa's)
3.PerfectGas Co. supplies it's LPGas.
Products :
This company sells cylinders and fills different kinds of cylinders with LPGas. for example, 11 kilogram LPGas in one kind and 25 kilogram LPGas in another kind and so on. Therefore, products are :
please see the attachment

No problem with customers table. There are two kinds of customer. 1. Known customers 2. Unknown (everyday changing) customers. (there is a record for them in customer table named as Uncustomers, that at the end of the day, the sum of sale of this kind entered under this name.)
Problem is :
As you see, there are some products with the same ID and same name or same type but different prices. How can I arrange the product table?

 

[spikepl]

What is the point of this ID? You have just discovered it is not unique, so no use as ID. What is wrong with an autonumber?

As to product names you can concoct whatever complex coomposite abbreviation you like, if you need something compact at all. The customers likely care diddly ...

 

[Steve R.]

You have a flat table. You need to make it relational. Do an internet search on Normalization. Base Tutorial: From Newbie to Advocate in a one, two... three!.
Note: this PDF document is not an MS Access document, but the narrative concerning Normalization is still highly relevant.

Your ID field should not be used as a Primary Key (PK). You need to create an autonumber field. It is that field which is used to establish your relationships.

There are two kinds of customer. 1. Known customers 2. Unknown (everyday changing) customers.(there is a record for them in customer table named as Uncustomers, that at the end of the day, the sum of sale of this kind entered under this name.)

Seems to me that when a person makes their first order, they become a customer. Therefore, there should be no need for this differentiation or a discrete table for that purpose. The Uncustomer situation can be handled by a logical field where the default value is "true" upon placing their first order. Subsequent orders would toggle that field to false.

To address your pricing concern, you will need a separate (price) table linked with a foreign key linked to your product table primary key.

 

[movaheditabrt]

Thank you so much spikeple and Steve R.

 

[gemma-the-husky]

the product id being the same isn't a problem, necessarily. This is more a matter of how your company chooses to manage it's sales

however, if the same product comes in different sizes, then you need to know BOTH the product AND the size you want. You could offer the size as a dynamic selection, or you could store the required size in the customer record, say.

To manage the pricing you will need a product pricing table that includes both the productid, and the unitid to provide a unique multi-column index