Getting result programmatically or via query...my biggest challenge

[ML!]

I am challenged by knowing the best practices with respect to getting a result I need programmatically or via a query result. Any advice on the following scenario is most appreciated. I'm simplifying for demonstration purposes...

The user enters production data by date, shift and product. In the Production table, the ShiftID is the PK relates to the date, shift & product. FK's include ID's from all the different process tables where specific process data on the individual products is captured.

tblProduction
ShiftID (PK)
Date
Shift (A or B)
ProductID (FK to product details)
Process1ID (FK to process details)
Process2ID (FK to process details)
...etc

In order to report total production on a product over the course of a shift, I have to check if there is data on that product from the previous shift and if so, subtract EOS (end of shift) plant inventory reported on the current shift from total production.

Total production by shift, date and product is a series of major calculations in and of itself and I've managed to sort that out in a calculated expressions in a query.

Does anyone have any suggestions on how I can check for data entry in the previous shift for a product? I only need to check the data in one process table but link to the calculation query for the total production result.

I hope this makes sense. If not, I will try to elaborate by mocking up a simple example of my overly complex db.

TIA!
ML

 

[jzwp22]

I'm not sure I quite understand your table structure. If a production run of a part (shift/date) requires many processes than that describes a one-to-many relationship. So, shouldn't the structure be this? (Perhaps you simplified your example too much):

tblProduction
ShiftID (PK)
Date
Shift (A or B)
ProductID (FK to product details)

tblProductionProcesses
-pkProdProcID primary key, autonumber
-fkShiftID foreign key to tblProduction
-longSequenceNo
-fkProcessID

If you have the number and sequence of processes required for a part somewhere else in your database, you could compare the number of processes completed (in tblProductionProcesses) versus total number required for the part. If the two are not equal, you know that parts spanned more than 1 shift and you can do your inventory allocation.

I would probably use a slightly different table structure since a part can be produced over multiple shifts (one-to-many relationship) and each shift can conduct multiple processes (another one-to-many relationship) to that part.

tblPartBatches
-pkPartBatchID primary key, autonumber
-ProductID (FK to product details)

tblPartBatchShift
-pkPartBatchShiftID primary key, autonumber
-dtePartBatch (date)
-Shift

tblPartBatchShiftProcesses
-pkPartBatchShiftProcID primary key, autonumber
-fkPartBatchShiftID foreign key to tblPartBatchShift
-longSequenceNo (sequence number for the process indicated)
-fkProcessID foreign key to a table that holds available processes

 

[ML!]

Thanks for your feedback jz. I am in a hurry to get this done so I tried to take shortcuts. You're right that the structure wasn't properly normalized. I was conveniently ignoring that fact because I built it based on data entry which had all the tblProduction fields on the main form with processing metrics in multiple subform. I came to the conclusion that I was creating more problems than solutions for myself last night and altered the structure...

tblShift
ShiftID (PK)
ShiftName
ShiftDate
ProductionID (FK)

tblProduction
ProductionID (PK)
ProductID (FK)
ProcessA (FK)
ProcessB (FK)
...a product may go through several processes and may or may not be worked on in a shift; each process has it's own table with disparate data to be captured for each product worked on in a specific shift


tblProduct
ProductID (PK)
...product detail fields

tblProcessA etc
ProcessAID (PK)
...process detail fields

I don't completely understand the manufacturing process but the parts aren't typical parts, they are volume calculations (which are enough of a challenge to figure out!). The outputs are wood products so the processes are not typical in that there isn't absolute sequencing - there is a quality variable (sometimes subjective) that alters sequencing and usage (or not) of the raw material.

I'm using a hugely complex excel workbook to inform the db development. I'm not sure I have time or scope flexibility to make the changes you're suggesting because it may also mean changing inventory processes at the plant...(?)...which is definitely not in scope of this project.

I'm not sure the volume calcs will easily translate to the parts concept. I will try to wrap my head around what you're suggesting here though and see if I can implement something similar but time might require me to stick to my current path

Thanks again for your feedback. I truly appreciate it. I tried to find a best practice manufacturing structure at the outset of this project - I should have just asked you!

ML!

 

[jzwp22]

tblProduction
ProductionID (PK)
ProductID (FK)
ProcessA (FK)
ProcessB (FK)

The ProcessA and ProcessB fields are good examples of repeating groups which is a sign that the table is not normalized. I'd be curious to see the structure of the process tables.

 

[ML!]

Here are a couple of examples with table names and some field names made generic. I hate to be so vague but I have an NDA in place with my company so have to be careful not to spill too many secrets

So one of the reasons I did the process and inventory steps in separate tables is they are segmented in the spreadsheet for data entry. Another is because the data is disparate and while ProcessA might happen to a product, ProcessB may not. It might skip all the way to ProcessE so wouldn't be properly normalized if all in one table. And still another reason is because each machine produces a report and/or the foreman collects data by machine or inventory process. We also want to be able to capture foreman comments by process for exception reports by machine/process. I saw the production table as a way to match all of these processes up by date/shift/product.

tblMachine1
Machine1ID
ProductionID
ShiftDuration
TotalDownTime
TotalIdleTime
Output1
Output2
Machine1Comments

tblMachine2
Machine2ID
ProductionID
M2FullOutput
M2HalfOutput
M2RandomOutput
M2RouterOutput
Machine2Comments

tblMachine3
Machine3ID
ProductionID
Avg1
Avg2
Avg3
Avg4
StdDev1
StdDev2
StdDev3
StdDev4
Machine3Comments

tblIncompleteInventory (partials complete but not packages so not in warehouse inventory but need to be counted)
IncompleteInvID
ProductionID
IFullOutput
IHalfOutput
IRandomOutput
IRounterOutput
IncompleteInvComments

tblEOSInventory
EOSInventoryID
ProductionID
EFullOutput
EHalfOutput
ERandomOutput
ERouterOutput
EOSInvComments

There's more but you get the drift.

Your feedback is eagerly anticipated and most welcome.

ML!

 

[jzwp22]

So one of the reasons I did the process and inventory steps in separate tables is they are segmented in the spreadsheet for data entry

You cannot design a relational database based on a spreadsheet design, it will not work. Relational database must be normalized to work properly; spreadsheets are generally not normalized.

I am in a hurry to get this done so I tried to take shortcuts.

The table structure and relationships you create at the start of a database project are the most critical aspects of a successful application, so it is important to get a better understanding of the design principles involved and to not hurry the design. If you take shortcuts, you will just be spinning your wheels later on.

From what I have read from your posts, this will not be a simple design. It will take several hours to flush out the basics and then several more hours to refine the table structure. Then there are always a few more hours to add things that you didn't see the first time through. Only after the table structure is completely set up, would I even consider starting a form for data entry.

To help out, I suggest you start by reading up on normalization. This site give a basic review. You might also try Roger Carlson's site for a practical tutorial on normalization. Crystal Long also has a good site


I have attached a database model that I worked out based on what you have posted. This model is just to define the processes, machines, and parameters for a part. Consider it more a structure to hold the definition or or the specifications of each part and what is required to produce it. It does not include the actual production related tables or the information related to producing an actual part, that will come later.

I don't know whether what I have posted is consistent with your application, it is just a guide upon which you can build.

 

[ML!]

Believe it or not, I am familiar with normalization and can challenge it when working with programmers in my real job.

I agree with everything you said and I did take shortcuts out of necessity so your chastising is well deserved. The resources you provided are great refreshers since I'm not a professional programmer and don't directly apply the concepts on a regular basis.

My real challenge on this project is the manufacturing process and that's why I agreed to take this on - it's good learning - and you're helping. I am grateful for your time and I hope I can take further advantage of your good nature with some additional questions/info.

I've reviewed the relationships and I'm going to work through them but need some clarification.

Can you please explain the purpose of the sequence number field? As mentioned above, the input is raw material, some machining and processes are applied (but not all the same for all products) to produce the final output.

Please also help me understand the parameters table.

I'm not sure the processes and the machines need to be separated. The machines output measurements (maybe that's what you mean by parameters) and that's all I really need to collect. I don't really care about how the raw material inputs get to the finished outputs. I've probably misled you in that I've used processes & machines kind of interchangeably. I was trying to capture the machining and the inventory information together into one 'category' if you will. There are inventory measurements for the products used in calculations. How do you think those will fit in?

Again, I'm grateful for your help.

 

[ML!]

...and as I get through this, I'll likely have more q's...

 

[ML!]

How will we capture shift details (ShiftName & ShiftDate) and how will they relate? Shift and daily productions reports will rely on them.

 

[jzwp22]

I'll try to answers your various questions from your additional posts

Can you please explain the purpose of the sequence number field? As mentioned above, the input is raw material, some machining and processes are applied (but not all the same for all products) to produce the final output.

I assume that if various processes have to be conducted in order to produce a part, the processes must be conducted in a certain order. The sequence number field would be used to capture the order. For example, a machining process might be used on various parts but may be step 1 for Part A and step 10 for Part B. You would reference the same process (fkProcessID) for the two parts, but use the sequence number to set the order.

I would consider machining as a process. The addition of raw material would be a process (addition).

I should have asked this question earlier. Are your parts a combination of other parts? Technically, you can consider a raw material as a part as well.
A part can be an entity in and of itself or it can be constructed of other parts. To model that in a database you need something like this

tblParts
-pkPartID primary key, autonumber
-txtPartNumber
-txtPartName

tblPartAssemblies
-pkPartAssemblyID primary key, autonumber
-fkPPartID foreign key to tblParts (the parent part)
-fkCPartID foreign key to tblParts (the child part)
-qty (quantity of fkCPartID need to make fkPPartID)

Please also help me understand the parameters table.

The parameters table was a way to define those items critical for a part (i.e. specifications) such as length, width etc.

For an actual production run, you would capture measurements from the various machines for the same parameters and then you can compare to the specificitions to determine if the part is in spec.

I'm not sure the processes and the machines need to be separated. The machines output measurements (maybe that's what you mean by parameters) and that's all I really need to collect. I don't really care about how the raw material inputs get to the finished outputs. I've probably misled you in that I've used processes & machines kind of interchangeably.

If the machine info is not important and can be incorporated into the processes that is OK.

How will we capture shift details (ShiftName & ShiftDate) and how will they relate? Shift and daily productions reports will rely on them.

The tables to capture actual production data will come after we define the tables necessary to define the parts and process and the related parameters. I didn't want to tackle too much at one time.

 

[ML!]

There aren't any parts - just wood that's machined to make a final product. Therefore there is no assembly and no inventory of parts, just final product. I'm not even tracking the volume going in, just out.

I may have led you down a wrong path to start.

To clarify, this is a performance tracking database, not a processing database. I don't need to track what happens just the volume at the end of the day to ensure they meet targets. Therefore it's really only the production data I need to capture.

The machine outputs data at the end of the shift, the foreman enters it and the data is put through a number of calculations to determine performance measurements.

I apologize if I steered you wrong.

 

[jzwp22]

OK, not a problem. We can still use some of what I proposed.

You still need to capture the processes and the parameters (the type of data outputted by the machine). Can you describe what type of data is involved? Is it the number of units processed or something else? You would just store the raw data. Calcuations like averages, standard deviations are generally not store but only calculated when you need them. Do parts still go through multiple processes and you want to capture how many are in the various stages of the processes?

 

[ML!]

Can you describe what type of data is involved?

Most of this is captured in the field types above but more specifically (note, I'm using the present table structure in descriptions so you can quickly connect it to information already provided)...

In the product table are the product details: species, thickness, # of items per measurement, product name and code.

The machine data is all captured by shift, at the end of each shift, they print the machine reports and capture the data in the worksheet. Shift performance is tracked as well as volume outputs over periods of time. No all data is presently used in calculations but they want to capture it all to improve their reporting processes.

Machine1 provides 3 time fields showing machine/plant performance and there are 2 numeric fields showing the number of units machined and wastage. The time fields are used in performance measurements but the numeric fields are captured for future use. They are not presently tracking this information - but they want to.

Machine2 reports on 4 different volume outputs (full/half/random/router) and 3 time fields showing machine performance

Machine3 calculates averages and the std dev on output. I know that you would not normally store these and you'd normally add calculations on reports from the raw data however, these metrics are already calculated by the machine and are used to track its performance. The data is captured for shift and time period reporting purposes.

Machine4 reports on the length, diameter and volume of its outputs. These are averages but they are provided by the machine so don't need to be calculated by the database. The values are used in performance calculations.

There are also 2 inventory tables that capture the same volume outputs as Machine2 - an interim EOS (partially complete) and plant inventory (packaged and ready to go. They need to be considered in production calculations.

Do parts still go through multiple processes and you want to capture how many are in the various stages of the processes?

They do go through multiple processes but the machine reports on the outcomes and I just want to capture that information by shift in order to use the data in performance calculations.

Please let me know if you need any clarification.

Thanks again,
ML!

 

[jzwp22]

With the information you have provided in mind, I restructured the example database I provided earlier. It is attached. If you look at the relationship window, the left side of the window shows a series of table that are used to capture the production data. The right side of the window shows a series of tables that define the parameters that are to be capture from each machine/process.


I'm not sure where the inventory tables you mention fall in...yet.

 

[ML!]

jz - as always I'm grateful. I have spent some time with it this afternoon and do have some questions but I have to go out for a few hours so will post when I return.

 

[ML!]

jz, forgive the tardy reply. I must not have submitted my message on Tue eve - I probably had a session expired error that I didn't notice.

I spent some time mapping collected data to the structure you've recommended and I'm not sure I understand it completely. Can you explain the purpose of tblProcessData. If I understand it, for each production 'transaction' there will be a data in one of the values (string, datetime or number value) and the other 2 will be null...is that correct?

 

[ML!]

BTW, for your info (and other viewers), I kind of cross posted on another board here: http://www.utteraccess.com/forum/find-record-specific-t1948896.html. I didn't mean to confuse things but the question I asked here was more philisophocal in nature which has become more practical and specific (with your kind assistance). But when it didn't really get much action here to start, I tried a more practical question on the other board. I wanted to make sure everyone had the info available on the other board too in case it helps other users with their dilemmas.

 

[jzwp22]

If I understand it, for each production 'transaction' there will be a data in one of the values (string, datetime or number value) and the other 2 will be null...is that correct?

That was my intent. I don't know if that is the best way. I thought that just 1 text field could be used and then format the actual value when you want to display it based on the resultdatatypefield designation for the particular parameter. That approach might get messy, so I opted for the 3 fields with the different datatypes.

 

[ML!]

Ok so, can you explain how the results table is used?

 

[ML!]

Sorry, I just opened it and figured it out. I didn't realize you had populated the fields.

Can you give me some advice on something? I am concerned that the structure may be complex for casual users. I bring this up because of my scope and time constraints. The company wants to have some ability to extrapolate data that may not be included in reports in the app I give them. I'm worried that they're going to be calling me for every query they want to run. I'd be grateful for your thoughts.