Tricky Table formatting

[FlyingDisc]

I am currently having some trouble designing a table for a specific problem at my work. Here is the capability I am looking for:

1 Patient name, up to 5 tests (number of tests and name of test need to be adjustable variables), and test scores for each test for up to 21 weeks.

The form would essentially be a 5x21 table for each patient/record, with the rows being different tests, and the 21 columns being different weeks. However, I can't fit this all on one table (Test1Week1, Test1Week2, etc. results in a table with >100 fields and thus many usability problems). I have tried sub-forms, relationships, queries, anything I can think of, but I am fairly new to access and don't seem to have the ingenuity to solve this problem on my own. Any ideas would be GREATLY appreciated.

 

[Kryst51]

Hi, I would read up on normalization.

These tutorials might help.

Another site

 

[vbaInet]

Just throwing in the Microsoft version on normalisation for good measure

http://support.microsoft.com/kb/100139

 

[MSAccessRookie]

I am currently having some trouble designing a table for a specific problem at my work. Here is the capability I am looking for:

1 Patient name, up to 5 tests (number of tests and name of test need to be adjustable variables), and test scores for each test for up to 21 weeks.

The form would essentially be a 5x21 table for each patient/record, with the rows being different tests, and the 21 columns being different weeks. However, I can't fit this all on one table (Test1Week1, Test1Week2, etc. results in a table with >100 fields and thus many usability problems). I have tried sub-forms, relationships, queries, anything I can think of, but I am fairly new to access and don't seem to have the ingenuity to solve this problem on my own. Any ideas would be GREATLY appreciated.

The design for your database sounds more like an Excel Spreadsheet. While this approach can be done, it should be avoided, as it will not be able to not take advantage of the power of a true relational database. Your database should have at least the following tables:

a Table for People related information
a Table for Test Description related information
a Table for Tests that people have taken/will take
​

While I am sure that there will be other tables as well, these will provide a starting point. Reading about Normalization as the others have suggested will be a big help as well.