Traceability in Production

[mymou]

Hi All

My case is quite simple. Raw material is turned into a finished product. This could then be stored (or not) and at a later date (or the same day) be processed into a further finished products. The db will have to describe the entire production path and packaging and finally show stocks and WIP.

This will have to be integrated with barcode and scanning to ensure minimal effect on production. I will leave this problem for when I understand how the db should be working.

I think that a tblProductDetails should be able to cover my needs if a field exists to relate to previous product and a Boolean to indicate the current state. Then by using a same table query (one table for each stage of production) – I should be able to map out the production and by using the Boolean field it will be able to identify current situation.

The alternative would be a table for each production stage (but more tables never sounds like a good idea to me).

I would really like to see a sample of an mdb that tracks production just to see if I’m on the right track. Any feedback would be appreciated.


Thanks in advance

Stew

 

[KenReay]

Hi

Do you have to track an individual item or a class or item?

By this I mean, if you were making (say) cups, there might be different types of cup (eg tea, coffee) and different colours, but when you come to sell a cup, no one is going to expect you to be able to trace an individual cup out of all of the essentially identical cups.

However, if you are making products for certain applications (eg aircraft, oil/gas installations/ Nuclear installations), theer is often a need to be able to identify and track individual instances of a product

The situation you have between these two will have a big impact on the design of your database.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mymou]

Hi Ken

Thanks for the reply - it sounds as though you have done this kind of thing before.

To answer your question, I think the answer is: Quantities will be processed in batches that need to be tracked.

To give an example:

The raw material A (of quantity x) will be processed into 3 final products A1 and/or A2 and/or A3.

A3 may be taken and further processed into A31 and/or A32 and/or A33. etc

This is for traceability in the food industry and the industry is going for a farm to fork solution. In essence, it means that we have to be able to link our final product to the source of our raw material. So for A33 - we have to be able to trace all information for A and A3.

Does that answer your question? Any insights?

Stew

 

[KenReay]

Hi

Only that it sounds like it is going to be more complex than you two or three table example

I would think you are going to need a (B)ill (o)f (M)aterials type structure to hold recipes or prodct structures of your products, Product Table, etc etc, it is too much (for me) to discuss here.

and yes I have done this kind of thing before

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

The generic problem is often stated in terms of logistics control. I the formal sense, logistics is the process of coordinating delivery of goods in a timely and efficient manner, with -perhaps- Military supply chains being one of the more prominient examples. I have done some small ammount of work in the area (non-military). I found that the multi-table soloution was much easier to deal with, but I had little variation in the possible stages of supply for any specific end product. Whenever there was a new end or a new primary source for a product added, it was not all that hard to simply introduce the additional table. I did use a bill of material type table to track the possible stages of a product. This was simply used to make sure that (as a bad example) Tires were not added used in the supply chain for transmissions.

I do not know who/where search for it, but the automotive industry has what is perhaps the most elaborate version of this process for any non-government production / logistics control. They are able to track individual subassemblies from multiple and diverse suppliers to the individual vehicle. If you can find any discussion of their system, I am sure it would be an excellent source of information for you.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mymou]

Hi

KenReay - it really is that simple. I would never hope to start from scratch and develop an entire ERP/MRP/??P system.

From 3 different raw materials we can produce a set number of products in a set number of production stages. For the traceability issue - all information was going to be embedded in the barcodes - and I have intervened to ensure that information isn’t wasted and stored instead. There is no requirement for this project to integrate with any other system at this stage (ie there are almost none!!) – but I’m sure this need will develop in the future.

MichaelRed – As always, you take a badly formulated problem and put it into the correct perspective with the correct terms – always useful. Logistics is obviously a huge area – but my problem is much more simple. I will take your advise with multiple tables (against my own judgement) and see where I end up. I will also use a bill of material type table as you suggest - as an error check.

Thanks all for your help.

Stew