Hello all. fairly new DB user/creater here...I am trying to create a form to update a table that will allow me to enter purchase orders for products for my company. I currently order footwear and skates and obviously order the products by size. I have a table that has a list of models, and a table that has various different size grids I use on order forms.(ex Grid A(US sizing) = sz7, sz8,sz9,sz10...etc and Grid B(mondo sizing) =sz24.5, sz25.0, sz25.5, sz25.5...etc). What I would like to do is create a form that pulls a model from the Product Table and then gives me the right size grid so that I can keep track of PO's. It is really hard to type what is in my head so I figured I would start with the above and see if someone has worked with an inventory/ordering DB for a footwear company or similiar. Or if someone can point me to an existing database that I could reference fro my particular task. All I am coming up with when I search is examples of products with no sizes which does not really helo me.
Are all sizes stocked for all products - if so you need some form of relationship between the size and product. All you need is a single size table (its irrelevant whether the shoe is size 11 (GB), 44 (EU), 12 (US) - they all map back to the same product), but you would need to know that shoe X is available in sizes a,b,c.....z
do you care if an order is received for a product that is out of stock
if the size is indendent on the shoe style then I'd suggest using a couple of combo boxes that are bound to the size column of your shoe order. One box is US sizes, the other World sizes, typing in either would set the other. The combo box wizard should help you there.
If the size does matter, and lets face it the girls do lie here, then when the user types the product code, requery the comboboxes so that they load the correct sizes for that shoe. it does mean that you will have to define every valid size for a specific shoe style. A handy technique, would be to disable the comboboxes when a new record is inserted, and then set the data and enable the baxes after a valid product code is retrieved.
the forms before insert event is usefull, as is the shoe style controls on change or on lost focus events.
The relationships to tblGrid ensure that orders for a particular model can only have the sizes specific to the grid for that model (I've assumed that sizes are all unique otherwise you would have to include Grid in tblOrders as part of the fk.
This design will allow you to create a form/subform for an order of a specific date. The main form will show the model and order date. The subform will show the detail of the order i.e. what sizes have been ordered. Note that when you create a new order there would be no lines in the subform and you add the sizes you want.
I suspect the problem with this is you want to see all sizes on the form and the user enters qtys for the required sizes and zeros for the sizes he doesn't want. This could be managed by getting the lines (one line for each size) to be pre-populated on the creation of a new order.
The other suspicion I have is that you want the sizes to appear as column rather than as rows as I have specified. I think this requires a whole different design approach and one that perhaps isn't normalised. Also it could be messy.
Consider the design:
etc (up to the max number of sizes for any model)
The purpose of using a generic title for size is that we can use it for any grid and use some kind of method for appending the appropriate size titles later.
In this design the subform is still based on tblOrders but the sizes appear as columns. The trick is to get the headers to show the appropriate description of the grid/size. This can be achieved by using the DLookup function so that the appropriate titles appear embedded in the form. If a model using gridA is chosen in the order then the titles for sizes for gridA show and same for other grids.
I suppose a cleaner way than using DLookup would be to change the grid table to look like this:
Then you could display another subform immediately above your order detail subform so that the size headings line up with the size columns in the detail subform. This will work providing you turn off horizontal scrolling for the subforms (otherwise you'll scroll one subform and it will be out of alignment with the other).
This second method isn't normalised as there is redundancy. However, I guess there is a finite number of sizes so rules can be broken . The first method is far better for say adding up the total number of shoes ordered.
DTProdSizes 'cross references shoe sizes with products
ProductID } pk fk-pk in DTProducts
SizeID } fk-pk in DTProdSizes
'note we are using a composite primary key (PK) comrpising ProductID & SizeID
'both reference the parent table as foreign keys (fk) in DTProduct & DTSizes
OrderID 'pk autonumber
OrderDetailID pk 'autonumber / possibly a byte field 1....255
OrderID 'fk-pk in DTOrders
ProductID 'fk-pk (with sizeID) in DTProdSizes
if you supply all shoes in all sizes, and you don't track shoes in sizes, then DTProdSizes is not required. However in the real world this is highly unlikely. I just don't like the idea of having to create all those entries in the cross reference table DTProdSizes
DTOrderDetail 'becomes } PK
OrderDetailID 'autonumber / possibly a byte field 1....255 }
OrderID 'fk-pk in DTOrders
ProductID 'fk-pk in DTProducts
SizeID 'fk-pk in DTSizes
WOW!! alot of options here!! thanks for the input, just reading all of the replies is making me a bit smarter!! Thanks for the help. I am unsure on which way to go so I have attached a sample DB of what I am working with. I like the idea of just entering the model qty;s by size in a generic scale and then applying the size description later. Looking at how I currently have my data set up I think this would work best. Although I visually understand what needs to be done I am still a novice at this program. I can be dangerous but at the same time be very ignorant.... Looking at the below attach. what are your thoughts? I have created a basic sample of the layout of the PO/Order entry FORM [ORDER TABLE] I would like but obviously there are no list/combo boxes to auto fill in the data I need when entering the orders. Again I thank everyone for there help and hope the attach further illustrates what I am trying to accomplish.