Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007

    Unanswered: Creating stock control system using Access

    im nw here but i was wondering if any1 could help me out with a project of mine which to create stock system using access, i have supplier, product,stock, supplier_order, customer and customer_order. the system have to notify them when to re-order on a particular product when it reaches a certain quatitiy. i really have no clue on even how to begin, the only thing i have done so far is the tables and relationship between the table, Pls help me!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  2. #2
    Join Date
    Feb 2004
    Welcome Tope

    What you will need in the table you have allocated for your "in-stock" records is one field named something like "Stk_Reserve". This will allow you to set a minimum carrying or inhand limit for each individual product or SKU Code.

    I would advise that you identify fast, medium and slow moving products so that the figure that eventually acts as the minimum reserve will be enough to take you through until the next delivery of that product.

    Consider also if the product has an expirey date i.e. food products or those that might be of very high value where carrying large quantities might put under pressure on cash flow or security of the product.

    Once you have made the decison on the RESERVE Figure it is a simple start of having a form with a text box calculating "RESERVE" - "In hand stock"
    A simple message activated by an IIF([Reserve]=>[In hand Stock], "ORDER REQUIRED!","") An there you are

    Hope that helps a little


  3. #3
    Join Date
    Jul 2007
    thanks very much, Gareth buy i am dealing with clothing stock control system, i want the system to notify them when the stock level on particular item is say 3 so that they can reorder the product from their supplier. thanks for you help so far
    Last edited by tope; 07-25-07 at 18:35.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so what have you done so far...

    how do you propose to handle the various product matrices (ie colour, size per range)

    how do you intend to model lead times, which can be significant in clothing?

    how do you propose to notify whom that you are low on stock

    how do you propose to identify what warnings the stock controller has already received and acted on?

    what is the proposed workflow for the stock controller

    basically where have you got to
    is this a real world business problem or is this coursework (seems to be getting a little late in the season for coursework, but y'never know?)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2007
    ok.... so far i have 6 tables all together, namely Product, Supplier,customer,customer order, supplier order and stock and i have linked them together[relationship]. the prodcuct fields are:
    Product_id, product name, description of product, prce and quantity.
    Supplier contains supplier_id, name, address, product id
    customer table contain customer id, name, address, order num.
    Customer order table contains product id, order id and customer id
    Stock table contain stock id, product id and supplier id

    I have a website in which i have to link this to, customer would be able to order online and their order would go in the order table and when they check out a product the it should automatically decucted itself from the product table and when they stock is low then they should be message flashing that a this product is low on stock.
    I am going mad here..................... pls help me anyone. by the way i am helpimg mt mum to build this stock control system but if anyone has a betta or easier way to do this pls do not hesitate to let me know

  6. #6
    Join Date
    Aug 2004
    Cary, NC
    You will need to expand your tables out a bit.

    In the current design a supplier will only be able to offer you one product or you will have to duplicate all the supplier information for every product they sell you (not a good idea!). It is probably easiest to just add the supplier_id field to the product table instead of having the product id with the supplier.

    Same problem with the order num in the customer table, they will only be able to have one order with you.

    The order table will need to be split to allow more than one item per order
    One table with the basic order info (customer id, order id, etc) and a second table OrderItem with order id and product id.

    Your stock table will need at least one more field for the quantity and as mentioned earlier by garethfx, a field for minimum quantity

    Step back for a bit and think about the database design. You have a lot of aspects to the project and they need to be incorporated into the table structure. If you don't create the design good upfront you will struggle at every step of the project.


  7. #7
    Join Date
    Jul 2007
    ok thanks steve i will do that but pls everyone do not hesitate to add any ideas. thanks

  8. #8
    Join Date
    Jan 2007
    Provided Answers: 12
    Tope, it certainly doesn't sound like you're helping your mum... You're doing this for her! Actually, people here are doing too much of the work for you already.

    What you need to do is relax. Take your time and you will realise that you can actually do most of this yourself!

    You mention you are linking to a website - what language are you writing the site in? How many concurrent users are you expecting?

    I advise that you post back everything you have done so far in a clear and concise manner so that we can see that you're trying! The more you do, the more people will be inclined to help you.

    Oh and one last thing: "i dnt thnk u shud write like dis, its v hard 2 read."
    Home | Blog

  9. #9
    Join Date
    Dec 2011

    need help

    i have database contain of 3 tables 1st is products 2nd is buying third is sales i relate between them by the product ID but what i need to calculate the stock between the two tables buying and sales and get to me the stock that reamin in the pharmacy
    in product table contain of
    ID - Productname - PRICE
    in sales table contain of
    ID - ProductID - QTYIN - price per one - total price
    in Buying table contain of
    ID - ProductID - QTYOUT - price per one - total price

    as i do the productID in sales and buying as a combo box

  10. #10
    Join Date
    Jun 2012

    Question Reorder Query

    Hello. I am trying to create a reorder report in Access 2007 for my engineering inventory. I have the following Tables:

    tblSuppliers - list of supplier information
    tblUnitTypes - designates what type of units the product uses, i.e. each, pair, etc.
    tblInventory: List of inventory products, prices, min, max, on hand and descriptions.
    tblCategories: This is the categories the products are in, i.e. engineering supplies, electrical, etc.

    What I am trying to do is create a query that shows me the following:
    Supplier name, Part Number, Description, Price, How many to reorder and Total Price.

    I have written most of the query with a TotalToReorder field that I have written: TotalToReorder: [MaxQty]-[MinQty]-[OnHand].

    FIRST QUESTION: The problem is that when I run the query it also shows me records that don't need to be reordered and have a zero to order.

    What do I need to do to have the query return items that only are zero and above? I have tried putting <>0, is not null and >0 in the criteria area, without success.

    SECOND QUESTION. I also would like this formula to say that if the on Hand qty is =< MinQty to calculate the TotalToReorder above. How would I do that.

    Thank you for any help as I am new at Access and have been struggling to learn.


Posting Permissions

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