Break up equipment order on form

[zimzala20]

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!!!!

[June7]

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?

[rpeare]

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:

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.

[zimzala20]

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!!!!

[rpeare]

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.

[zimzala20]

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!!!

[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.

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.