Table Structure and relationships

[khalakmu]

Hi,

I am creating small Database in Access 2007. Entering Data and
Producing Report of Products.

1. tblProduct (Pk auto, Description,etc. )
1:M
2. tblProductSpecs (pk auto,fkProductID,Size,Shape,PaperType,PaperColor,
.. PaperSize,PrintType)

Now on Inspection either Product has Defects or No defect.

Created tblFaults (pk auto, Sizefault,Shapefault,PapertypeFault,
PapersizeFault,PrintTypeFault)
How I create Relationship .. do I create another table (transition? )

I would Like entry form so I can enter all data.
can somebody point me in right direction please

Thanks
Khalak

 

[tehNellie]

Sounds like Faults is a property of the inspection which appears to be your missing link at the moment

i.e.

tblInspection
------------
inspectionID - PK
productID - FK
faultID - FK
EngineerID - FK
inspectionDate
etc.
etc

 

[nanscombe]

If there were more than 1 fault possible per Inspection.

tblFaults
-------------
faultID - PK
inspectionID - FK
faultDescription
remedialAction (Where you could note remedial action)
remedialActionComplete -Yes/No

 

[khalakmu]

Hi tehNille and Nigel,

Thanks for the reply. I created tblInspection, I have tblFaults
should I create table Faultcategory ?

tblProducts 1:M tblInspection and how tblFault will relate ?
tblInspection 1:M tblFaults .. ?

How I should do. Please. Thanks in advance.

Khalak

 

[nanscombe]

Yes, I would see it as

tblProducts 1:M tblInspections 1:M tblFaults

Rather than have FaultCategory in its own table I tend to have a table that holds all the items that I need to make all of my lists.

Something like

tblLists
--------
ID Pk
ListType - String (Examples FaultCategory : PaperSize : PaperColour)
ListItem - String (Examples Minor, Major, Beyond repair : A4, A5 : Red, Blue etc)
ListNumber - Number (In case your list contains numbers)
ListOrder - Number (In case you want to order your lists in a particular way)

That table could then be used as a source of all of your lists depending on which ListType you select.

 

[khalakmu]

Hi Nigel,

Many thanks for quick reply. I like Idea of table tbllist.
Will study and correlate it with present situation.
Appreciate a lot.

Thanks
Khalak

 

[nanscombe]

One field I forgot from tblList is ListParent - Number (So you could link back to a Parent Item in the same table) if needs be.

 

[khalakmu]

Hi Nigel,
I am little confused about this new field ListParent (num),
could you elaborate it. please. i will be much obliged.
thanks
khalak

 

[nanscombe]

ListParent could be used to link back to a Parent record in the same table.

Your have a list of Areas linked backed to a list of Regions, an example in England could be:

Regions: South East, South West
Areas: Kent, East Sussex, Cornwall, Devon

Kent & East Sussex could be linked back to the Parent Region South East
Cornwall & Devon could be linked back to Parent Region South West.


Or you could have; Red, Blue, Green linked back to a Parent called PaperColours.

I hope this helps

 

[khalakmu]

Many many Thanks Nigel,

All tables mentioned I created and studied, appreciate very much.
From this tables creation and looking in details of it. I got idea of
using these into a new Access 2007 Database, which is related with my hobby of different Collections.

Let me go to drawing board and make some tables and relationship, than
I will be back. I am sure as new bee and learner I will have many questions
till reasonable normalization of Tables, before I go for creation of forms,
reports etc.
I am sure I will get help in this task,

thanks
khalak