Simplify Data Enter

[jiayue]

Hi,

I am new in database & have some basic questions that need your help.

I wanted to set up a very basic product & product code so that it will be easy to be access & enter. I am selling numbers of brand (Enc,Rom,Vig,Juc) with different categories (Shampoo, Shower Gel, HB Lotion, Conditional) and with different volume spec (50ml, 90ml, 120ml, 250ml, 500ml,750ml).

1. E.g in a form, how can I have a simple enter (e.g code???) so that the product can show a full description: e.g
Ench Shower Gel 500ml or
Rom HB Lotion 90ml……..

2. Due to the promotion, we may need to sell banded products (banded 2 products together & sell as 1 product):
e.g Vig Shower Gel 750ml + Enc Shampoo 90ml, qty =1000 pcs
(in this case it is only one selling products but involving 2 sub products).
How can I :
• Key in to the product description on my invoice or delivery note with easier (e.g by code…) same as 1. above
• Related to the transaction of stock of each individual sub products automatically. E.g there will be minus out from stock of 1000 pcs of Vig Shower Gel 750ml & 1000pcs of Enc Shampoo 90ml & than after the banded pack, there will transfer to store & add in to stock of 1000 pcs of (Vig Shower Gel 750ml + Enc Shampoo 90ml).

Many thanks

 

[Rolliee]

Start with the form you wish the user to see while considering the output you want the customer to get. Then as you are doing, build tables. Any data should be input only once. You want a product table where all the products data resides. You want a kit table where the groups of products reside. You want an inventory table where the ProdID points to the product and the custID points to the customer and all the sales data resides. You want an Inventory table where positive numbers of quantities are seen as positive numbers and the sales are seen as negative numbers. The the events table which is the heart. It speaks to the above. The customer and products including kits are 'fed' iin with combo boxes.

Is that what you were looking for?

rollie@bwsys.net

 

[garethfx]

Ive been involved in the development of a large retailers DB. and had to cover the points you aim at. In many cases the colusion of 2 products to make a 1 sale item has toi be monitored very closely. IF you are selling a shampoo (x1) and a conditioner(x1) as a i item pack in the offer it could be re-itemised as a combination of the 2 product codes ie shampoo 2314 conditioner 3571 could be 23143571 that way later you can extract the sales and replenishment information easily - many say it could cause the product table to have problem but i havnt found that as it was put in at the same stage as you are at - the start

hope as a suggestion it will help the thought process