Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Need Help in stock control in Access DB

    Hello,

    I'm currently designing a database for a small designing and printing business, what I've done so far is create the following tables:
    Client;
    Employee;
    Products;
    Selling Transaction Master;
    Selling Transaction Details;
    Supplier;
    Supplies/stock Master; (this one contains the Material ID along with other fields)
    Supplies/ stock details; (this table contains the quantity ordered from supplier)
    Material's table; (these are the supplies supplied by the supplier) this table has fields as follows: material ID, Name, Order Level, Cost, supplier ID.)

    and i did all the links (relationships) necessary>

    My problem is:
    The products i have in the products table involve the use of material ofcoarse, to take an example, i have posters as a product it involves the use of paper and ink and lamination, those are the materials, I cant find a way to link those together so that when a product is ordered by a client the material used are deducted from the number in stock.

    What i need:
    - Help in a way that the "Quantity" Entered in the "Selling Transaction Form" Is deducted from the the "number in stock" in the "Material's Table", which also appears in the "Stock order Form";
    - Also, the "Quantity ordered" from supplier in the stock details Table to be added to the "number in stock"
    -Finally i want a message to alert to the need of ordering stock , and if possible use mail merge to automatically create messages to suppliers using details from the supplier's table: this is when the "number in stock" <= to the "reorder level" in the material's table.

    PS: I already created my input forms, and the selling trans. forms and the stock order form!

    I hope i made my work clear enough! I know it seems too much to ask (to read and answer) but really in whole its only one core problems and if solved everything else would work good for me !

    I would really appreciate your time and effort !
    thank you !

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is a classical "composite items" problem.
    One solution could be:

    1) Create a table ("Items" for instance) that will list all the items you sell or use to make something you sell (a stock), like this:

    SysCounter, Autonumber, Primary Key
    Name, Text
    Description, Text
    SupplierID, Long
    Quantity_In_Stock, integer ' Long if you are an optimist.
    Price... etc.

    2) Create a table ("Composition" for instance), like this:

    SysCounter, Autonumber, Primary Key
    ItemID, Long, Not Null, Indexed duplicates allowed
    ElementID, Long, Not Null, Indexed duplicates allowed
    Quantity, Long, Not Null, Default value = 0

    Now, for each item (simple or composite) fill this table:
    - ItemID receives the ID from a row in the Items table (a row "containing" the simple or composite item).
    - ElementID receives the ID from the same (simple item case) or another (composite item case) row in the item table (a row containing a simple item (can be the same as above if it's a simple item).
    - Quantity is the quantity of the element that you need for manufacturing or selling this item.

    - If an item is only made of one element, there is only one entry (row) in the Composition table and ElementID = ItemID.
    - If an item is composed of several elements, there is a row for each element in the Composition table.

    An over-simplified example could be a computer. Let's suppose that I assemble and sell computers and computer peripherals.

    I can sell a computer which is made of:
    - 1 motherboard,
    - 1 processor,
    - 4 SD-RAM units,
    - 1 power supply,
    - 1 hard disk,
    - 1 graphic card,
    - 1 CD-ROM drive,
    - 1 sound card,
    - 1 case,
    - etc...

    I can also sell a workstation, which is made of:
    - 1 computer,
    - 1 keyboard,
    - 1 mouse,
    - 2 loudspeakers,
    - 1 LCD monitor
    - etc...

    Or I can sell a simple part (say a CD-ROM drive).

    1) For a simple item (ex. CD-ROM drive):
    - In the table Items:
    SysCounter: 4
    Name: CD-ROM
    Description: Sony CD-Rom drive model XYZ
    Supplier: 10214 ' ID from the Suppliers table.
    Quantity_In_Stock: 5
    Price... etc.

    - In the table Composition:
    SysCounter: 1 ' primary key.
    ItemID: 4 ' Same as the ElementID as it is a simple item.
    ElementID: 4 ' Same as the ItemID as it is a simple item.
    Quantity: 1

    2) for a composite item (ex. Computer):

    - In the table Items:
    SysCounter: 32
    Name: Computer
    Description: Basic computer 2.8 GB processor, 1 GB SD-RAM ... etc.
    Supplier: ' ID for myself.
    Quantity_In_Stock: 2

    - In the table Composition:
    SysCounter: 20 'primary key
    ItemID: 32 'foreign key from table Items for Computer
    ElementID: 4 'foreign key from table Items for CD-ROM
    Quantity: 1

    SysCounter: 21 'primary key
    ItemID: 32 'foreign key from table Items for Computer
    ElementID: 12 'foreign key from table Items for SD-RAM
    Quantity: 4

    SysCounter: 22 'primary key
    ItemID: 32 'foreign key from table Items for Computer
    ElementID: 7 'foreign key from table Items for Processor
    Quantity: 1

    Etc...

    3) For a super composite item (ex. Workstation)

    - In the table Items:
    SysCounter: 114
    Name: Workstation
    Description: Workstation ABZ-119
    Supplier: Myself
    Quantity_In_Stock: 1

    - In the table Composition:
    SysCounter: 215 'primary key
    ItemID: 114 'foreign key from table Items for Workstation
    ElementID: 32 'foreign key from table Items for Computer
    Quantity: 1

    SysCounter: 216 'primary key
    ItemID: 114 'foreign key from table Items for Workstation
    ElementID: 91 'foreign key from table Items for Keyboard
    Quantity: 1

    SysCounter: 224 'primary key
    ItemID: 114 'foreign key from table Items for Workstation
    ElementID: 42 'foreign key from table Items for Loudspeaker
    Quantity: 2

    Etc...

    Now we can manage the system using SQL, VBA or both, but we shall need some basic tools (don't try to type them, they are just pseudo-code):

    1) Is an item simple or composite.
    Code:
       Composite = (SELECT COUNT [SysCounter] FROM [Composition] WHERE [ItemID] = <some ItemID>) > 1 ' Returns 1 if it's a simple item, >1 if it's a composite item.
    2) Retrieve all simple items and their quantity for a composite item (should be a recursive function as a composite item can be made of other composite items).
    Code:
       RecordSet = (SELECT [ElementID], [Quantity] FROM [Composition] WHERE [ItemID] = <some ItemID>) ' the query returns 1 or more ItemID's
       For Each ElementID In Recordset.ElementID
           subRecordSet = (SELECT [ElementID] FROM [Composition] WHERE [ItemID] = ElementID)
       ' We end up with a list of the primary keys of all simple items that compose a composite item
       ' or just one primary key if the item we are analyzing is a simple item.
    3) When we have the list of primary keys for all simple items we can update the Items table in several ways, depending on the operation:

    b. I sell a Computer that was already assembled: Quantity_In_Stock = Quantity_In_Stock -1 in the Computer row of the table (but I might need to re-order spare parts to assemble another one).
    c. I assemble a computer: Quantity_In_Stock = Quantity_In_Stock-Composition.Quantity in each row of simple elements (processor, RAM, etc.) that compose the computer and Quantity_In_Stock = Quantity_In_Stock +1 in the Computer row.
    d. I sell a CD-ROM drive: Quantity_In_Stock = Quantity_In_Stock -1 in the CD-ROM row of the table

    It might seem awfully complex but in reality it is very simple once you have the basic functions to manage the system.

    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sinndho, you're a machine!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2009
    Posts
    3

    Thank you Thank you Thank you

    Oh my God , I cant thank you enough for your detailed help, It does seem awfully complex, Now i'm ganna try and do the tables as u described, but the thing is i'm so not familiar with SQL and how to deal with it (and i also dont know what pseudo code is), so if it is possible that u kindly tell me where do i type in these codes you've written in your post, and would it work if i copied and pasted them? or it wouldnt work?

    p.s: I'm really unexperienced and this is a school graduation project so your help is so appreciated!!

    Thank you again
    Have a nice day!!

Posting Permissions

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