How to Manage this type of Data?

[cap.zadi]

hey thanks being with me here.



yes you are right we can do like but how we will discuss. i have around 13 different types of tests in side the process. some of are on the pipes itself like those which we discuss above but some are part of process like to moisture of kiln, dryer, and raw materials as well.

i will explain you the process in next hours so we will have a vision to move on.

thanks

[cap.zadi]

Well,

We are manufacture for clay pipes. i attached the process flow chart which is showing the Data collection points and physical QC check points. we have all those tests on those stages.

i am in keen interest to build my database to function in all those areas where we can have production data and also the tests which we perform.

if u look into process flow chart so 1st step is "Body Preparation Section" where we receive the Raw Materials and process to make a mixture (Clay) which supplied to "Press Section" Where 1st pipe is made and loaded on Cars called "Dryer Cars" and transfer to "Dryer Section" where pipes been dry for a almost 12 hours process passing through the '"Tunnel Dryer" then those pipes received in "Glazing and setting section" where they inspect the pipes and if reject so scrap it and if good so glaze it and load on other cars called" Kiln Cars" and send to Kilns for firing and then after 2days process pipes received on unloading where we have inspection as well and then other process called jointing where we make the jointing socket and spigot inside the pipes.


Role of QC

The QC role process map is attached "QC01" and so on which are the Test points.


my objective is to have database where i can tracking and monitoring on process and for example when i receive a finished products from a car "Kiln Car" So i should be able to analyze from which Material those pipes made, which glaze used and what were the moisture parameters and who made those pipes in which shift.

please advise now if any further question?


thanks

zee

[cap.zadi]

hey there, did u get my msg with attachments of process flow charts?

[jzwp11]

Yes, I did get them; of course, it generated more questions. Basically you are taking raw materials (the 2 clays) and process them into clay pipes and you conduct quality tests along the way. Do you do any quality tests on the raw materials (the clays)? From your process flow, it appears that you do quality tests on the mixture of the clays prior to the formation of the pipe. When you have the clay mixture, does it yield 1 pipe or many pipes? How do you track the material and pipes through the process? Do you have batch numbers or lot numbers of some sort?

[cap.zadi]

hey there, still waiting?

[jzwp11]

I was waiting for a response to my questions:

Do you do any quality tests on the raw materials (the clays)?

From your process flow, it appears that you do quality tests on the mixture of the clays prior to the formation of the pipe. When you have the clay mixture, does it yield 1 pipe or many pipes?

How do you track the material and pipes through the process? Do you have batch numbers or lot numbers of some sort?

[cap.zadi]

Sir,

Yes we do one Raw Materials Quality Test when recieved in store and then process to the Mixers where we record the qty of materials consume based on fix composition (called body composition) and after mixing having this test and then move to next step where having another more mixing and record total quantity by machine and the quality tests of Moisture.

then this material is supply to extruder section where they extrude the pipes from two lines currently and having a quality test as well which is moisture and penetration.

those pipes having product name and then loaded on dryer cars which having own number and then pass through the dryer and having other test when out of dryer which is called "Moisture test after dryer" then cars move to inspection area where people inspect and if it is good so process to glazed over the pipes and if bad so through out as scrap putting with reason of scrap.

then those glazed pipes loaded on other cars called as "KilnCars" so here depend on the product but in all cases many dryer cars fill out one Kilncar as they are bigger so here we havin another test of glaze which is glazed on the pipes from two tanks called tank-1 and Tank-2 then Kilncars move to Pre-Kiln Dryer where having another test called as "Moisture test at pre-Kiln Dryer" and then Kiln cars move to kilns (4 lines). in this case we took the a small part of a pipe to test moisture in all cases and in test record the car number and timing as well as u have seen formats.

then those cars come out from kiln after 24 process (fixed but changeable) and unload the cars called (sorting) and inspect the products if good so move to stock or for jointing and if rejected so through out but record the reasons for rejects.

This part is called finished goods part and here having many test in those have seen examples where we record the Kiln and Kiln car number which is important effective on results.

how it can be if here i just put the "KilnCarNo" and can get the results from which mixing material it is made and the tests through out the process.

so far we are not using any batch number which is difficult to fix over the body mixing area so please advise what tool can be used as Key to link up the data or either time factor will be good today's mixing will be supplied after 24 hours ?

the production data is collected in this format almost every where"

Like Extruder Area (where 1st pipe is made)
Main Table
Machine 2 machines
Product (which is fix for a machine for some period like 2/3 months)
PrdDate
Operator (Who opertaor the machine bcz each shift/machine)
Shift (Two shifts normally day and night and we get data by each shift)

DryerCarNo (when extrude the pipe so Robot load on cars - >2 Pc's)
StartTime (record the time for each car when start to load)
LoadedQty (normally fixed qty as 8 pc's some times bad pc's so reject)
RejQty (Reason for reject)

When we recieved those products after dryer at inspection area so they unload the product and glazed if good and load on other KilnCar and data is recorded like that:

Machines (2 lines and same product is fix for months)
Glazingdate
PrdDate (there is date on pipes when Extruded)
Operator
Shift

startTime
DryerCarNo
TotalPipes
Good
Reject (with reasons so there can be many reason for diff. qty)
KilncarNo (the good pipes only loaded on KilnCars)



then all those Kiln cars move to Kilns for firing and then recieved after 2-3 days other end of Kilns. here we record which carno what time was in and out from kiln.

unloading the cars

LineNo (2 lines)
sortingdate
Shift
Operator


StartTime
KilnCarNo
GoodQty
RejectQty (reasons for reject)
ScrapQty (Scraped but some small Fittings made later by cutting)


then those good pipes are stock outside and some of process in same line for jointing where we joint the spigot and socket for jointing.

JointingLine
JointingDate
Shift
Operator
Product one product for couple of weeks)

PalletNo (identify the PalletNo with a key)
NoofPipes (qty per pallet is fix for each product)

* can we have here facility to print lables to paste on every day pallets)

After those pipes here are again quality test and QC people mark it as passed or not based on their tests like to see leakage test and joint leakage.


i wish if i could build this database and can have touch screen system on those product control points to record data live not on papers.


please advise now where we stand to move forward.

thanks for ur so much time.

zee

[jzwp11]

I have been trying to come up with a general approach to your issue.

You basically have a product (clay) that is processed through various operations into other products (pipes) that are further processed and you test those products at various points in the processing. I think keeping it more general will help in laying out a basic structure; we will leave the specifics for the actual records. So with that said, you need a table to hold the products; in that table I would have a field that associates a product type (raw material, pipe etc). We can also have a table that holds all possible product types

tblProducts
-pkProductID primary key, autonumber
-txtProductName
-fkProductTypeID foreign key to tblProductTypes

tblProductTypes
-pkProductTypeID primary key, autonumber
-txtProductType

The products go through various processes, so lets have a table that holds all possible processes

tblProcesses
-pkProcessID primary key, autonumber
-txtProcessName

Also, you have various pieces of equipment involved in the process including mixers , extruders, kiln cars, drier cars etc. So they should be stored in a table. Again you can have a field that describes the general category of each piece of equipment is you want

tblEquipment
-pkEquipmentID primary key, autonumber
-txtEquipmentName

You mention that you have to record the values of certain process related parameters (weights, times etc.), so a table to hold a list of those parameters

tblParameters
-pkParameterID primary key, autonumber
-txtParameter

You will also have tests to conduct on samples taken during the processing, so tables to hold that info.

tblTests
-pkTestID primary key, autonumber
-txtTestName

tblSamples
-pkSampleID primary key, autonumber
-txtSampleID
-dteSample (sample date)


You also have people involved (operators, test technicians etc.), so a table to hold all people

tblPeople
-pkPeopleID primary key, autonumber
-txtFname
-txtLname

Now we have to bring the processes, products, equipment, parameters, samples and tests together. In a typical manufacturing process, you take a product or products run it/them through a process that typically involves some equipment and you end up with another, modified product. That modified product can go through another process/equipment to create yet another product. To do that in a relational database we have to associate the products used to make the modified product as follows:


tblRelatedProducts
-pkRelatedProductsID primary key, autonumber
-fkPProductID foreign key to tblProducts (the modified product)
-fkSProductID foreign key to tblProducts (the product(s) + process +equipment) to make fkPProduct

In order to produce the modfied product, the original products must be processed:

tblProductProcess
-pkProdProcessID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkProcessID foreign key to tblProcesses
-dteProdProc (a date/time field for when the product was started?)

For each product/process combination, you might have many pieces of equipment involved

tblProductProcessEquipment
-pkProdProcEquipID primary key, autonumber
-fkProdProcessID foreign key to tblProdProcess
-fkEquipmentID foreign key to tblEquipment



Now for each product/process/equipment combination, you have to record various parameters as you mentioned

tblProductProcessEquipmentParameters
-pkBatProdProcEquipParaID primary key, autonumber
-fkProdProcEquipID foreign key to tblProductProcessEquipment
-fkParameterID foreign key to tblParameters
-RecordedParameterValue (you will have to determine the correct datatype for this field)

Also, for each product/process/equipment combination you will pull samples for testing

tblProductProcessEquipmentSamples
-pkProdProcEquipSampleID primary key, autonumber
-fkProdProcEquipID foreign key to tblProductProcessEquipment
-txtSampleID
-fkLocationID foreign key to tblLocations as discussed earlier; indicates from where the sample was taken
-dteSample (date/time field to capture when the sample was collected)

You will have to conduct many tests on a sample

tblSampleTests
-pkSampleTestID primary key, autonumber
-fkProdProcEquipSampleID foreign key to tblProductProcessEquipmentSamples
-fkTestID foreign key to tblTests
-TestResultValue
-fkPeopleID foreign key to tblPeople (identifies the person doing the test)
-dteTest (date/time field for when the test was conducted)

If you need to track people involved in the processing (not including people doing the testing), then you would need this table

tblProductProcessEquipmentPeople
-pkProdProcEquipPeopleID primary key, autonumber
-fkProdProcEquipID foreign key to tblProductProcessEquipment
-fkPeopleID foreign key to tblPeople

The above is just a framework, you will need to add additional fields and maybe tweak the design a little, but I think the basic core you will need is captured.

[cap.zadi]

Well, it is great to hear from u and really it is basic core.

how to handle as each process stage having its own subform format for the data recording and same for tests where we have different data format for each test. so how to control that?

will be able to do the following things with ur design?

- If I want to know the history of a Kilncar which is unloaded at last stage to know about from where it was produced and what was QC tests was?

- can we keep in mind for future to have touch screens over those areas to capture data?


thanks for ur time and really it is my pleasure to have conversation with you.

thanks.

zee

[jzwp11]

how to handle as each process stage having its own subform format for the data recording and same for tests where we have different data format for each test. so how to control that?

I've never seen your data format, can you provide examples?

If I want to know the history of a Kilncar which is unloaded at last stage to know about from where it was produced and what was QC tests was?

You do not test the car, you test sample of product taken from the car, the car is just a piece of equipment. The structure I have presented should allow you to track the products through the process & equipment used. You will need to develop queries to extract that information.

- can we keep in mind for future to have touch screens over those areas to capture data?

That is a hardware issue; I do not know anything about that.

[cap.zadi]

Hey, i am developing ER modeling and i think it will be good tool to carry on our discussion. so hope will be able to share with you soon.

well, i just want to know Access can support Touch Screens to capture the data?


thanks.

zee

[cap.zadi]

Hey Sir,

How are you.

attached, please find the Process Flow chart marked up with Data and QC test Points. there is also a detailed description about the process and data details on each step of process which can give us idea now how to set up the database structure.

the QC's test details are not ready yet and hope to revert you soon. as we discussed before and u can see the marked data points where those test need to perform in process and how can be linked to the process information.

* there is 17 Type of Test wth different data structure.

hope to hear from you to start up for data base structure.


thanks.

zee

[jzwp11]

Looking at your process description attachment, you would not have tables for each collection point. You would use the table structure I provided. The data you need to collect relative to the process would be captured via the parameters table I noted (i.e. amperes). When you actually samples that would be handled through the sample/test junction table. In terms of quantities of materials, that would be handled through the tblProductProcess and tblRelatedProducts tables.

...hope to hear from you to start up for data base structure.

I have already laid out the table structure for you, so I'm not sure what else I can do. You should be able to use what I have provided and fill in the details.

[cap.zadi]

Well, i was just waiting if there is any new direction for the modification in previous design.
i will start to work on that structure and will revert you if there is any more details required.

thanks for your time. bye

by the way Happy XMax.

zee

[jzwp11]

You're welcome and have a good Christmas as well.