Hi everyone
I have a database which I use to keep track of stock for the company that I
work for. We are a retail chain with 8 locations at present and a head
office. I need to keep track of quantities of each product for each
location. All locations have the same products. The tables are set up as
follows:
Products(StockCode*,Description,MajDeptID,CostPric e,SellingPrice)
Departments(MajDeptID*,MajDept)
Suppliers(SupplierID*,SupplierName)
ProductsBySupplier(ProductsBySupplierID*,StockCode ,SupplierID,SuppStCode)
Locations(LocationID*,LocationName)
StockOnHand(StockOnHandID*,StockCode,LocationID,Qu antity)
*Note: ProductBySupplierID and StockOnHandID and AutoNumber fields.
ProductsBySupplier lists the StockCode, the supplier's ID and the stock
code used by the supplier (SuppStCode) which differs from StockCode.
StockCode is the barcode of the product, Departments refer to groups in
which items are categorised (for example Department 87 would be the group of
all items which are consumable)
The StockOnHand table is the table that I want to use to keep track of
quantities. Because we're a retail chain, products are being sold,
transferred between locations, received from suppliers etc. We also get new
products in on a regular basis and discontinue items from time to time. We
have a backoffice application which deals with all of the above and
downloads new quantities for the store everyday. I'm able to export to CSV
files and update my Access 2002 database from the CSV files but the problem
I have is in the StockOnHand table.
I'd like to list each product for each location and have a quantity for each
product at each location which will change everyday. The Products table
currently has in excess of 3000 items. How would I go about doing this? If
I take the items as they are now, I can use something like
INSERT INTO StockOnHand ( LocationID, StockCode )
SELECT LocationID, Products.StockCode
FROM Products;
and I'd be prompted for LocationID each time I run it.
What happens if I add a new product though? Would I have to manually go into
the StockOnHand table and type out the product 8 times? Also what if I
wanted to add all the items to all the locations in one go? (for example if
I had ten items in my product list and ten location, I'd want to add 100
records to the StockOnHand table).
Please help, I'm very frustrated, and will be extremely grateful :(
Thank you,
Michael
