Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825

    So you need a structure that sets up the inspection criteria for each part. Then need structure that reports the inspection results for each serial number. Again, I have parallels in my db. I have a 'Specifications' table. Sample (soil, aggregate, asphalt, concrete, etc) is received. Sample is logged in and coded for material type, project, specifications set, etc. Technician performs tests and enters data and report printed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    Join Date
    Apr 2017
    Posts
    1,673
    So the database structure I adviced is almost what you need! You can have any number of different measurements for part.

    So you have a products table with ProductID as primary key, and probably a product serial numbers table, where all serial numbers for all products are registered.

    And you need a tests table, where are registered individual test series with test date, part number, and tested part serial number, and test measurements table like I described earlier.

    Are all individual parts measured, or only selected ones (like every 100's)?

    When every part is measured, then you have to link tests with serial number. For every serial number you register a test. For every test, a certain set of measurements determined by part is registered in measurements table.

    When selected parts are measured, then you have to link tests with part number. A part can have many tests. You register a new test for a part, and in test table you register the serial number of part tested. And again, a certain set of measurements determined by part is registered in measurements table and linked with this test.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't fall into a trap where you're trying to show too much in one place. Agree with the possibility of 2 (or more) subforms on one main form, but probably not more. One problem you have is that you're not familiar with what I likened this to, as in spite of your update, I still see quite the similarity. It matters not that yours isn't based on a work order, though it's possible that there is such a parent entity. What else ties all the machining operations together? It seems to be the part serial number. For me, the description of the business at hand requires a bit of thought; unfortunately I will have to delay doing that for at least a few hours from now but some clarification on this may help
    There is no case where one Part Number has the same operations as another Part Number.
    You're saying that this part is denoted by the part number, and an identical part will never be made more than once since the same operations will never be repeated. So the part is always a one-off. Is that a correct interpretation of your statement.

    As for uploading a db, I think more clarity is needed around the business first (at least for me).

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    I think OP means, that there is a certain class of produced items defined by part number. There is a set of operations needed to produce this part, and this set and all operations in it are defined for this part only. For every individual part produced (identified by serial number), the same set of operations is repeated.

    It looks like OP is designing a DB for small production. Registering all measurements for whole product isn't possible for industrial production, where production process may run in several workstations, departments, or even in different factories. In our factories all measurements are linked to production orders - and those are defined only for one operation or set of linked operations on single workstation - i.e. for producing a product there is usually ten's or hundred's of production orders, and almost all of them have metrology included. Any problems must be discovered as early as possible - otherwise may be cheaper to scrap all party where a faulty part was discovered.

  5. #20
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    ArvilLaanemets got it exactly right. I will try to illustrate further with a specific example.

    Let's say we have part number T223095. This is a particular kind of turbine blade made for Company X. We will be running 200 of T223095, so there are 200 serial numbers for that part. The same set of operations is run on each of these parts (serial numbers). Each operation has its own set of measurements that need to be recorded (these are always of data type short number or a binary pass/fail, but the exact number of fields and descriptors differ). However, these operations are unique to this part number. Operation 20 for part T223095 is not the same as operation 20 for part 83337043. They are both called Operation 20 because the Ops are ordered 10,20,30,40,etc and these numbers loosely correspond with the class of operation it is (Grinding, EDM, Milling, Shipping, etc).

    This is a mid-sized contract manufacturing plant focused on machining parts to tight tolerances. We measure key dimensions after every operation after every part, and anything that is out of tolerance is labelled non-conforming and is either re-processed or scrapped.

    Arvi: Are you suggesting the correct path would be to create various tables for different sets of data? So maybe there is a table for each part number, where the primary key is the serial number. Then each of these serial numbers is linked to a set of tables, one table per operation? I could potentially see this working, but only if its possible to auto-populate other tables with the serial numbers as they are entered in OP 20. Would this be possible? I haven't been able to figure this out so far.

  6. #21
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by BDibz View Post
    Arvi: Are you suggesting the correct path would be to create various tables for different sets of data?
    On contrary, I'm advicing to have a single measurements table. Having a table per operation means, you have to redesign your database whenever you get a new part to produce. And probably when you get a new version of some part you produced until now too.

    So you have more like industrial production. When this is the case, then you better link measurements with operations. You can have part number and serial number on measurements too (for reporting p.e.), but this will be a secondary info. And as you have several different values to measure per operation, then you need to define at first this set or test, and all measures will belong to this test.

    An example.
    You have an operation on some part for some product. At metrological viewpoint all starts from operation, which is defined with ProdOrderOp (an operation number assigned to certain operation in production process of some product - how you exactly build this production chain is up to you).
    After you ended the operation, you need to measure the part. So you need to register the whole measurements complex for this certain part. Let's name this a test. So you will have a table tblTests: TestID, ProdOrderOp, [ProdSerialNo], [OpDate], TestStatus,....
    Here will be the uncertainity. You did write, that you need to register all measurements with product serial number. Does it mean, that the client gets a metrology report with all measurements for all parts of product - what assumes all parts and materials used to produce certain product unit with certain serial number are determined at start of process. Or are ~200 parts produced at once, and assembled to product later (in which case usually operation measurements are processed immediately, and a decision is made immediately for every single part, does it pass the test or not)? It is not impossible to get all measuremants together in 2nd case too, but it assumes that all parts are marked somehow - p.e. with sticker where TestID is printed.

    After the test is registered, the measurements mus be done. So the next table is needed. tblMeasurements: TestMeasurementID, TestID, MeasurementType, ValueNumeric, ValueBinary, MeasurementDescription.
    For every single measurement, you add a row. Depending on measurement type, binary or numeric result is recorded.

    When you want to get the predefined set of measurements into tblMeasurements, you design a similar but empty table linked to ProductNo (and ProductVersion), which you use as template. Then you run an INSERT query based on template table joined with tblTests to add new rows into measurement table for next test.
    EDIT! Not an empty table of course, but a set of entries in test templates table for this product - with empty TestID and empty value fields.

    I hope this is enough for you to get a start. For me it is midnight in a hour, so it is enough for today!
    Last edited by ArviLaanemets; 02-15-2018 at 01:05 AM.

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm going to let AvrilLaanements carry on since this seems to be his/her area of expertise. Still think this is like the work order system where I used to work.

    WO 1234
    TASK 1 do something
    TASK 2 do something else
    TASK 3 do something different yet again
    TASK 99 maximum number of tasks allowed

    WO 3456
    TASK 1 do something totally different than task 1 of any other work order
    TASK 2 do something totally different than task 2 of any other work order
    TASK 3 do something totally different than task 3 of any other work order

    Standards, SOP's, approvals, stations, departments, sub-departments (like outside processing), status, measurement outcomes (via electronic checksheets), completion comments, status, start/finish dates, cost, etc, etc, all tied to the work order task. Seems similar to me in that your OP 20 (my task number) is not the same from part to part (or work order to work order).

    Regardless, I think you are in good hands. Good luck with your project!

  8. #23
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Avri:

    I'm gonna have to sit down and unpack all of this for a while; I'm sure it is relevant and carries a lot of good information, but being so new to this, its hard for me to develop a mindset wherein I can easily picture these tables and relationships. For now, I'll answer one of your questions briefly to clarify the situation:

    The metrology report (what we call an "inspection sheet") is at first an internal ledger of completed parts, used to keep track of parts as they move through operations to ensure that they are of good quality - if there is an operation that produces a non-conforming part - out of tolerance somehow - then it goes through additional processing to fix this. And yes, these inspection sheets are attached to the work order and I believe sent back to the client. They do report 100% measurements - every serial number, every operation performed, every important dimension for that operation.

    There is no assembly occurring at our plant; we are only subtracting material from cast metal parts through various machining methods to return the part with precise features. This is why metrology is so important; these are mostly things like turbine blades, so tolerances are within a few thousands of an inch, and we need to make sure that every important feature is within that tolerance.

    So yes, essentially the measurements are processed immediately in the sense that once they are measured, if something is out of tolerance it is immediately tagged as such and then goes through whatever additional processing may be necessary to fix it (or scrap it if it can't be fixed).

    I hope this helps further clarify the situation. Thank again for your help.

  9. #24
    Join Date
    Apr 2017
    Posts
    1,673
    You haven't said, do you have some database to plan and follow your production process. Is it existing and 'given', or you are developing this too? On this depends, how much you can use of following, or how much you have to adapt it.

    Planning of production starts with composing something named BOM list. This is a hierechical list of parts needed to produce a product. The BOM list consist of levels, and rows on those levels. P.e. you have to compose a BOM list for a product with part number 'TurbineBlade', which is produced from cast part with part number 'TurbineBlade_0' - and you need some number of operations for it.
    The 0-level of your BOM list is for finished product itself.
    BOMLevel = 0; BomRow = 0; ProductPartNo = 'TurbineBlade'
    The 1. level is for packing the product for transport:
    BomLevel = 1; BomRow = 1.1 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'TurbineBlade' (This is the turbine blade ready for packing);
    BomLevel = 1; BomRow = 1.2 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'PackingMaterial' (Here and further on this level can be many rows for different packing materials);
    BomLevel = 1; BomRow = 1.3 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'PackingLabel';
    On the 2. level the serial number is engraved into your product (it is better leave this as last production step - so you don't waste serial numbers for scrapped production):
    BomLevel = 2; BomRow = 2.1 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'TurbineBlade_3' (This is produced turbine blade without serial number)
    On next levels, the real production is made.
    BomLevel = 3; BomRow = 3.1 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'TurbineBlade_2'
    BomLevel = 3; BomRow = 3.2 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'Spending Materials for level 3'
    BomLevel = 4; BomRow = 4.1 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'TurbineBlade_1'
    BomLevel = 4; BomRow = 4.2 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'Spending Materials for level 4'
    BomLevel = 5; BomRow = 5.1 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'TurbineBlade_0'
    BomLevel = 5; BomRow = 5.2 ProductPartNo = 'TurbineBlade', MaterialPartNo = 'Spending Materials for level 5'

    NB! You see, that whenever you make something with some part (engraving of serial number may be an exception, as in my example, but it is up to you), you create a new part!

    Now you take this BOM list, and based on this you create operation list:
    ProductPartNo = 'TurbineBlade', BomRow = 1.1, OpRow = 1.1.1, Workstation = 'Store', OpNo = 99 (Packing)
    ProductPartNo = 'TurbineBlade', BomRow = 2.1, OpRow = 2.1.1, Workstation = 'Ws', OpNo = 91 (Serial Number engraving)
    ProductPartNo = 'TurbineBlade', BomRow = 3.1, OpRow = 3.1.1, Workstation = 'Ws3', OpNo = 60
    ProductPartNo = 'TurbineBlade', BomRow = 3.1, OpRow = 3.1.2, Workstation = 'metrology', OpNo = 68 (testing)
    ProductPartNo = 'TurbineBlade', BomRow = 4.1, OpRow = 4.1.1, Workstation = 'Ws2', OpNo = 40
    ProductPartNo = 'TurbineBlade', BomRow = 4.1, OpRow = 4.1.2, Workstation = 'metrology', OpNo = 48 (testing)
    ProductPartNo = 'TurbineBlade', BomRow = 5.1, OpRow = 5.1.1, Workstation = 'Ws1', OpNo = 20
    ProductPartNo = 'TurbineBlade', BomRow = 5.1, OpRow = 5.1.2, Workstation = 'metrology', OpNo = 28 (testing)
    ProductPartNo = 'TurbineBlade', BomRow = 5.1, OpRow = 5.1.1, Workstation = 'Ws1', OpNo = 20

    There may be rows for making sets of materials for production in storage too.

    Based on operation list, production orders are created. For every different workstation, a separate production order is created. And in your case, it is best to have a separate production order for every processed piece in production chain. Then you can to give some unique ProdChainID to this production chain at start, which is added to every production order - you can use it later to link all production orders with serial number given to product at last step.
    When you have several operations on same workstation, you can group them into same production order, but there must be a separate production order for every testing row in operations list

    Now for every testing operation in operations list you create a new test in tblTests, and link it with ProdOrderID, etc.

  10. #25
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Ok, I've spent some more time getting into the mindset of normalized tables and relations. I have a soft plan for where to go from here, but it involves ending up with hundreds of tables. Is this normal for databases that involve specific formats for different items?

    Here's why: it seems that the best option for normalizing that still respects the differences between the different part's inspection needs is to have one table of inspection criteria per operation per part number. So I'd have a table named partNumber_operationNumber (where those tags represent the actual specific numbers) and that would contain all the fields necessary for that part number/operation number, and be linked to the serial number table by its ID. Does that seem like a viable option?

    Additionally; is it better practice to allow the input of the primary key during data input (i.e., operator enters a new Serial Number and this becomes the primary key) or to have the table auto-generate an AutoNumber, and simply have the serial number in the second column, but related to that AutoNumber?
    Last edited by BDibz; 02-22-2018 at 01:43 PM. Reason: Clarification

  11. #26
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Probably not normal.

    However, building a functioning db is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." The database I describe in posts 11 and 13 breaks a lot of normalization rules but it does what we want.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-25-2016, 02:56 PM
  2. Replies: 4
    Last Post: 04-01-2014, 02:33 PM
  3. Replies: 16
    Last Post: 02-06-2013, 03:06 PM
  4. Replies: 5
    Last Post: 08-24-2012, 10:32 AM
  5. Replies: 2
    Last Post: 03-25-2011, 12:22 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