Results 1 to 8 of 8
  1. #1
    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…

    Thanks in advance.
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks R937 for your quick reply.

    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.../

    Please advice with your great ideas.

    Regards.



    Please advice.
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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.

    Thanks again for advice
    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote 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.

    ... think about it
    .. 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. #8
    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.

    Thanks for your valuable guidelines.
    With kind regards,
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •