Results 1 to 7 of 7
  1. #1
    zimzala20 is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4

    Break up equipment order on form

    HELP!!!



    I place bulk orders for equpment that upon arrival at our warehouse get distributed to multiple locations/agencies. I want to enter the initial bulk order information into access and when I go to create reports(receipts) it will break down the order to each agency.

    Question -

    I can't figure out how to associate te bulk order number with each group without entering all the information over and over for the different agencies when that is all that is changing.

    Thank you in advance for any help out there!!!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How did you generate the bulk order to begin with? You had request for supplies from each agency? You are fulfilling these requests? Are there records for these requests in the db?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    When you have an incoming bulk order do you distribute it completely? or do you retain some on hand?

    If you are basically the clearing house and every incoming bulk order is distributed as you get it you should have a table structure like:

    Code:
    BulkOrders (BOID autonumber PrimaryKey)
    BOID BO_RecDate other information ------>
    1    1/1/2011
    2    1/2/2011
    
    Items (ItemID autonumber PrimaryKey)
    ItemID ItemName
    1      ItemA
    2      ItemB
    
    BulkOrderItems (BOIID autonumber PrimaryKey)
    BOID BOIID ItemID ItemQty
    1    1     1      500
    1    2     2      1000
    2    3     1      1000
    2    4     2      750
    
    Locations (LocID autonumber PrimaryKey)
    LocID LocName
    1     LocationA
    2     LocationB
    
    OrderDistribution (DistID autonumber PrimaryKey)
    BOIID DistID DistQty LocID
    1     1      200     1
    1     2      300     2 
    2     3      300     1
    2     4      700     2
    If you set it up like this then you can create forms with subforms that will inherit the parent item (the primary key fields) so that all you are storing is the primary key and you don't have to re-enter any information.

  4. #4
    zimzala20 is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4
    Well as you can tell from by request I am fairly new at Access. I did take a course on how to work it but I am still way behind. I have yet to enter any data so I can still change things around. What I have set up so far is;
    Tables
    --Tables for drop down boxes on master table
    --One Master Table that has all possible fields for order and distribution
    --So from above suggestions I have item ID, item Quantity, and Group ordered for, but I don't know how to break down say a 500 qty order into 10 orders of 50 for 10 places. I may have some stock left over so that may be an issue too.

    Forms
    --Order an Distribution Forms

    Query
    --Order query
    --Distribution query

    Reports
    --I was hoping once I figured out my problem above I could create reports for each like a receipt.

    THANK YOU VERY MUCH FOR ANY AND ALL HELP!!!!

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't see a question in here but:

    --So from above suggestions I have item ID, item Quantity, and Group ordered for, but I don't know how to break down say a 500 qty order into 10 orders of 50 for 10 places. I may have some stock left over so that may be an issue too.
    if you look at my example, the last table would be where you keep track of this. A table that tracks which item went to which location and the quantity of the item that went to that location. The only change you *may* want to do is to drop the bulk order ID from this table, particularly if you want to build this into an inventory management application where you are going to track both incoming and outgoing items in your database (in your example you only seemed to care about distributing items you already had rather than keeping track of what was left over and any new items coming in) My example was based on the premise that everything you get in is distributed as soon as it gets to you.

  6. #6
    zimzala20 is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4
    rpeare -

    Thank you for taking the time to help here. I read into your suggestions more. So with your example you recommend making 5 seperate tables? If so when I enter the information in your last table do I enter the data as a number as shown or the actual data, say LocatioA for LocID.

    While I plan to be giving out most of the equipment there will be times where parts of orders will sit in warehouse as inventory for replacement or such. Could you elaborate on changes to your layout?

    You mentioned that I can create forms from the above. Just to make sure I am basing my forms off the tables. Not a query I would create?

    From here I would like to create 'reports' for what department has been issued what, as well as, 'receipts' for personnel to sign upon pick up. Any hints there? Thank you again!!!

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Thank you for taking the time to help here. I read into your suggestions more. So with your example you recommend making 5 seperate tables? If so when I enter the information in your last table do I enter the data as a number as shown or the actual data, say LocatioA for LocID.
    You shouldn't be doing data entry on your tables at all, you should build forms to do that. If you build forms you can set it up so that the the child table inherits the ID from the parent table. For instance, the tables one and three of my example, tblBulkOrders, tblBulkOrderItems. The table tblBulkOrderItems can automatically inherit the bulk order ID (BOID field from the tblBulkOrders) so you don't have to enter it at all. Further, you can have combo boxes that just have the names of your items so your user can type in the item name (usually the first three characters will do) so, again, you never see the ID on your form, you just see the item description.

    The changes I would make if you want a true inventory management application is to have a table or tables for your receipts of items (which the tblBulkOrders and tblBulkOrderItems would be) and have other table (tables) which track your outgoing items. For instance if your location 1 requests 3 different items from inventory you can create one order with three items on it, or if that's not the case with your business you can just have a straight line item request where you only have one table with the item being requested and how many were taken.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. a question about Equipment Repair Database
    By Nokia N93 in forum Forms
    Replies: 1
    Last Post: 03-05-2011, 12:31 PM
  2. Replies: 1
    Last Post: 11-07-2010, 11:04 AM
  3. Help With Purchase Order Form
    By SpeedyApocalypse in forum Forms
    Replies: 29
    Last Post: 04-09-2010, 07:06 PM
  4. equipment and people check in and out
    By aaronlalonde in forum Access
    Replies: 0
    Last Post: 07-29-2009, 08:28 PM
  5. design equipment management
    By chanlongs in forum Database Design
    Replies: 0
    Last Post: 07-14-2009, 06:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums