Table Design for the following data sheet

[masood]

Hello access users.. Help me in designing a table in which the rows should consist of six parameters(neglect their names) and each field should contain a machine number(we have 5 machines).. and all these data should be entered on a daily basis....



A sample data sheet has been attached for your reference... Kindly help...

[jzwp11]

You would need 3 tables in a relational database to capture the data you show in the spreadsheet

tblMachines (each machine would be a record in this table)
-pkMachineID primary key, autonumber
-txtMachineName

tblParameters (each parameter would be a record in this table)
-pkParameterID primary key, autonumber
-txtParameterName

Now a table to relate the parameters applicable to each machine

tblMachineParameters
-pkMachParaID primary key, autonumber
-fkMachineID foreign key to tblMachines
-fkParameterID foreign key to tblParameter
-MachineParameterValue


Since you say you have 5 machines each with 6 parameters, you would have 30 records in tblMachineParameters.

[masood]

Thanks to JZWP11 from my heart for help ... But the table design given by you is sufficient for one day only... what if I want to use the same table daily.>?? How can I relate the date with the parameter and the primary key....???

Please help..... I am very confused.....

[jzwp11]

It sounds like you are doing daily inspections, so a table to hold that:


tblInspections
-pkInspectID primary key, autonumber
-dteInspect (date)
other fields related to the inspection

We need to remove the field -MachineParameterValue from tblMachineParameters, so that table is now as follows:

tblMachineParameters
-pkMachParaID primary key, autonumber
-fkMachineID foreign key to tblMachines
-fkParameterID foreign key to tblParameter


During an inspection you will gather results for many machine/parameters (one-to-many relationship).


tblInspectionResults
-pkInspectResultID primary key, autonumber
-fkInspectID foreign key to tblInspections
-fkMachParaID foreign key to tblMachineParameters
-InspectMachineParameterValue (the actual value for the machine/parameter for the particular inspection)

[masood]

It sounds like you are doing daily inspections, so a table to hold that:


tblInspections
-pkInspectID primary key, autonumber
-dteInspect (date)
other fields related to the inspection

We need to remove the field -MachineParameterValue from tblMachineParameters, so that table is now as follows:

tblMachineParameters
-pkMachParaID primary key, autonumber
-fkMachineID foreign key to tblMachines
-fkParameterID foreign key to tblParameter


During an inspection you will gather results for many machine/parameters (one-to-many relationship).


tblInspectionResults
-pkInspectResultID primary key, autonumber
-fkInspectID foreign key to tblInspections
-fkMachParaID foreign key to tblMachineParameters
-InspectMachineParameterValue (the actual value for the machine/parameter for the particular inspection)

Thanks once again...... Here i am attaching an excel workbook which contains both the formats of the tables and the report. Please have a look and suggest me whether should I select Excel or Access to create these....

[jzwp11]

I am partial to Access so my opinion is biased. If you have a large amount of data to capture and store, a database is usually the preferred method rather than a spreadsheet. However, learning how to structure data correctly in a relational database is a skill that takes time to learn and develop. Then you also have to learn how to use Access or any other relational database software. So one might say that the learning curve with a relational database program like Access is steeper & longer than the one for Excel. If you are willing to invest that time then I would recommend Access, if not then you will be better off with Excel.

[masood]

I am partial to Access so my opinion is biased. If you have a large amount of data to capture and store, a database is usually the preferred method rather than a spreadsheet. However, learning how to structure data correctly in a relational database is a skill that takes time to learn and develop. Then you also have to learn how to use Access or any other relational database software. So one might say that the learning curve with a relational database program like Access is steeper & longer than the one for Excel. If you are willing to invest that time then I would recommend Access, if not then you will be better off with Excel.

Thank you jzwp11 for your valuable suggestion........

[jzwp11]

You're welcome; good luck with whichever option you decide to go.