Creating multiple sub datasheets in access

Question

Hello,

I have little project I need to do for school- we run multiple tests in our lab- each time with minor tweaks and we log the results in excel spreadsheet. We've Accumulated quite a lot of tests data and we want to reorganize it using access. Right now I have tables for each test and I'm trying to organize them as Subdatasheet for the major table that has the test name, date and description (so when I click on a certain row in the table I can see this particular test results). I've defined all the test data sheet and the main datasheet to have one-to-many relationship but then every time I try to add an experiment as a Subdatasheet it deletes all the other sub datasheets. What am I doing wrong? (to link them I defined the first column of each experiment to be the row number in the main table)

Answer 1

Users should NOT directly interact with tables. While subdatasheets are a nice feature they are limited.

You can easily accomplish what you want using forms and subforms. You can have a main form bound to the major table and then as many subforms bound to the respective test tables. You can use a Tab control if you need more space.

On a separate note, you should only have separate tables for test results if the fields holding the results data are different for each test. If they are the same, then you should have one table with a field that identifies the test.

Even better you can have a table like this

tblTestResults

TestResultID (PK Autonumber)

TestTypeID (FK)

ResultID (FK)

ResultValue

This is a better normalized table and show all your results.

One other point. I would not accelt the default names for the ID field. I always name my Autonumber PKs tablenameID. This make it easy to see the relationships at a glance.