Results 1 to 14 of 14
  1. #1
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42

    Talking Job Tracking Through Factory - Tips For Design

    Hi Everyone, the operations manager was so impressed with the CRM / Job tracking database I set up for my department ( thank you to this forum and Bruce Myrons Udemy courses), he has contracted me to help build a job tracking database for the jobs going through the factory. We're a large scale custom cabinet manufacturer. I have some ideas of how this should work, but I was hoping to get some advice from the experts on here. I'll provide some background on the flow of work for the order.



    Once the order is approved it goes through accounting then onto scheduling, which is where the database will start tracking the order. Each order consists of 1 or more "sections". Think for example you would have a different cabinet finish in your kitchen, bathroom, laundry room etc. The order actually starts production 13 working days before target delivery, even though the orders are scheduled out 30 to 50 working days.

    Scheduling will enter the order onto the tracker, and schedule into the target week for delivery. This will vary based on the product (paint and stain take longer than melamine, custom work takes longer, etc). Scheduling will put it onto a target day for the target week, based on how many jobs are going that week. I have an idea on how to set up the forms with subforms to be able to see dates.

    After scheduling, the order goes through admin (engineering and purchasing), we will need to track the date of completion at each step through this process.

    After this it goes to the shop for production. At this point they are not looking at tracking through here, but I would like to set up the DB in a way that I can expand into that functionality. I would also like to be able to expand in the other direction to be able to build a client database for use for the sales people.

    My thoughts are setting up a table for the order, a table for the sections of the order, and a table for the completion dates as it goes through admin. The second 2 tables linking back to the first.

    I know this is a ton of information, but I was hoping to get some tips from more experienced database designers on anything I may have missed or not known while setting this up. I'm happy to chime in if I might have missed any important information in this post. I really want to be able to set this one up right from the start, unlike the last one where I had to work around some noob mistakes.

    Thank you.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I know this is a ton of information
    Not really.

    What about tables for

    tblWO
    - WoNo, CreateDate, CreateBy, Status, StatusDate, ReqdByDate, CompleteDate, Approver, etc etc

    tblWoTask (n possible tasks per work order)
    - TaskNo, WoNo_fk, rest similar to above

    tblWoCommnts
    - comment, EnteredBy, EntryDate

    tblWoTaskComments
    - comment, EnteredBy, EntryDate

    tblWoTaskParts
    - parts list per task (links to inventory/stores/parts tables which provides costs)

    tblWoTaskLabor
    - hrs/labor data based on employee data (pay rates)

    Task parts and labor costs roll up to the work order.
    That is a bare bones overview of a work order system I dealt with for many years.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    I really like the addition of the tables for comments. As much as I would like to have the inventory system built into this it's out of the scope of work for this project. Here is what I"m thinking for tables

    tblWO
    WONumber, createdDate, enteredDate, reqWeekOf, completeDate, bucketNumber

    tblPapers
    desPaper, Material, boxCount

    tblAdmin
    eng1DateComplete, eng2DateComplet, eng3DateComplete, eng4DateComplete, pur1DateCompelte, pur2DateComplete, pur3DateComplete

    tblNotes
    note, enteredBy, enteredDate

    All the tables would link back to a a specific WO number. I will also need to figure out how to set up the forms so the scheduler can look at a week and add completion dates for jobs. Eventually I think we could automate a big part of that, but it's baby steps. If I go too far too fast everyone starts screaming because they are afraid of change.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    tblAdmin - this one might be OK but looks like un-normalized structure on the surface. Hard to say when it comes to dates.
    All the tables would link back to a a specific WO number.
    Not without WO fk, but you probably just left those out of your last post.
    I will also need to figure out how to set up the forms so the scheduler can look at a week
    IMHO, always queries 1st unless form is based on 1 table. Even then, I often prefer a query. If you cannot edit your query, forget about updating/appending on a form that it is bound to.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I would question this table :-

    "tblAdmin
    eng1DateComplete, eng2DateComplet, eng3DateComplete, eng4DateComplete, pur1DateCompelte, pur2DateComplete, pur3DateComplete"

    eng1,eng2,eng3,eng4 looks like repeating group
    pur1,pur2,pur3 also repeating group ??



  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Micron's advice and comments, review some of these articles on Database Planning and Design.
    Do NOT start with a physical Access database. If you do not yet have a good overview description of the business or a high-level picture of the functional areas and what they do, then start by identifying the areas (your Ordering/Purchasing. Accounting....). Since you mentioned Inventory, which is not in scope for your current activity, it would be helpful now, and in future, to know (even in concept/abstract terms) where and how these "pieces" fit into the big picture. This can be quite enlightening especially if/when some areas of your organization don't "know what other areas do" or "what happens next in terms of process".

    As Micron mentioned, your tblAdmin design raised a red flag immediately --what is the eng1, eng2...? My first reaction is --you must use Normalized design, and every table should have a Primary key. But even before potential table design, you need a plan even if in general terms. Review the "knowledge nuggets" from BA-Experts in the link above to get an appreciation of what analysis and design involves.

    Make sure you have your project requirements in a clear, understandable format and vet these with the project sponsor and others that may be involved. The data modeling articles in the link will be helpful to you and any maintenance /adjustments in future. A broader conceptual model of the business to identify major topics and their inter-activity and relationships will be helpful to you and management. Bottom line here is to make sure your project "fits" in the bigger picture. If there are things outside your project scope or whose details are unknown at this time, create a black box in your description or model identifying this "thing" and some of its attributes (eg your desire for Inventory) so that it is not forgotten or ignored in future. Perhaps the next project will deal with Inventory (or "thing") and you will already know where it fits, which areas would be involved etc.

    In the link provided are some tutorials from RogersAccessLibrary that will help you experience the process of designing tables and relationships. Work through 2 or 3 of these - you will learn.(each has problem statement and a solution)

    Do NOT be too quick to jump into physical database. Do the analysis and modelling - see the stump the model article for more info. Get an approved blueprint for your database structure, then do the physical development.

    Good luck with your project.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just to clarify, the reason I said "maybe" is because the fields are dates yet appear to be quite similar with what they seem to represent. Consider from my WO db experience
    - CreateDate
    - SubmitDate
    - ApproveDate
    - FiniDate
    - CompleteDate
    - CloseDate
    - StatusDate
    These are all attributes of a single entity - the work order; thus like the db designer, I would not break them into their own table. If the posted example dates are operational phases, then those dates appear to be attributes of the entity "operations" where each operation represents a distinct activity - possibly each activity having its own series of dates. If that is the case, they are not entities of the work order. Then they are prime candidates for a separate table. Another governing factor would be whether or not each date is always applicable. In my example they are. If the OP's example is such that not every item will go through each phase thus not have a date value, that is another indicator where the dates represent some other entity thus should be on their own.

    This is why the best marriage is db design experience and a complete understanding of the process it is meant to support. Unfortunately that is seldom the case.

  8. #8
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    So for tblAdmin, there are currently 5 sections of engineering that the order travels through and 3 stops in purchasing. The idea is to be able to tell how long it took for the order to make it through those departments.

    MY thought for the form for scheduling would be to have have 5 continuous sub forms (Mon - Fri) based on queries by by dates in text controls.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    then IMHO it ought to be its own table with arrival and departure dates, each stop being a record. Anything that may come up that doesn't go through all areas means no holes in the table. Any future area is just another record - not a field that doesn't yet exist. There would have to be a compelling reason before I'd have 5 subforms, one for each day of a week rather than 1 with 5 records.

  10. #10
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    That actually makes a lot of sense to set up the admin table with department and date complete, instead of a date for each one.

    For the scheduling, we will have multiple jobs going through on the same day. The scheduler needs to see how many jobs are scheduled for a certain day, and the size of the job. Then they have to manually assign a job that's scheduled for that week onto an available day. I"m sure there is a better way of setting up the from for them to be able to see a Monday to Friday with which jobs are scheduled each day and the total box count for each day. They will also need to see which jobs are scheduled for that week, that haven't been assigned a day yet.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you try to model your requirement and test that model with sample data using pencil and paper?

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by MaxQTime View Post
    The scheduler needs to see how many jobs are scheduled for a certain day, and the size of the job. Then they have to manually assign a job that's scheduled for that week onto an available day. I"m sure there is a better way of setting up the from for them to be able to see a Monday to Friday with which jobs are scheduled each day and the total box count for each day. They will also need to see which jobs are scheduled for that week, that haven't been assigned a day yet.
    It just seeks like something you can do with a continuous form driven by status choice (scheduled OR not OR both) and date.

    I come from a maintenance world where a planned work order had tasks, each of which could be assigned resources and separately scheduled. Best way to track costs IMHO. A WO task could be as simple as "order parts" (because even that could take someone hours) or order mobile crane (the PO or related dept WO could be a part of that info) which also provides for situations where one task must be completed before another one can begin. In fact, the system was smart enough to not allow task 3 to be scheduled if task 2 wasn't complete provided the necessary flag was set IIRC.

  13. #13
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Can you upload a zipped copy of the Database so we can see the Relationships?

  14. #14
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    Quote Originally Posted by mike60smart View Post
    Hi

    Can you upload a zipped copy of the Database so we can see the Relationships?
    haven't actually started building the database yet. I'm still in the planning stage, I should be starting in the next couple of days.
    I

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

Similar Threads

  1. Database Design Help: Lien Waiver Tracking
    By twarner3 in forum Database Design
    Replies: 8
    Last Post: 12-03-2023, 04:23 PM
  2. Database design for grading / profile tracking
    By zoiets in forum Database Design
    Replies: 4
    Last Post: 11-29-2018, 02:39 AM
  3. I need to make 4 inventories to one factory
    By Mehvan in forum Database Design
    Replies: 10
    Last Post: 10-06-2016, 11:33 AM
  4. Help With Patient Tracking Database Design
    By wkenddad in forum Programming
    Replies: 2
    Last Post: 04-25-2012, 09:15 PM
  5. Cafeteria tracking system design
    By tsr_83 in forum Programming
    Replies: 2
    Last Post: 07-28-2010, 09:45 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