# Thread: How to enter one single item with 3 diffferent barcode value

1. Registered User
Join Date
Jan 2005
Location
Nanded, India
Posts
397

## Unanswered: How to enter one single item with 3 diffferent barcode value

Hi,

I am designing a database for a supermarket.

I am at the initial design stage and so I created my first table and forms. I have a question;

I will have to enter / read Barcode value in Product_Master form of the system, which is not a big problem as scanner is fully computable with windows, all applications like MS Access. Before I make entries in Product_Master I have to decide following things where I m confused.

Let us take example of Lux Beauti Soap of 175 Grams.

Lux Beauty soap is receives in three different pack.
1. Single pc (which is normal) with barcode value - called ‘Pcs’
2. Six pieces packed together with a plastic cover called ‘Pkt’
3. Seven ‘pkts’ contains in one box called ‘Ctn’ (Carton)

Now if we sale it by pc, the barcode value is different
If we sale it by 6 pc pkt then price will be different
If we sale it by carton (7 packs -each contain 6 pc) then price will be different

So for these three different price Barcode also will be different like first barcode will be printed on single Lux soap, second barcode will be on plastic cover of 6 Lux pcs called ‘Pkt” and the whole carton of 7 pkts also holds barcode value.

This is common in supermarket when a customer purchase one single pc, it would cost him more than purchasing 6 pieces packet.

My question is how I can enter this item in Master form with three different barcode value for the SAME ITEM.

Because when I will design sales invoice, off course the goods will be entered using Scanner. If we sale a whole carton, it should read and display carton price. Similarly if we sale only 6 pcs pack from that carton, it should read and display 6 pc price and like wise for single pc also do the same.

In my below calculation the proudctcode I used as criteria and so I can pick up qty of similar item from diff tables to calculate the stock like below;

Stock = Nz(DSum("Openingbal", "Product_master", "ProductCode = " & ProductCode), 0)
Stock = Stock + Nz(DSum("PurQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)
Stock = Stock + Nz(DSum("SalesRetQty", "T_SalesInvFoot", "Productcode = " & ProductCode), 0)
Stock = Stock - Nz(DSum("SalesQty", "T_SalesInvFoot", "Productcode = " & ProductCode), 0)
Stock = Stock - Nz(DSum("PurRetQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)
Stock = Stock - Nz(DSum("Qty", "T_MInv_Foot", "Productcode = " & ProductCode), 0)

But how to calculate the stock for the same item with 3 diff prices and barcodes? What I understand it there should be one single entry for each product in product master table.

After selling the item, how would it show current stock? I mean when we sale 2 single pcs of Lux, how it would less from carton or pkts and display current stock.

I appreciate if someone could put light on it how to deal with.

All ideas are welcome…

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
the situation you have is three different barcodes for three different products, not three barcodes for the same product

the only difference between the packet of 6 bars of soap and a bag of peanuts is that the individual peanuts do not have a barcode

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
strictly speaking most wholesaling systems would treat that as 3 separate items with 3 different product / barcodes.

However that depends on the information on the packaging. if the outer(s) have exactly the same barcode as the individual items then you have a problem.

Messy: create your own barcode labels for the case and scan those.

Another approach is to recognise that you have more than one product line with that barcode when it is first scanned. The request from the user which product they are actually scanning.

one major concern I would have about having 3 products with the same barcode is that the system is prone to fraud.. someone could scan the case but only select the individual item (the customer then giving cash to the till operator later on). You need to design something which can prevent that (whether thats a manual procedure or a more elegant design is up to you).

a slightly more sophisticated approach may be to identify which customers buy which products.. often this is done on contract basis.. ie the wholesaler agrees what products will be supplied to which customers, and at what price.. you then know what the customer buys. items that are not on the agreed contract cannot be supplied, or cannot be supplied without clearance.

Alternatively you cold take the view that this customer A is a wholesale customer so cannot buy single items.... so always charge them the case price, whereas customer B only buys single items.
you could do that through say average spend per invoice over the last n weeks or n sales.
you could do that through a flag on the customer definition.
you cudl do that through analysing previous purchases
If you go down the route of basing it on previous spend, or previous purchases then you hav a problme when in the early days of customers purchases.. untill you have a pattern of transactions.

As said earlier nmost warehousing systems would treat that as 2 possibly three types of stock. If you break open the packet. Its quite usual for a warehouse to break open a case and sell of individual items.. if so they would normally price at pack (intermediate level). Ite relatively rare int he UK that I've come accross warehousing systems that allow the pack to be pbroiken open (usually its either wholesaling or retailing.. but not both in the same system

for stock control purposes i think you have to decide what you are going to do
if its one barcode then I think you should consider recording all sales at the base item level (ie sell 1 bar of soap), sell 1 pack but record the sale as 6 items, sell one case 42 items) and apply an appropriate discount rule... you would need to know how many items each successive outer contains

In an ideal world I think you should treat an individual item as a different product, and packs (6 off) as another. Its arguable that in a very high volume warehouse cases another.. but I doubt you would have a situation where you would have all three. a big wholesaler may sell by the pack or case, a smaller wholesaler may sell by the individual item or pack (ie act more or less as a retailer).

HTH

4. Registered User
Join Date
Jan 2005
Location
Nanded, India
Posts
397

The beauti bar has three barcode prices.
1. Barcode price on bar itself say US\$ 1.5/pc
2. Bracode price of for each pack of 6 pcs ...say US\$ 8.00/ Pack
3. Barcode price of a carton that contains 6 packs. say US\$ 45.00/ carton

When we sale pack (of 6 pcs) it means it would have different barcod to display US\$ 8.00. If sold by carton then diff barcode price to show with is US\$45.

My question is how we will enter all these three Barcode prizes for ONE ITEM in Master form bcz Master entry means ONE TIME entry for each item.

Do you think that there should be 3 diff prices text boxes and 3 different barcode text boxes iin Master form so that we will a choice for Unit of Measurement. Means if UOM is Carton, the barcode value should stored in first text box and its price in relavant text box...something like this.../

Regards.

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
i don't think i was very clear in my previous answer

you do not enter these three barcodes for the SAME ITEM

you enter these three barcodes for THREE DIFFERENT ITEMS

6. Registered User
Join Date
Jan 2005
Location
Nanded, India
Posts
397
Thanks Heldem and r937 again.

Sir, it is not different item. Item is same but packing is different.
Do you still think it should be considered as 3 separate item and enter in Master file 3 records for them?

If so, how can I calculate the stock then? I mean if you see my vb code I earlier mentioned,

Stock = Nz(DSum("Openingbal", "Product_master", "ProductCode = " & ProductCode), 0)
Stock = Stock + Nz(DSum("PurQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)

If I agree with you sir, what will be criteria to have sum of qty for similar item? What key fields in the table / forms to set so that it would give me current stock.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by Ashfaque
Thanks Heldem and r937 again.

Sir, it is not different item. Item is same but packing is different.
Do you still think it should be considered as 3 separate item and enter in Master file 3 records for them?
if you only sell the item individually then they can be treated as one item. but if you sell item, pack or case with different prices attached then to all extents and purposes they are different items.

.. besides which its not just the packing that is different Pack B contains 6 bars of soap, whereas the case (Pack C) contains either 42 bars of soap or 7 packs. In principle what you are suggesting is that say Cola is tha same product whether its packed as a 330ml bottle, 330ml can, I lite or 2 litre PET bottle.... they aren't.

if your wholesale customer comes to you and asks for 42 bars of soap.. they are expecting to get 7 packs each with 6 bars of soap in, not 42 individual bars of soap. I doubt a Wholesaler would be that keen on getting 42 bars of soap chucked in a box.

the warehouse can break down a case into packs, or individual bars of soap if they wish. Mind you some suppliers have got wind of this so no expressly don't put a barcode on mulitpacks, or put a message on to the effect this item must not be sold individually

by trying to treat them as the same product you are having the stock control issue, if you treat them as individual products the stock control issue disappears.

if you must treat them as the same product with the same barcode then you are going to have to request user input at the time the barcode is scanned to identify what variant of the product they are selling.. with all the potential problems that comes with that approach..

if you continue with your approach then you will need some recursive element in your table which identifies that product A is the same as product B, and product B is the same As Product C, and there are 6 Product A's in Product B, 7 Product B's in Product C and so on.

8. Registered User
Join Date
Jan 2005
Location
Nanded, India
Posts
397
Thanks Healdem again.

I appologize for my late reply.

I will start entering them as separate items as thier barcode value are changing. I will let you know once few of them are finishes and I turns at calculating stocks.