Table Design Help

  • Thread starter Thread starter wonderBoy
  • Start date Start date
W

wonderBoy

Hello There:

I am trying to design something in Access and needed some
help/feedback from people who are comfortable with access.

Basically, I am trying to track a product that goes through 3
different manufacturing phases.

Here's the table structure:

productInfo
|-------|---------|---------|---------|
| pdtID | pdtName | pdtVrsn | pdtLang |
|-------|---------|---------|---------|

pdtID: Product ID
pdtName: Product Name
pdtVrsn: Product Version
pdtLang: Product Language

phaseManufacture
|-------|----------|-----------|---------|-----------|
| pdtID | sbmnDate | mfgVendor | etaDate | mfgStatus |
|-------|----------|-----------|---------|-----------|

pdtID: Product ID
sbmnDate: Submission Date
mfgVendor: Manufacturing Vendor
etaDate: Estimated Date of Completion
mfgStatus: Status of product (As provided by manufacturer (5 options))

phaseQuality
|-------|----------|----------|---------|----------|
| pdtID | sbmnDate | qaVendor | etaDate | qaStatus |
|-------|----------|----------|---------|----------|

pdtID: Product ID
sbmnDate: Submission Date
qaVendor: Quality Assurance Vendor
etaDate: Estimated Date of Completion
qaStatus: Status of product (As provided by QA vendor (3 options))

phaseRelease
|-------|----------|-----------|---------|-----------|
| pdtID | sbmnDate | relVendor | etaDate | relStatus |
|-------|----------|-----------|---------|-----------|

pdtID: Product ID
sbmnDate: Submission Date
relVendor: Release Vendor
etaDate: Estimated Date of Completion
relStatus: Status of product (As provided by go-live team (3 options))

pdtID is an autonumber and is also the primary key. What I Access to
do is to insert the same autonumber into each of the tables when its
put in the productInfo table. That way I can just lookup the productID
to track where it is in the production process. Unfortunately, I am
not very good with SQL, so any help on how to do this in access or any
online resources that can be of help will be appreciated.

More questions to follow as I develop this database.

Thank you!
 
Hi, looks like you're finding normalization where it doesn't exist.
Create one table with all the fields and populate the fields at each stage.
Note: you will need to make the field names unique for each phase (e.g.
sbmnDate_pahseManufacture, sbmnDate_phaseQuality, etc.) so that they can be
stored in the one table

If you must stick with the structure detailed then turn off the pdtdID
Autonumber for each of the phases (keep productInfo.pdtID as Autonumber) and
set their field type to Number (Long).

Hope this helps, Graeme.
 
Back
Top