Is this the best way to have these tables set up or do they need seperated into more or less

[Cayliff]

Hello I am new to databases just learning. Here are the tables



LU is look up column from another table
PK is of course Primary Key

When I have Code as first column name it is the part number for the second column I am going to try to find a way in the form to be able to put either code or part not just one and the part will show up.

all Codes are unique.

1 powder part is connected to 1 row in all 3 process tables

blanks make powder parts and powder parts make all 3 processes

anywhere the table is blank its a lookup in another table






Colors Lookup table

PK

Code	Name	Name on Box
BK01	Black	Black/Matte Black/Shiny Black/Coal
RD04	Burgundy	Burgundy/Plum/Boysenberry
WT02	Polar White	Polar White/White/Brite White
WT06	Light Stone	Light Stone/Sand Stone/Desert Sand

Screw Types lookup table
PK (autonumbers)

1	Woodbinders
2	Steelbinders
3	ZXL Steelbinders
4	ZXL Woodbinders

Blanks Lookup table


PK LU

Code	Part	Description	Size ID	Type ID
83023	9K100TDH0Z	10 X 1 KWIKSEAL WB	83209	Woodbinder
94243	12B100DMC0Z	12 X 1 SD STEELBINDER	94933	Steelbinder
94054	12B250DMC0Z	12 X 2 1/2 SD STEELBINDER	87003	Steelbinder
29977	9K150TDH0Z	10 X 1 1/2 KWIKSEAL WB	74803	Woodbinder
29349	12B100D3Z0Z	12 X 1 SD ZXL STEELBINDER	89988	ZXL Steelbinder
29345	12B125D3Z0Z	12 X 1 1/4 ZXL STEELBINDER	78978	ZXL Steelbinder
93948	9K150K7Z0Z	10 X 1 1/2 ZXL STEELBINDER	33432	ZXL Woodbinder

The size ID is the same for all parts that use that blank, any color and any process.
the code is the part number just for the blank itself.

PROCESSES look up table
(auto number)

ID	PROCESS
1	250
2	100
3	CTN

This table will be a split form after I choose which blank to use, in the form part it will show code, blank, and its description. After I enter the Color in the same place it will change to code, powder part, description, and color name. After I enter Process 1 and/or process 2 it will change to code, process part, description, and color name.

For example


Blank
84830
9K100TDH0Z
10 X 1 WOODBINDER

POWDER PART
49234
9K100TKH0PS WT02
10 X 1 WOODBINDER
POLAR WHITE/WHITE/BRIGHT WHITE

LU LU LU LU

BLANK	BLANK QTY	POWDER TICKET	PROCESS 1	PROCESS 1 QTY	PROCESS 1 TICKET	PROCESS 2	PROCESS 2 QTY	PROCESS 2 TICKET

POWDER lookup table
PK LU LU LU

CODE	PART	BLANK ID	SIZE ID	TYPEID
84859	9K100TKH0P WT02
85792	12B125DMC0PS BK01
94757	12B125D3Z0PS RD04
84729	9K150K7Z0PS WT06

PROCESS 250 look up table

PK LU LU LU LU LU

CODE	PROCESS PART	SIZE ID	BLANK ID	POWDER ID	TYPE ID	PROCESS ID
09877	9K100TKH0PS WT02-250				WOODBINDER	250
98779	12B100DMC0PS WT06-250				STEELBINDER	250
87430	9K100K7Z0PS RD04-250				ZXL STEELBINDER	250
94770	12B125DMC0PS BK01-250				STEELBINDER	250

PROCESS 100 lookup table

PK LU LU LU LU LU

CODE	PART	SIZE ID	BLANK ID	POWDER ID	TYPE ID	PROCESS ID
87098	9K100TKH0PS RD04-100				WOODBINDER	100
73802	12B125DMC0PS WT02-100				STEELBINDER	100
89320	12B125D3Z0PS WT06-100				ZXL STEELBINDER	100
74392	9K150KZ0PS BK01-100				ZXL WOODBINDER	100

PROCESS CTN lookup table

PK LU LU LU LU LU

CODE	PROCESS	SIZE ID	BLANK ID	POWDER ID	TYPE ID	PROCESS ID
84772	9K150TKH0PS CTN				WOODBINDER	CTN
84733	12B125DMC0PS CTN				STEELBINDER	CTN
75420	12B100DMC0PS WT35-CTN				STEELBINDER	CTN
98273	9K200TKH0PS CTN				WOODBINDER	CTN

Sorry for the long post any help will be appreciated.

[Cayliff]

Sorry, didn't know it would move the LU's I places. Everywhere it has as name along with ID in field name it is a lookup from another table.

Thanks again

[June7]

Don't understand 3 Process tables. Why not 1 Processes table with another field for the process category, which you already seem to have with ProcessID, and might be better named ProcessCat.

Can build tables in post with the Advanced post editor. Can even copy/paste from Access table or Excel sheet.

[Cayliff]

Don't understand 3 Process tables. Why not 1 Processes table with another field for the process category, which you already seem to have with ProcessID, and might be better named ProcessCat.

Can build tables in post with the Advanced post editor. Can even copy/paste from Access table or Excel sheet.

ID (autonumber), process ID,code,part

Where the coma is new field. Or like this?


ID,process 1 ID,code,part, size id,powder id, type Id,blank Id, process 2 ID,code part,size Id,powder id,type Id,blank Id



If not please advise me how i should set it up.

Thanks

[June7]

No, do not mean duplicate fields.

Code	ProcessPart	SizeID	BlankID	PowderID	TypeID	ProcessID
09877	9K100TKH0PS WT02-250				WOODBINDER	250
98779	12B100DMC0PS WT06-250				STEELBINDER	250
87430	9K100K7Z0PS RD04-250				ZXL STEELBINDER	250
94770	12B125DMC0PS BK01-250				STEELBINDER	250
87098	9K100TKH0PS RD04-100				WOODBINDER	100
73802	12B125DMC0PS WT02-100				STEELBINDER	100
89320	12B125D3Z0PS WT06-100				ZXL STEELBINDER	100
74392	9K150KZ0PS BK01-100				ZXL WOODBINDER	100
84772	9K150TKH0PS CTN				WOODBINDER	CTN
84733	12B125DMC0PS CTN				STEELBINDER	CTN
75420	12B100DMC0PS WT35-CTN				STEELBINDER	CTN
98273	9K200TKH0PS CTN				WOODBINDER	CTN