Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: Trying 2 Set Up an Inventory Control DB...

    ....and not sure how to execute something.

    A friend of mine owns a business at which he constructs several types of trailers. Each trailer has a different parts list, but all of the parts used to construct all trailers are listed in the main 'Master Inventory' table.

    What I want to be able to do is to tell Access to decrement the Master Inventory table by the number (and types) of parts required to build each trailer. For instance, if I know 'Trailer One' is constructed with 5 left handed widgets and 5 Snickers bars, I want the 'quantity' field in the "Left Handed Widgets" and "Snickers bars" lineitems in the "Master Inventory" table to be decremented by 5 when I tell Access to "Build Trailer One."

    I'm just not sure how to proceed. I'm fairly sure I will have to build one table for each kind of trailer, and have it contain the parts list for each trailer - and then have the quantities for each line item decrement the quantities for the corresponding line items in the "Master Inventory" table. I just don't know how to go about it. Sorry for the bother, and thanks for any assistance provided.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I'm Thinking down this line

    Trailer Table
    ------------
    TrailerID
    Trailer Name
    Trailer Comments More info about the Trailer only

    What Parts in a Trailer
    ----------------
    What Parts in a TrailerID
    Master InventoryID
    TrailerID <= Trailer Table the link to the tailer table
    Qty
    more stuff about Building the Trailer

    now join then

    Order Trail
    ----------
    Order TrailID
    What Parts in a TrailerID
    QTY

    So
    stock used is

    parts used = [Order Trail].[qty]*[What Parts in a Trailer].[qty]

    $$$ used is
    Trailer cost $$ = [What Parts in a Trailer].[qty] * [Master InventoryID].[$$$]

    this just off top of head need more thinking
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, it would actually be easier to take the entire process out one notch and account for inventory RECEIVED as well.

    You could make entries into your incoming invoices table, and never have to touch them again.

    When an order is made, a quantity for each inventory item is entered along with the order. To return the current inventory level, you would then subtract the sum of a given part in the orders table from the sum of a given part in the received table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jun 2004
    Posts
    3
    That's just it. I don't think it's going to be that easy.

    In other words, when stuff is 'added' to the inventory, it will be added as the parts. But the only time that things will ever be subtracted from the inventory (other than when people take stock of the inventory) is when a trailer is built. So, what we want to be able to do is to create a macro of some sort that will decrement the Master Inventory db by the correct number of EVERY part required to build that trailer.

    For instance, if there's 20 different things required to build the trailer, and each of those 'things' are needed in a quantity of five, then I want to be able to have the database decrement the Master Inventory database by 5, for each of those 20 items.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think you understood my proposal.

    You WON'T NEED to decrement your "master inventory" levels if you track what trailers have been built.

    Say you get an incoming invoice in with 3 widgets and 4 wingnuts.

    You built a trailer that takes 2 widgets and 1 wingnut.

    You want to find out what the current inventory of widgets is:

    DSum("widgets", "incoming") - DSum("widgets", "order_dtl")


    Naturally, you SHOULD have a primary key associated with your parts, so you could create a query similar to the following:

    SELECT partid, SUM(incoming.widgets) AS original_inventory, SUM(order_dtl.widgets) AS used_inventory, SUM(incoming.widgets) - SUM(order_dtl.widgets) AS current_inventory
    FROM incoming LEFT JOIN order_dtl ON incoming.partid=order_dtl.partid
    Last edited by Teddy; 06-22-04 at 12:14.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2004
    Posts
    3
    Alright, I think I understand what you're saying. (I'm NOT trying to be stupid, honest! *LOL* I seriously AM this new at it.)

    It seems to me that I'm going to be creating a database that will be the 'original' inventory. Then, I'm going to add the number of incoming parts to that, and decrement the number of outgoing parts, and that'll give me my current inventory.

    What I'm having trouble understanding (and you might have said it - like I said, I'm this new *LOL*) is how to make Access equate building a trailer with decrementing numerous parts. The people I will be building this for DO NOT want to go in and mark that 2 widgets and one wingnut has been used. They want to somehow press a button or notate that one trailer has been built, and then automate the task of decrementing all invididual parts.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's not to big of a deal. Assuming you have a table that describes how much of each part is required for each type of trailer, you simply include that table in the query and use it to fetch the required information.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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