Results 1 to 4 of 4
  1. #1
    opopanax666 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    27

    Help with design

    Hi everyone,

    I'm trying to implement a system in our warehouse to label our different packaging and transport structures (e.g. boxes, pallets, pallet jacks,...)(all of which I will be calling "stuff" from now on) with barcodes to simplify the calculation of the net weight of goods. But since I'm no good at designing user-friendly interfaces , I'd like your input.

    What I have in mind is this: the weight of the packaged goods (goods + boxes + pallet) is input on a form, then the stockroom employee presses a button (labeled "Tare", or showing a pic of a barcode) which brings up an other form, and they start scanning the appropriate barcodes. For the boxes I'm using an average weight, so they only have to scan the barcode "box" (printed on a sheet next to the scales); an other form would pop up, and on this they would push the button for the amount of boxes on the pallet (1-20). The same for pallets, but they would have a default amount of "1", so no pop-up. As for the pallet jacks, they would be "barcoded" individually, and also have default amount "1", so no pop-up.
    I have a table listing all the "stuff" along with their barcode, including a column with a code to indicate whether to ask input or not ("y" or "n").

    The problem now is: how do I store the running total (table, recordset,...) of the scanned "stuff", how do I reset this after completing the calculation, and how do I make the program check whether to ask input of the amount?
    Some things to consider: the barcode scanner I intend to use has an automatic "carriage return", and the design should be "relatively" dummy-proof.



    Any help/insight would be greatly appreciated!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Here is what I would do

    Have one table for you transport structures

    Code:
    tbl_Categories (a table to store the different types of structures)
    CatID (autonumber)
    CatDesc (text)
    CatAllowMult (yes/no, a field to tell your database when to allow a pop up window to define the amount you're using on an order)
    
    CatID CatDesc    CatAllowMult
    1     Box         -1 (or yes depending on how you view the table)
    2     Pallet      0
    3     Pallet Jack 0
    The next table would be a table to store weights and sizes of object:

    Code:
    tbl_Structures
    StructID (autonumber)
    CatID (number, foreign key to tbl_Categories)
    StructDesc (text)
    StructWeight (number, double)
    
    StructID CatID StructDesc  StructWeight
    1        1     BoxType1    1.1
    2        1     BoxType2    1.5
    3        1     BoxType3    2.1
    4        2     PalletType1 5.4
    5        2     PalletType2 6.8
    6        3     JackType1   4.5
    7        3     JackType2   7.8
    From here it depends on whether you want to keep your data for historical reference or not. If you don't you can append all your information to at temporary table you purge of data for each new order, or you can create a history table so you can go back and see what shipped on what date and to whom. If you want the more robust route you will probably want a customer table, an order table (which would store the customer ID and the date of the shipment among other things, and an order items table (which would contain the order ID and all of the individual items sent to the customer on any given order along with the quantity sent. From there you can have a query figure out the total weight of an order based on what items are entered and the weights you've set up in your tables.

  3. #3
    opopanax666 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    27
    Hi rpeare, and thanks for the reply.

    My problem isn't so much with the structural side of the tables (my existing tables pretty much follow your examples), but with the logic behind user-friendly forms, and how to handle the input from those forms. This is not so much a concrete question, as a question about "best practices".

    - do I use a subform directly linked to a "temporary" table (which is, in my mind, not very flexible), or is there an other way to temporarily store (and display) the subtotal (recordset)?
    - where do I perform the calculations: on the form with calculated fields, or in a query linked to the temporary table/recordset?
    - do I use a popup form (modal or not) to ask the user for the amount, or do I use buttons on the main form (I want to use numbered buttons to avoid keyboard input)?

    The reason for my asking is that I have no clue how other (commercial) programs deal with this. Maybe someone on the forum has developed something similar, and wouldn't mind sharing how he dealt with these issues...

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There are a few ways you can handle it
    The easiest way I can think of to handle this is to put a form footer on your subform and total the lines you want to total. Then on your main form reference that sum. For instance let's say your main form is Frm_Main, and the subform is Frm_Main_Subform. on Frm_Main_Subform footer you have a text box that is totaling a field (let's assume it's the field QUANTITY) with the formula =Sum(Quantity), we will call this field SubFormSum.

    then on your main form you could have another text box (doesn't matter what name) with this as the control source:

    =[Forms]![frm_main].[frm_main_sub]![SubformSum]

    Every time you added a new item to the order (or whatever it is you're tracking) the summation text box on the main form will get updated. This is the easiest way I can think to handle displaying sums of subforms on a form. This way you don't need to do anything fancy with code.

    As far as storing data on temp tables, there's really no need to. I would be inclined to keep all the data you enter so you can go back to your history if it becomes necessary. If you are simply printing shipping labels and some other software is tracking the actual contents then yes, a temporary table would be fine, you would just have to make sure to purge the data in the table before each new order was processed or you'd end up with incorrect results.

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

Similar Threads

  1. Help with Design
    By rbiggs in forum Database Design
    Replies: 8
    Last Post: 07-10-2011, 07:13 PM
  2. Help please in design!
    By Sleepymum in forum Access
    Replies: 1
    Last Post: 01-25-2011, 11:12 AM
  3. Design help
    By jacko311 in forum Database Design
    Replies: 0
    Last Post: 11-12-2009, 05:57 AM
  4. DB Design
    By Merkava in forum Database Design
    Replies: 2
    Last Post: 11-10-2009, 05:51 PM
  5. Design help
    By marix in forum Database Design
    Replies: 1
    Last Post: 04-18-2007, 07:54 AM

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