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

[Dave14867]

Arvi,



tblLotNumbers isn't linked to anything and I don't currently use that for anything other than keeping track of the "Lot Numbers" that get manually assigned in a log book. So those won't link up. I have considered using this but currently don't.

Lot number 401776 in the M22305LotNum field is a special lot that doesn't follow the normal path, this is actually a part that is received, we send a vendor some material that we make in house and tell them the lot number to use for it and when they send it back after their processing, it has the lot number we gave them which is a unique lot for that shipment only.

M23138ID is the id in the tblM23138 table (not included in the sample file, sorry my fault)

If you look at the M22302ID field it will match up with the ID in the tblM22302.

Thanks

Dave

[ArviLaanemets]

I'm not sure about your data structure still, but anyway I'll make a try. Probably you get some ideas.

At first, your main problem is, that you have to design new tables and new forms whenever a new part or lot is registered. You need a setup, where you design tables and forms once, and after that you only enter data. The new design must be needed only, when you have to add new functionality.

As first step, you need a table where all parts are registered. And I mean ALL parts! You can't send anything what is not registered, you can't order anything, what is not registered, you can't produce anything, what is not registered etc. So you need a table
tblParts: PartID, PartNum, PartType, ...
For me, your Lot's look very like what I know as fictitious parts/articles (PartType has a value which refers to fictitious one) - the sets of parts. So your Lots must be also registered in tblParts (NB! The lots in general, not certain lot which is sent somewhere).

The next table is
tblPartStructure: StructureID, Main_PartNum, StructureRow, Component_PartNum, ...

As example I'll use KABRSA_12 as example. The table tblParts:

PartID	PartNum	PartType	DHR_Rev
1	KABRSA_12	8	A
2	M08082	1
3	M13472	1
4	M16092	1
5	M16115	1	A
6	M16695	1
7	M22302	8	A
8	M22305	1
9	M22368	8
10	M22866	1
11	M23238	1
12	M23621	1

For tblPartStructure is 2 possibilities.
All lots are described, and in structure table the lots are not detailized:

Main_PartNum	StructureRow	Component_PartNum
KABRSA_12	00
KABRSA_12	01	M23238
KABRSA_12	02	M22302
KABRSA_12	03	M22305
M22302	00
M22302	01	M16092
M22302	02	M22368
M22368	00
M22368	01	M13472
M22368	02	M23621
M22368	03	M22866
M22368	04	M16115
M22368	05	M08082

Or component lots are detailized:

Main_PartNum
StructureRow
Component_PartNum

KABRSA_12
00


KABRSA_12
01
M23238

KABRSA_12
02
M22302

KABRSA_12
0201
M16092

KABRSA_12
0202
M22368

KABRSA_12
020201
M13472

KABRSA_12
020202
M23621

KABRSA_12
020203
M22866

KABRSA_12
020204
M16115

KABRSA_12
020205
M08082

KABRSA_12
0203
M16695

KABRSA_12
03
M22305

Now, when you deliver a lot (p.e. ID=1 in your tblKARBSA_12 ), you sometimes send some structure row element several times, ans some not at all. So probably need some table where you determine which rows from structure table are involved. Something like tblDeliveryRows:

Delivery_Row	Main_PartNum	StructureRow	Lot_ID	LotNum	DHR_Rev	DOM
	KABRSA_12	00	1	402720	A	27.10.2017
1	KABRSA_12	01	15
2	KABRSA_12	02	4	402722	A
3	KABRSA_12	0201		800818
4	KABRSA_12	0202	15	800719
5	KABRSA_12	020201	36
6	KABRSA_12	020201	39
7	KABRSA_12	020208	6
8	KABRSA_12	020209		800855	A
9	KABRSA_12	020210		800819
10	KABRSA_12	0203		800817
11	KABRSA_12	03		401776

I did not add ID fields to examples, except for tblParts, but of-course they are needed too (unless you go for multy-field pK)