Hi,
I am new to Access, and I was hoping for advice on how to setup my database.
I'm trying to setup a database that will track which raw materials/submodules are built into submodules/products at various levels of assembly.
So I have 3 related tables:
KITTING: Records the workorder (WO)# and product being built
SMT: Records information about the workorder during the Surface Mount step (when components are added)
SMT_TRACK: Records which raw material / submodule is built into the workorder
There is a one-many relationship between KITTING and SMT, and a one-many relationship between SMT and SMT_TRACK
One problem is that the various products that will be entered into these tables don't all have the same information linked to them. Here is a list of all the different types of products that will be input into the KITTING table, and where they'll be used as inputs into both the SMT and SMT_TRACK tables:
COMPONENT W. BATCH - PO#, BATCH#, WAFER#. Data is used in SMT_TRACK table.
COMPONENT WO BATCH - PO#. Data is used in SMT_TRACK table
FLEX / PCB BASE - PO#
CERAMIC BASE WO#, BATCH#
IPD BASE WO#, BATCH#, WAFER#
DIE/WAFER PROCESSING WO#, BATCH#, WAFER#. Data is used in SMT_TRACK
SUBMOD, FLEX/PCB BASE: WO#, PO#. Data is used in both SMT and SMT_TRACK
SUBMOD, CERAMIC BASE: WO#, BATCH#, Data is used in both SMT and SMT_TRACK
SUBMOD, IPD BASE: WO#, BATCH#, WAFER#, data is used in both SMT and SMT_TRACK
MODULE, CERAMIC BASE: WO#, BATCH#, data is used in SMT
MODULE, FLEX/PCB BASE: WO#, PO#, data is used in SMT
MODULE, IPD BASE: WO#, BATCH#, WAFER#, data is used in SMT
Firstly, I'd like advise on the general setup of this database. Currently, I'd planned on having just the 3 tables (plus a lookup table for products). Since in my product list I have classified all products as per the list above, I can create queries to sort out each table based on which product when I need to. Any time I consider splitting the tables (ie having 2 KITTING tables, one for WO-based items and one for non-WO based items) I run into questions on how to handle them on the input to forms... (when I'm doing the SMT_TRACK form, I'd have to have separate tables there depending on which KIT table is being placed) It also makes me think the Queries/Reports I generate would become messy.
Right now I'm thinking keeping them in the same table is the right way to go. What do you advise?
Any help or advise you can provide is greatly appreciated!!!
I am new to Access, and I was hoping for advice on how to setup my database.
I'm trying to setup a database that will track which raw materials/submodules are built into submodules/products at various levels of assembly.
So I have 3 related tables:
KITTING: Records the workorder (WO)# and product being built
SMT: Records information about the workorder during the Surface Mount step (when components are added)
SMT_TRACK: Records which raw material / submodule is built into the workorder
There is a one-many relationship between KITTING and SMT, and a one-many relationship between SMT and SMT_TRACK
One problem is that the various products that will be entered into these tables don't all have the same information linked to them. Here is a list of all the different types of products that will be input into the KITTING table, and where they'll be used as inputs into both the SMT and SMT_TRACK tables:
COMPONENT W. BATCH - PO#, BATCH#, WAFER#. Data is used in SMT_TRACK table.
COMPONENT WO BATCH - PO#. Data is used in SMT_TRACK table
FLEX / PCB BASE - PO#
CERAMIC BASE WO#, BATCH#
IPD BASE WO#, BATCH#, WAFER#
DIE/WAFER PROCESSING WO#, BATCH#, WAFER#. Data is used in SMT_TRACK
SUBMOD, FLEX/PCB BASE: WO#, PO#. Data is used in both SMT and SMT_TRACK
SUBMOD, CERAMIC BASE: WO#, BATCH#, Data is used in both SMT and SMT_TRACK
SUBMOD, IPD BASE: WO#, BATCH#, WAFER#, data is used in both SMT and SMT_TRACK
MODULE, CERAMIC BASE: WO#, BATCH#, data is used in SMT
MODULE, FLEX/PCB BASE: WO#, PO#, data is used in SMT
MODULE, IPD BASE: WO#, BATCH#, WAFER#, data is used in SMT
Firstly, I'd like advise on the general setup of this database. Currently, I'd planned on having just the 3 tables (plus a lookup table for products). Since in my product list I have classified all products as per the list above, I can create queries to sort out each table based on which product when I need to. Any time I consider splitting the tables (ie having 2 KITTING tables, one for WO-based items and one for non-WO based items) I run into questions on how to handle them on the input to forms... (when I'm doing the SMT_TRACK form, I'd have to have separate tables there depending on which KIT table is being placed) It also makes me think the Queries/Reports I generate would become messy.
Right now I'm thinking keeping them in the same table is the right way to go. What do you advise?
Any help or advise you can provide is greatly appreciated!!!
