Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2016
    Posts
    22

    Answered: Forms, SubForms, & Data Normalization best practice advice

    Hello all,

    I am creating a rather complex database to keep track of teaching supplies that are checked in and out for my program. I have a CheckOutmain form that has 2 subforms, 1 for barcoded items, 1 for misc items, in addition to team information on the form itself.

    The CheckOutmain form uses a listbox to pull the team info in from the table (an excel spreadsheet that changes by semester), and on the subform, it pulls the description of the materials in using a Dlookup based on the BarcodeID. (everything right now is dummy data, to be replaced once I can get it working).

    Here is what the CheckOut looks like now. Once I get this working I can do the Checkin.

    Click image for larger version. 

Name:	checkoutimage.PNG 
Views:	4 
Size:	87.0 KB 
ID:	17238

    Here are my questions.

    1. How do I normalize the data so that I don't have to have the team number and student names attached to every barcode item? This would duplicate a huge amount of data, which I know is wrong. There are 3 different teaches, so I have to have at least team number (not a number but a string) and teach number. Right?

    2. What is the best way to pull the 3 different Form data together? Or do I? Do I keep 1 table that is barcoded info and add Team and Teach number? Keep the tables separate? Join them? I know how to take the CheckOutBarcoded data and push to a separate table and erase the form table, but is there a better way to start with a blank subform table?

    Essentially I am asking for some advice on best practices here. I know enough to do it kludgy, but would prefer to do it right.

    If it helps, I have attached the entire access database (all data is dummy data.)

    Thank you for any advice you have to offer!

    NVTCInventoryDatabase.zip

  2. Best Answer
    Posted by weejas

    "You can add a yes/no field to the table and run a simple update query to set all values to yes to deal with existing records. After that, you can add a stage after processing to set all flag to yes where they're currently no.

    Regarding real time information, you can always set the DLookup to use a query that generates the sum of the values in InventoryBalance and all unprocessed records in InventoryMovement.

    All that being said, it's probably easier to update InventoryBalance each time there's a movement!

    your code gave me an error
    Apologies - I've been up to my elbows in Lotus Notes all day! Switching between programming languages can get confusing. Glad that you managed to make it work."


  3. #2
    Join Date
    Dec 2016
    Posts
    22

    And I didn't give the form a parent-child relationship

    I just realized I did screw up in creating the sub form (the child) and not give it a parent-child relationship with the main form. Fixing that, making two fields important, the team number and the teach number. That actually should solve a lot of my issues.

    I would still like to have a conversation about data normalization in this context just to make sure I am following best practices.

  4. #3
    Join Date
    Dec 2016
    Posts
    22

    Except, that didn't do everything I needed

    The parent child relationship won't quite work, because the TeachNum field is unbound.

    This MainForm has a Record Source of the read only excel table of students so that I could do a combobox lookup. But that means that the TeachNumber, TeachDay, TeachDate, TeachTime, KitName, and CopiesNeeded fields were being updated via a VBA record function and button.

    Would I be better off storing these variables in a table natively, and using a DLookup for the student names?

    As you can tell, I have enough knowledge to screw this up, but have some gaps in the idea of efficient and best practice on Access.

    What would an expert recommend?

    Click image for larger version. 

Name:	Capture.PNG 
Views:	5 
Size:	60.0 KB 
ID:	17240

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Can you post a summary of the tables that you have - names, columns, keys and a brief description of what they'll store? Also include any relationships that you have defined.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #5
    Join Date
    Dec 2016
    Posts
    22
    Thank you Weejas,
    I attached a picture of the relationships between the 5 tables I currently have working. It shows all the columns I have.

    Attachment 17244

    The TeachingTeams is an excel doc, because that will change by semester. It is there to provide DLookups for the checkout process to make sure the TeamNum (not a number but a string like 'S7') is entered correctly with a name verification. Only the TeamNumber and TeachNumber is saved in the "CheckoutSubBarT" and "CheckoutMiscT" tables. I saved both, so that the Parent-Child relationship in the forms would check both in the CheckInmain and CheckOutmain forms.

    In the InventoryMaster table, I have the following columns:
    BarcodeID - a number which will be an actual barcode on items
    LocationID - a string which will locate the item in the storage room
    ItemDescription - string that describes the item. This is the only place this is saved, but it is DLookup'ed in forms
    BeginningInventory - number of items on hand at the beginning of the year
    EndingInventory - number of items on hand at the end of the year
    CurrentOut - number that will (eventually) be placed here via update query based on the CheckedOut in CheckoutSubBarT
    LossCount - difference between Beginning and Ending Inventory (do I need this in the table, or create via report?)
    UnitCount - a description of the units we are counting for the BeginningInventory
    SubjectType - Math or Science teaching resources
    FoodSafe - a yes, no in order to specify


    The workflow is for workers to be able to open the "CheckOutmain" form, type in the TeamNumber and TeachNumber, which loads the students names for verification, then they will scan in the barcoded items to be checked out and enter the "Requested" number, hit an 'update' button [because I need something to trigger the query] which will pull counts from the BeginningInventory and CurrentOut columns, and show the 'Available' amount.

    From there, they will enter the "CheckedOut" amount and finish.

    The MiscItems are for anything not barcoded and consumable. Keeping them separate because they are not barcoded (but they are inventoried but since they are not barcoded they won't pull up in the CheckOutSubBartT table.)

    I think I have made some good decisions in setting this up, but am very open to criticism and suggestions.

    From the picture above, you can see the Table that is connecting everything is the CheckInCheckOut table.

    What do you think?

  7. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Apologies - I hadn't spotted that you'd attached the database.

    This database is in dire need of normalisation. Firstly, any time you find yourself numbering table columns (e.g. StudentName1, StudentName2 etc), you should strongly consider breaking this into three or more tables. From first glance, I would suggest splitting TeachingTeams into a number of other entities:
    • Teams (ID, Team number, [Semester], [Section])
    • Students (ID, Student name, Cellphone, Email)
    • Teachers (ID, Teacher name, School name)
    • TeamTeachers (TeamID, TeacherID)
    • TeamStudents (TeamID, StudentID)

    You might also want to split out Semesters into their own table, and have another intersection table that joins them to teams.

    Secondly, related to the above, your table InventoryMasterT is a hybrid. It appears to contain item information and stock information. You also appear to have duplicate items (going by the descriptions) - these records appear to exist so that you can hold the same thing in multiple locations. These should be kept in different tables:
    • InventoryMaster (ID, BarcodeID, Description, UnitOfMeasure, IsFoodsafe, SubjectType)
    • StockBalance (ItemID, LocationID, QtyOnHand)

    You ought to consider moving all items into the same master table, regardless of whether they have barcodes, and provide an alternative way for users to select them in the same form.

    To record movements and stock snapshots, I believe that three tables are required:
    • StockRequests (ID, Requester (linking back either to teacher or team), RequestDate, IsProcessed [flag for updating StockBalance.QtyOnHand])
    • StockMovements (RequestID, ItemID, LocationID, MovementQty[, MovementType - either allow positive and negative values in MovementQty, or restrict it to positive values and use movement types to determine whether stock is incremented or decremented])
    • StockHistory (ItemID, LocationID, Period, OpeningBalance, ClosingBalance)


    Any differences should be calculated with queries and reports.

    This is a very ambitious project - I realise that I have suggested a lot of changes, but I believe that they will make it easier to maintain the data and extract meaningful information from it. Please come back to me if you have any more questions.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  8. #7
    Join Date
    Dec 2016
    Posts
    22
    Weejas,
    Thank you very much for the advice. I will act accordingly.

    The reason for the spreadsheet and Student1, 2, etc is because we collect that information via a Google Form, and I felt keeping that info together for the semester would be easier. That table is the only one that will be replaced every semester with completely new information. Given the temporary nature of that information, would you still split it up?

    As to everything else, I am on it. I have one question right up front on this (so that makes a total of 2). It is a question from lack of creativity or experience. If I pull the misc items into the InventoryMaster table, those items will not have barcodes. Is it okay to have blank barcode fields when that would be the key field? Or should it not be the key field? (oops, that is 3, but they are related )

    Thank you very much for your advice. I know it is ambitious, but in the long run it will benefit my program tremendously, so it is worth the time to do correctly. I appreciate your help.

  9. #8
    Join Date
    Dec 2016
    Posts
    22
    Weejas,

    Wait, I just thought deeper about your statement in the Student section, and connected it with my statement that that data is "temporary". That would mean that once we replaced the data with the new semester, new code numbers, EVERYTHING prior becomes unattached to the team number!

    Hence, your statement about the Student info needed to be normalized. It can't be temporary, or I screw up the intent of the database. I create fields that are linked to nothing.

    Boom. Why data normalization is so critical.


    Okay, so scratch my first question above. It is irrelevant. I need to fix the student info too.

  10. #9
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    You're welcome! Also, glad to see that the reasoning behind my advice is making itself clear to you.

    In an ideal world, barcodes could be the key for an inventory table. However, as you've found, we haven't got around to barcoding everything yet. Therefore you probably want to avoid using them for the primary key. I would advise you to index the barcode field, though - disallow duplicates and allow nulls. This will prevent anyone from entering two items with the same barcode, which would cause problems with the lookup process.

    Regarding the temporary nature of the students, there's nothing to stop you from collecting the initial information via a Google spreadsheet. I would advise writing an import process that prompts for a file, updates existing records and adds new ones. (Just out of interest, would you be able to use SSNs as primary keys for students?)

    Data normalisation can be a pain - when we moved from a DB2 system (approximately third normal form) to an Oracle system (approximately fifth normal form) at work, we had to re-write a lot of peripheral interfaces. One such interface used to extract standard vendor prices. From the old system, obtaining four columns of data required joining two tables together. To get the same four columns from the new system, we had to join nine tables...
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  11. #10
    Join Date
    Dec 2016
    Posts
    22
    We want to avoid storing SSN's. We could ask for student ID's, however.

    I thought I was doing great on this project, and then read an article on data normalization and realized just how badly I could mess it up if I didn't stop and get some competent advice. Thank you for your help so far. I will work on it this week and post an update later. (and I can't imagine the difficulty of needing to join 9 tables to get 4 columns of info. ouch.)

  12. #11
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Student ID is a good candidate for primary key.

    I thought I was doing great on this project, and then read an article on data normalization and realized just how badly I could mess it up if I didn't stop and get some competent advice.
    You have no idea how many people fail at this stage! They fall for the Sunk Costs Fallacy - as they've invested so much time/money/political capital in $ReallyBigProject, they couldn't possibly change their minds now(!)
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  13. #12
    Join Date
    Dec 2016
    Posts
    22
    Thanks Weejas. I have some programming experience, but not Access. From that experience I learned the hard way that sometimes the easiest way to make great progress is to start over. Great projects never come out of constant success, but from failure and starting over. Luckily, I hadn't gotten as far as failure yet.

  14. #13
    Join Date
    Dec 2016
    Posts
    22
    Weejas,
    Okay, ran into a new problem that I can't find an answer to anywhere in my searching.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	2 
Size:	80.9 KB 
ID:	17251

    I attached a screenshot, because I noticed some weirdness in the Query. Notice that Access is changing the name of my fields from what I typed to the error message? That is odd.

    Also, the Select Query is perfect, it is only when I change it to the Update Query where the error message occurs. In case it helps, here is the Select Query SQL code. All I have done is hit the "Update" button and type in the location you see in the screenshot.

    Am I doing something stupid in turning this into the update?

    Thank you again for all your help. I have made terrific progress and it is turning out very well.

    SELECT InventoryHistory.Barcode, InventoryBalance.QuantityonHand, InventoryMovement.MovementQuant, InventoryMovement.InOrOut, [QuantityonHand]+[MovementQuant]

    AS Expr1 FROM InventoryBalance INNER JOIN (InventoryHistory INNER JOIN InventoryMovement ON InventoryHistory.Barcode = InventoryMovement.Barcode)

    ON (InventoryBalance.BarcodeID = InventoryMovement.Barcode) AND (InventoryBalance.BarcodeID = InventoryHistory.Barcode)

    WHERE (((InventoryMovement.InOrOut)="In"));

  15. #14
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    I've never seen it do that, either.

    However, you don't need to include the InventoryHistory table in this process. This table should only be updated once per period, and should then only be used for reports.

    A simpler approach:
    Code:
    SELECT ib.Barcode, ib.QuantityonHand, im.MovementQuant * IIf(im.InOrOut = "In", 1, -1) AS EffMovementQuant, ib.QuantityonHand + (im.MovementQuant * IIf(im.InOrOut = "In", 1, -1) AS ResultingBalance
    FROM InventoryBalance AS ib INNER JOIN InventoryMovement AS im ON ib.Barcode = im.Barcode;
    Unless you're processing movements in real time, you might want to add a flag to indicate where a record has been processed to InventoryMovements. You can then restrict the above query to just show movements that haven't been processed.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  16. #15
    Join Date
    Dec 2016
    Posts
    22
    Weejas, that does help, but raises another question. I understand now how to use the flag (use an if,then statement to ignore or not ignore) but how do I create a flag after processing?

    I do want to process movements in real time because I need student employees to be able to see "nope, I can't check out 20, we only have 10 on hand". To the best of my knowledge, that requires storing the updated amount in a column so the DLookup can pull that value. I most certainly can be wrong here.

    Also, just in case other people need this information in the future, your code gave me an error, but I rebuilt in in design view to check. Here is the updated SQL code that worked. I am not familiar enough with SQL to troubleshoot in the native code. The error was a "missing operator" and it looks to me line it is was in the FROM line.


    SELECT InventoryHistory.Barcode, InventoryBalance.QuantityonHand, [MovementQuant]*IIf([InventoryMovement].[InOrOut]="In",1,-1)
    AS effMovementQuant, [InventoryBalance].[QuantityonHand]+[MovementQuant]*IIf([InventoryMovement].[InOrOut]="In",1,-1)

Posting Permissions

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