Results 1 to 13 of 13
  1. #1
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22

    Stock codes and Level autofill/adjustments

    Hello again guys.

    I'm having some trouble autofill on one of my forms. The form is a recipe collation form.

    Just a bit of info as to why: As part of trace, it is a requirement that each ingredient of a recipe is logged with the ingredients batch code that is used for the specific ingredients within that recipe. These batch codes are logged in on a inventory table upon delivery. Each delivery is given a unique ID number. This unique number needs to be logged in on the recipe collation.

    I'm trying to have it so that when a recipe is called for collation, the batch codes autofill with the batch that was brought into site first (basic stock rotation rules - first in first out). Screenshot provided of the recipe collation form. KEY and Step by Step guide to how I use it.

    A - First, the user inputs the mix number. This is added to the finished Batch Code
    B - This is the recipe selection menu. When selected, the recipe appears in the subform below with its required ingredients and amounts
    C - SubForm that loads when recipe is selected. Each ingredient and target weight is shown here
    D - Completed batch code based on mix number, julian date, department and job number


    E - a simple "Tag" that is copied and attached to the racks for reference purposes for packing department - not saved, its just for the users needs
    F - ingredient stock code ****** this is for this post.... I need it so when you select the box it will either:
    ** Autofill based on stock values
    ** open a subform showing all the different stock codes and amounts, and when entry is selected, put this code in the F box
    G - Actual amounts - amount used is typed in here and deducted from the stock table
    (I plan later in the year to have a scale linked to the COMM1 port of the system and pulled data from it, but for now, typing it in is fine. one step at a time)
    H - Job number - autonumber that is used for storage and part of the batch code

    So... any ideas guys? its baffling me, doesn't seem to work. Possibly due to the amount of tables used in this system... The following tables are used:

    inventory stock - (INV_STOCK) - stores the ingredients and the delivery dates. Contains the ingredient codes and stock values I need in this
    linked to INV_STOCK:-
    *** ING_DATA - shows the ingrdients information used to store the data

    Recipe table - (tblRecipes) - Recipes are made on a different form and stored here. This stores each ingredient for each recipe, each target amount, and stores is under a unique recipe ID.

    Recipe Collation (tblDailyFG) - the screenshotted form loads recipes up from their unique recipe ID, loads their ingredients in the sub form. Once data is recorded, it stores it under the job id (Daily_FG_ID) or "H" on the screenshot

    Any help would be greatly appreciated!Click image for larger version. 

Name:	Untitled2.jpg 
Views:	53 
Size:	138.1 KB 
ID:	39662

    Crys

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hi Crys,

    Suggest you post a graphic (png) of your tables and relationships to assist readers.
    You may get some ideas from data models at Barry Williams' site Database Answers -- Recipe related data models.

    Also, there are youtube videos on FIFO.

    What exactly does this mean?
    "
    So... any ideas guys? its baffling me, doesn't seem to work.
    "
    Good luck with your project.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I agree with orange, the table structure would help. I do not see an autofill working if you are not recording receipts/usage from each back of raw materials. If you're not recording usage there's no reliable way to get to what you're after. If you're just measuring how much of an ingredient you have in stock, that's also a problem unless you have a way to manually adjust the quantity on hand. For instance if a bag bursts and spills so you throw it all away you'd have to have a way to manually adjust for the weight of the bag.

    Assuming you *are* currently doing that the very minimal calculation you would need to do is look at the total of *all* receipts of a particular raw good minus *all* usage of a particular ingredient to get what's on hand. If the ON HAND total is greater than the total from the most recent receipt you would want to use the 'prior' receipt group first, if the ON HAND total was less than or equal to the last receipt the batch number would be the most recently received batch. Please note this is *not* a great way to do this as it's making a bunch of assumptions, primary among them that you are, without fail, using the oldest stock first. Secondly you may want to create an inventory roadblock. For instance if you do a manual inventory of your stock every month you would only have to look at the receipts/usage to the last roadblock instead of for the entire history of your database.

    If you are not currently logging receipts of materials then your database would need a major restructure.

  4. #4
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22
    Click image for larger version. 

Name:	Untitled3.png 
Views:	43 
Size:	77.5 KB 
ID:	39668Relationships and the tables:

    Key tables and fields for this post are:
    * INV_Stock table - stored all stock codes I need to be pulled as each recipe in the recipe table is loaded. they are linked to the recipe. Thus far there isn't any relationships made to this table. I removed them due to a few errors that cropped up whilst I'm trying to do this**
    * tblRecipes - this is the recipe data called on the collation. It pulls data stored after being created on a separate form. The creation form, lets you "build" recipes by calling data from the Ingredients table (using the Ingredients IDs) and then adding the target weights, instructions ect.
    * tblJobIngredientFG - stores the recipes collated data under job id (Daily_FG_ID) - everything about the batch but amounts and ingredient lots
    * tblDailyFG - stores the collated recipe data (BC, actual amounts and lot codes) and stores them under the Daily_FG_ID - basically stores everything



    There are also a few table on the left hand list that are old need deleting, so any advice please keep the above tables in mind only. The database needs a big clear-out. It has taken months and months to do so bits keep getting added/deleted.

    thanks again

    Cryst

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't see anything in there indicating you are actually receiving goods into your system, all the tables seem to be about usage. How would you anticipate knowing which batch of raw materials your recipes used if you are not recording receipts?

  6. #6
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22
    My apologies. I'm not good at explaining things.

    My system has a few things I failed to mention.

    Goods in
    ---------------
    The goods in recording takes place in the INV_Stock table. Ingredients are recorded by using a prefilled datasheet table that lists the ingredients, ingredient names, their order codes, supplier codes, kg/l per unit, spec sheet ect. The goods in form pulls data from this when you record an entry in goods in via a combo box with the ingredient names and autpfilling out parts of the form. Users note down delivery date, batch code, quality/damage checks and amounts ect...

    The order code is what is used to identify the ingredient. This code is also used in the recipe creation.

    Individual batches upon entry are recorded as a separate generated unique batch or internal batch reference. So for example:

    A bag of flour came on delivery yesterday. When logged in it was given the unique code "4012". The next day the same brand/type of flour came in again, however it was given a unique code of "4013". ** This is because I have to log down their official batch codes, sometimes you may have 3-4 different batch codes in stock. With over 330+ different ingredients each with their own batch codes upon delivery, using a unique numeric code to log them seems to be sensible since some are 4 digits, some can be 11+ alphanumeric batch codes... it can be a nightmare rying to find them...Ect

    These unique codes are what make it possible to trace when ingredients came into site. Since the ingredient order code for the recipe management is the same as what is used in goods in, I thought it would be easy to get a list of each ingredient batches that are in current stock, however I keep hitting walls.

    Is this clearer? It's tricky to explain a trace system...

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    so what happens if, during the processing of a recipe, you use raw materials from 2 or 3 or even 4 different batches, does your table structure handle that? I can't tell just by looking at field names. Are you differentiating the amount of material coming from each batch i.e. I'm using whole wheat flour. I have 50 pounds from batch A0001-0001 and I draw the remaining 200 pounds from batch XYZ-432, is that relevant to your system or just any portion of a batch was used. Further, is it a safe assumption to say any given recipe will use, at most, 2 different batches of received raw materials or can it be 3 or 4 different batches of received raw materials?

  8. #8
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22
    Hit the nail on the head there.... This is exactly what I'm trying to do.

    Currently they are just typing in the batches they are using. When the incident you describe occurs I have to manually go in an alter the batch by adding additional ingredients and their batches. They record on a manual trace document I have created and then I take it, manually change the data record and then remove the stock level. By adding this function with the autofill codes/stock adjustments it will be much easier to use on all parts.

    possibly adding an autonumber based on the job ID and each ingredient and on each ingredient entry called ING_PART and so each ingredient has an entry but if the stock reaches 0, it creates a part2 to complete the remaining. eg:

    job ID: 12003 - breadcakes
    ingredient 120031 - breadcakes - flour ingredient
    ingredient 1 part 1 id 1200311 - breadcakes, flour ingredient, part 1 entry - stock ID 12121, amount 12000 (leaving 4000 remaining)
    ingredient 1 part 2 id 1200312 - breadcakes, flour ingredient, part 2 entry - stock ID 13444, amount 4000 (complete)

    Any suggestions how to implement this and add the auto stock/bc values?

    Kind Regards

    Crystan

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you can probably do this with domain functions (dlookup, dmax, dsum) as long as you are accounting for *ALL* usage within a receipt batch (actual usage, loss due to spillage, loss due to scrap, etc). You could do this by writing a function to look at the total amount of material needed for a batch, then compare how much material is available in your stock by looking at (For instance) the most recent 2 receipts of the material and subtracting the total usage of those batches from your recipe builder tables. I would add a prompt if the material is going to come from 2 different batches to make the user confirm the amounts before progressing. If the material comes from a single receipt batch then no prompt would be necessary.

    Do you have an example of your database with some junk data in it that can replicate this issue (i.e. I would only have to put in a specific recipe to figure out a function that'd work)?

  10. #10
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22
    Full trace System v4 - SPLIT_be.zip

    I've removed the sensitive data - thought there wasn't much to be fair. These files are my copies of the DB files, so I've just copied some random goods in entries over the past few months to populate the list for testing, and uploaded a handful or recipes from the DB for this.

    I update the DB itself and overwrite the files when I'm certain they are working. always handy to keep a separate DB setup and tables file

    Any assistance would be most welcomed as I'm fairly new to lookup functions you mentioned...

    Kind regards

    Crys



    NOTE: Forgot to add - When it loads choose "Go To Recipe Colllation" about halfway down the page on left side. This loads the form up used in collation.

    First type a number in the Red "Mix Number" at the top

    Next, choose a recipe and the ingredients will pop up in the subform and the Job_ID, Daily_FG_ID, BatchCode ect will be generated.

    Also

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Your data is a bit of a mess.
    When I look at the INV_STOCK table you have 6 lines for the receipt of INGREDIENT ID 240.
    INGREDIENT ID in the INV_STOCK table is stored as a text value.
    This does not correspond with the way you have set up your relationships (I don't typically use relationships and they are not super important, I am just pointing it out because it's not consistent in your database)
    If I try to trace the INGREDIENT ID from the INV_STOCK table I have to go to the ING_DATA table
    However you also have a table tblIngredient which has an independent numbering system for ING_ID which is stored as a number.
    I can find no table where I can cross reference the two (apparently) unique ingredient lists.

    why do you have 4 different ingredient tables...
    ING_DATA
    ING_LIST
    INGREDIENTS TABLE
    tblINGREDIENT

    your receipt table (INV_STOCK) is storing the TEXT Value primary key from the table ING_DATA
    your your setup table for recipes tblRECIPEINGREDIENT is also storing the TEXT value primary key from the table ING_DATA

    However your actual ingredient usage in table tblJOBINGREDIENTFG is storing the ING_ID NUMERIC from table tblINGREDIENT

    Unless you have a cross reference for your various ingredient tables I do not see how you can pull the receipt batch code.

  12. #12
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22
    The tables are indeed a bit of a mess, ive added alot whilst trying to progress this.

    ING_DATA is used to pull ingredients information that we order in - so it stores the weight per unit, supplier codes, our codes, name and ing ID ect
    This goods in data is then stored in the INV_STOCK table

    ING_List and INGREDIENTS TABLE are old tables - need deleting. There are probably some relationships built with them but they aren't used.


    As for the tblINGREDIENT, it is the newest of the tables and links to the ING_DATA table using the references, but stores an autonumber to different ING reference I use this to create recipes though as I'm typing this I think i should delete this too and just use ING_DATA table and the ID Field to lookup ingredient instead since the relationship with ING_DATA and ING_Stock is fine...

    So in short, ill try changing the tables used in recipe creation as ING_DATA.

  13. #13
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    22
    Quote Originally Posted by rpeare View Post
    Your data is a bit of a mess.
    When I look at the INV_STOCK table you have 6 lines for the receipt of INGREDIENT ID 240.
    This is because there have been 6 deliveries of Ingredient ID 240. What makes them unique is the "Number" Field. This is Internal Batch Code/Reference. Its an autonumber. If you open the table and view the data the first column "Number" is the autonumber (or internal batch code/reference).

    The recipe collation pulls the recipe data for the ingredients using the INGREDIENT ID field in the INV_STOCK, which is the same as the ID in the INV_DATA (relationship made)

    The STOCK_LOT numeric field that I want to be loaded up automatically for the tblJobIngredientFG is the "Number" or Internal batch code/ref from the ING_STOCK table...

    Can it be done?

    ADDITION: Heres an updated file:

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

Similar Threads

  1. Replies: 4
    Last Post: 12-16-2016, 09:57 AM
  2. Replies: 1
    Last Post: 02-14-2015, 02:29 AM
  3. Sql codes
    By hawke in forum Programming
    Replies: 1
    Last Post: 01-14-2015, 11:53 AM
  4. Linking Tables at the Table Level Vs. the Query Level
    By Tammy in forum Database Design
    Replies: 3
    Last Post: 12-03-2014, 01:34 PM
  5. Inventory Database: Automatic Adjustments?
    By MediaVue in forum Access
    Replies: 1
    Last Post: 02-04-2010, 03:08 PM

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