Best way to handle new issue - add new table or just add to existing tables or other solution

[Dave14867]

Hello all,



Before I jump right in and start changing things and mucking everything up I would like to here from people who have a bunch more experience than I about how to solve a problem. I have created a database for tracking "Lots" of various items (for traceability purposes) And for the most part it seems to be working well, still some polish to put on it but it does work for what I want. I ran into an issue today where when any item that has a "deviation" must be tracked thru out any future "Lots" that contain that specific "Lot Number" of material in it.

Currently the database has 20+ tables of various Items, each item contains multiple "Lot Numbers" of the items that go into making the new Item with it's own "Lot Number".

Should I add a new field to each table to hold a deviation #, and create a new table for the Deviation info and link every existing table to it? ( this would require an "ID" link for each table as well correct)

Should I just add the new required fields to each existing table?

How to I handle finding (in a query and subsequent report) and highlighting any other "Items" that use a "Lot Number" of an Item that had a deviation applied?

I hope that explains it well enough, if not, let me know and I'll try to explain it better.

I want to do this the correct way and don't have enough of my own experience to know the best way to handle it as I am a novice with access.

Thanks

Dave

[CJ_London]

sounds like you do not have a correct table structure. From what you describe I would expect 1 table, with a field which contains an ID for the item.

regret I do not understand what you are trying to achieve, what is a deviation? how is it indicated? what is a new item? Perhaps explain in simple terms what your business is and what the database is supposed to do

[Dave14867]

Ajax,

We have multiple items (parts) that are made up of various other parts. Most of these parts have a unique lot number that MUST be traceable, each "Lot" of a part gets it own unique "Lot Number", That part then goes into another "Item" or part and so on.

A Deviation is a document that states that a given part "deviates" from normal processing for some reason and gives details, instructions, etc. If a part has a deviation associated with it, I must be able to identify and other items downstream that contain that Part & Lot Number

Does that make sense?

Each "Part" has it's own table (20+ different parts)

Thanks

Dave

[CJ_London]

not really. this 'Each "Part" has it's own table (20+ different parts)' tells me your tables are not set up correctly. If you cannot explain, then I cannot help. I understand traceability - at least in the context of the food and timber industries. You are intermixing items and parts in your description and I can get no visual concept of what you are trying to do.

However I am signing off now - have work to do, perhaps someone else can pick up the thread

[Dave14867]

Ajax,

Imagine a Chemical formulation Part# 1234, is made up of Milk (Part# 5678) and Honey (Part# 5AAA), I have a table set up for Part# 1234 that contains fields for the Unique "Lot Numbers" of Part#'s 5678 and 5AAA as well as some other fields like "Date of Manufacture", "Document Rev", etc.

Each time a new "Lot Number" or batch of Part#1234 is created, a new record is created in the table that includes the Lot #'s of Part#'s 5678, and 5AAA, the "Date of Manufacture", 'Document Rev", etc. Anything that went into the making of that "Lot Number" of Part# 1234. So each new record for any part contains the "Lot Numbers' of any parts that went into it.


Thanks

Dave

[orange]

Dave,

Please describe a walk through of building a formulation -step by step.
It seems a Part can be both a "component" and a "finished product".

What exactly is involved in tracking? Can you give a few examples? What info is required exactly?

[ArviLaanemets]

Looks like a typical case of describing the product structure.

At start consolidate all your item tables into one, as you were adviced before, and add an Items/articles/parts table. The simplest reason - imagine you get some new item. With current design, it means you have to add a new table, and redesign all your forms, reports and most of calculations. By adviced design, you register the new item, an that's all - the rest of application is working as before, you can use the new part to assign it to some lot, etc.

To follow the composition of items, you must have a structure table.

The simplest design for structure table will be something like SructureID, ItemID, ParentItemID, ItemQty - but it is also most difficult to use, when the item is composed from other items, which are composed from other items, which... To get the whole BOM list (a structured list of all components of product) you need a recursive query (and I'm not sure Access allows it), or you have to calculate it using VBA.

The other way is to define products, and to save this the product structure like BOM list in structure table - with position index calculated for every structur row.

[Dave14867]

orange,

Yes a part can be both a component and a finished product, The ultimate end product for this database is a "Kit" which consists of several Sub components and each of those can also have sub components.

Part A – is made up of several parts, each with it’s own unique part number and a unique Lot number for each part used, thus the table for Part A will have.
Part A Table
PartAID
PartALotNum
Part1LotNum
Part2LotNum
Part3LotNum
DOM
DocRev

Each new “Batch” of Part A is given it’s own unique “Lot Number” lets say ABC123.

Part A is then used to make Part B along with some other parts so Part B table looks like this.

Part B Table
PartBID
PartBLotNum
PartALotNum
PartXLotNum
PartYLotNum
DOM
DocRev

Does that help explain it better?

Thanks
Dave

[Dave14867]

Arvi,

I am not sure what you mean. I don't think I have enough room (255 fields) to put all of the tables into 1 table, unless I am misunderstanding you.

Thanks

Dave

[June7]

Manufacturing/assembly type database common topic. Have you searched forum and reviewed other discussions?

[ArviLaanemets]

I don't think I have enough room (255 fields) to put all of the tables into 1 table, unless I am misunderstanding you.

I'm afraid you are

At start let's set up the terminology. You are writing you have some items and those items have lot numbers. Here I am confused - for me a lot is a container with certain number of items packed into. P.e. when we send some quantity of items, or we buy some quantity of items, and the delivery is scheduled p.e. weekly, then the items are sent package-wise, and the number of items in such package is named 'lot size'. I don't belive this is what you mean with 'lot number'.

As you use 'lot number' as some characteristic of items, it feels that maybe it is same what I'm used to name 'article number' or 'part number' - a some code which uniquely defines a certain item/article/part. So when I'm right with this, then you have some articles (items) which have some article number (lot number). (And it looks like you have a separate table for every article?). And some articles are composed/produced using other articles as components. Am I right about this?

I wasn't writing, that you have to all your info into one table, but that you must have a single table where all articles are defined.
P.e.
tblArticles: ArtID, ArtType, ArtNo, ArtName, ArtUnit, ArtCreated, ArtStatus, ...
This table contains all info about article, which you can put into single row, and which doesn't change over time, or which current value is written directly into other tables and is not used in calculations. The number of such fields can be about 100 for big ERP systems, but hardly more, and I don't think you need so much of them.

Then there will be other tables, where additional info about articles is stored, like prices, suppliers for purchase articles, etc.

One such additional table will be structure table I did write.
tblArtStructure: ArtStrID, ArtID, ParentArtID, ArtQty, ...
, where ArtID determines the article which is the component of another article determined by ParentArtID, and ArtQty is the quantity of ArtID needed for producing one unit of ParentArtID.

Now when you need to plan the production of some article, you search the structure table for all articles having product as parent article. Then you repeat the same for all found 1st level components. and so on until you don't find any components anymore (usually every chain ends with purchase article(s)). The list of articles with quantities gotten this way and organized in certain order is named BOM list, it determines how much of every components and in which order are needed to produce one unit of product, and is a used for planning purchases and to organize production.

Edit. I just read your post #8, ad it looks like your tables are structure tables - a one for every product or produced component. It means, that you have to design your database anew whenever you have a new product or produced component.

One way is to design the structure table for products, and so, that a row in table will be essentially a BOM list row. An example:
tblProductStructure: ProdStrID, StrCode, ProdArtId, CompArtID, CompQty
, where
ProdStrID - autonumeric primary key;
StrCode - an unique string for product, which determines the position of given component in product's BOM list. I have used StrCode with up to 25 structure levels, and the component's position in chain is determined with 2-character number pair. When you use only numbers, then you can have up to 99 components for every article in structure, when you use 32-bit numbers, then over 1000 components. StrCodes will be like:
'00' - a product itself;
'01' - 1st component on 1st level;
'0101' - 1st 2nd level component of 1st level 1st component;
'0102' - 2nd 2nd level component of 1st level 1st component;
'010201'
'0103'
'02'
... etc.;
ProdArtID - article id of product (same for all rows of this product structure). You need a products table too, from where you get ProdArtID (which must be unique in products table);
CompArtID - article id of component described on this structure row. For StrCode = '00' you leave it empty, or fill it with ProdArtID;
CompQty - the quantity of CompArtID for this structure row.

When some product components are produced directly in production chain, then their structure is included into product structure. When those parts are produced separately, and are waiting in storage to be used, then their structure is usually excluded from product structure (the structure chain ends with such component).

I have used product structure table like this to get the product structure from our ERP system (which has the structure table like I described earlier) into Excel to get some reports.

[Dave14867]

Arvi,

I have attached a sample of what I have in the database. It has 6 tables and forms that I use to populate each table. I should also state that this isn't intended to actually run our operation like a true MRP database, it is only intended at this time to be a useful tool to aid myself and a handful of other to determine where any given "Lot" of a Part Number was used and what other Part Numbers was it used in the creation of.

I keep reading over your post and I think you are correct that there is a need to clarify the terminology, and I admit, I use some terms interchangeably.

Part Number - a unique identifier given to a single item (in my case a 5 digit number preceded by "M" i.e. M12345)
Lot Number - a unique identifier given to a "Lot" of Part Numbers that were received or produced internally, internal produced lots have a unique 6 digit identifier between 400000 and 499999, items received from an external source have a unique 6 digit identifier between 800000 and 899999. For both types, a "Log Book" is maintained and whenever a new "lot Number" is required, the next number in sequence is used and no lot number can ever be re-assigned. (See Sample file for "Lot Number" table for internal Parts)

This is also just my own little project and most of it is being done on my own time just to create a tool to help me out and like I said, a handful of others (3-4) look up to determine where was a particular Lot Number of a given Part Number, used. And in this particular case, If a particular part had a "Deviation" tied to it, what else is affected since everything downstream of that will be affected. Currently I don't have a way to assign a deviation to any record.

Could you possibly create a table in the attached file to show me what you are referring to tblProductStucture, once I visually see what your talking about it will probably become clear to me.


Thanks

Dave

Sample.zip

[ArviLaanemets]

I'll try to have a look during weekend.

[Dave14867]

That will be great, in the meantime, I am off today and plan on digesting what you have suggested so far and see how I can try to incorporate that as well as doing some more reading on the subject.

Thanks

Dave

[ArviLaanemets]

I had a look, and I don't get the logic of your Lot - tables!

Lets have a look p.e. at tblKABRSA-12. It has 2 rows with KABRSA-12ID IN (1,2). Let's continue with KABRSA-12ID = 1
There is a field M23138ID. It looks like reference to PartNum in tblLotNum and has a value 15. But only ID in tblLotNim is LotID, and LotID = 15 refers to PartNum = 'M13472'. In tblLotNum, matching LotID values are (1,23,38,82,115,153,149,163,185,211,235)
The next field is ;22302ID. again it look like reference to tblPartNum, but LotID = 4 in tblPartNum refers to PartNum = 'M16361'. For PartNum = 'M22302' matching LotID values are (39,119,160,172,214,244).
Then there is a field M22305LotNum = 401776. But in tblLotNum is no entry with such LotNum!

So I have no clue, what various fields in lot-tables are meaning, and from where the values in those tables are gotten! And without this information I can't help - for me those tables are currently meaningless.