Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    15

    Unanswered: creating a form for a cross table

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    HTH

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hmm, interesting problem. Here's my stab...

    I think the natural way to struture the db from a design point would be to have tables looking like:

    tblModel
    ModelID
    ModelName
    Grid(fk/tblGrid)

    tblGrid
    Grid(pk)
    Size(pk)

    tblOrders
    ModelID(pk)(fk/tblModel)
    Date(pk)
    Size(fk/tblGrid)

    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:

    tblModel
    ModelID
    ModelName
    Grid(fk/tblGrid)

    tblGrid
    Grid(pk)

    tblOrders
    ModelID(pk)(fk/tblModel)
    Date(pk)
    Size1
    Size2
    Size3
    Size4
    Size5
    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:

    tblGrid
    Grid(pk)
    Size1Desc
    Size2Desc
    Size3Desc
    etc

    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.

    hth
    Chris

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In terms of table design I'd expect to see something similar to:-

    DTProducts
    ProductID 'autonumber pk
    ProductDesc 'the shoe description
    ....etc

    DTSizes
    SizeID 'autonumber pk
    USSize 'str
    EUSize 'str
    UKSize 'str
    WorldSize 'str
    MartianSize 'str
    ......etc

    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

    DTOrders
    OrderID 'pk autonumber
    ....etc

    DTOrderDetail
    OrderDetailID pk 'autonumber / possibly a byte field 1....255
    OrderID 'fk-pk in DTOrders
    ProductID 'fk-pk (with sizeID) in DTProdSizes
    SizeID
    Qty
    ....etc

    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
    Qty
    ....etc

    jsut my 2/1d.....
    Last edited by healdem; 01-09-06 at 07:13.

  5. #5
    Join Date
    Oct 2005
    Posts
    15

    creating a form with crosstable

    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.
    Attached Files Attached Files

Posting Permissions

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