Adding a new subject to all my tables

T

teacheyp

I am a new user to Access and I am hoping someone can help me. I have
created a database on research subject for a study. I have 14 tables in my
database that have all my subjects listed in them. The tables are set up by
the month the subject comes in for the tests. Since the subjects did not
start the study at the same time, I need all the subjects listed in all the
tables so we can know where they are in the study. Is there a way where I can
add a new subject to all my tables at the same time? I have a main table
that list all information about the subject and then the rest of the tables
are the different months that they came. In those tables are the tests they
took and their scores. Every table has one field that is the same, it is the
ID number assigned to each subject and it is the primary key in all tables.
 
B

Beetle

You're making things much more difficult than they need to be. The evidence
of this fact is your current problem. Every time you add a new test subject,
you will have to add them to every table in your db. This is just one of the
many problems you will eventually run into with your current structure.

Based on what little I know about your app., you appear to have a
many-to-many relationship between a test subject and the actual tests.
To resolve this relationship you need 3 tables (not 14). As an example;

tblSubjects
********
SubjectID (Primary Key)
FirstName
LastName
EntryDate
ContactNumber
other fields related specifically to the subject

tblTests
******
TestID (PK)
TestName
TestType
other fields related specifically to a test

tblSubjectTests
***********
SubjectID (Foreign Key to tblSubjects)
TestID (FK to tblTests)
TestDate 'the date a certain Subject took a certain Test

The PK for tblSubjectTests would typically be a combination of the
SubjectID and TestID fields unless a Subject can take the same test
more than once, then you may need a separate PK field. Or you could
add TestDate as a third field in the key.

The EntryDate field in tblSubjects should be all you need to determine
when a Subject began the study. You do not need a table for every month.

Here are some links where you can find some good information about
proper relational database design.

http://www.mvps.org/access/

http://allenbrowne.com/casu-22.html

http://www.accessmvp.com/JConrad/accessjunkie.html
 
J

John W. Vinson

I am a new user to Access and I am hoping someone can help me. I have
created a database on research subject for a study. I have 14 tables in my
database that have all my subjects listed in them. The tables are set up by
the month the subject comes in for the tests. Since the subjects did not
start the study at the same time, I need all the subjects listed in all the
tables so we can know where they are in the study. Is there a way where I can
add a new subject to all my tables at the same time? I have a main table
that list all information about the subject and then the rest of the tables
are the different months that they came. In those tables are the tests they
took and their scores. Every table has one field that is the same, it is the
ID number assigned to each subject and it is the primary key in all tables.

Your database design IS WRONG.

You should have *ONE* table of subjects, and the subject should exist in that
table, and only in that table.

You should have *ONE* table of studies, with information about the nature of
the study, date started, etc.

These tables would both be linked to a StudyParticipation table with a link
(not a copy of the data!!) to the Study table and to the Subject table. This
would have dates of participation, etc.

You're using a relational database - use it relationally! It appears that
you're taking a "spreadsheet" approach, which will be *very* difficult to work
with. There are some tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
T

teacheyp via AccessMonster.com

Thank you for your response. I am now rethinking my database. I will start
over and see if I can make it just three tables.
You're making things much more difficult than they need to be. The evidence
of this fact is your current problem. Every time you add a new test subject,
you will have to add them to every table in your db. This is just one of the
many problems you will eventually run into with your current structure.

Based on what little I know about your app., you appear to have a
many-to-many relationship between a test subject and the actual tests.
To resolve this relationship you need 3 tables (not 14). As an example;

tblSubjects
********
SubjectID (Primary Key)
FirstName
LastName
EntryDate
ContactNumber
other fields related specifically to the subject

tblTests
******
TestID (PK)
TestName
TestType
other fields related specifically to a test

tblSubjectTests
***********
SubjectID (Foreign Key to tblSubjects)
TestID (FK to tblTests)
TestDate 'the date a certain Subject took a certain Test

The PK for tblSubjectTests would typically be a combination of the
SubjectID and TestID fields unless a Subject can take the same test
more than once, then you may need a separate PK field. Or you could
add TestDate as a third field in the key.

The EntryDate field in tblSubjects should be all you need to determine
when a Subject began the study. You do not need a table for every month.

Here are some links where you can find some good information about
proper relational database design.

http://www.mvps.org/access/

http://allenbrowne.com/casu-22.html

http://www.accessmvp.com/JConrad/accessjunkie.html
I am a new user to Access and I am hoping someone can help me. I have
created a database on research subject for a study. I have 14 tables in my
[quoted text clipped - 7 lines]
took and their scores. Every table has one field that is the same, it is the
ID number assigned to each subject and it is the primary key in all tables.
 
T

teacheyp via AccessMonster.com

Beetle, I want to thank you so much. It was like a light bulb went off in my
head. Your suggestions on how I should set up my database were wonderfull.
I recreated my database with 4 tables (I know you suggested 3 ,I have an odd
test that I needed to have separate) and it all works, Yeah!!! Sadly I am
going to have to start from scratch and input all the data I have already
collected, but I am ok with that. Thanks again.
You're making things much more difficult than they need to be. The evidence
of this fact is your current problem. Every time you add a new test subject,
you will have to add them to every table in your db. This is just one of the
many problems you will eventually run into with your current structure.

Based on what little I know about your app., you appear to have a
many-to-many relationship between a test subject and the actual tests.
To resolve this relationship you need 3 tables (not 14). As an example;

tblSubjects
********
SubjectID (Primary Key)
FirstName
LastName
EntryDate
ContactNumber
other fields related specifically to the subject

tblTests
******
TestID (PK)
TestName
TestType
other fields related specifically to a test

tblSubjectTests
***********
SubjectID (Foreign Key to tblSubjects)
TestID (FK to tblTests)
TestDate 'the date a certain Subject took a certain Test

The PK for tblSubjectTests would typically be a combination of the
SubjectID and TestID fields unless a Subject can take the same test
more than once, then you may need a separate PK field. Or you could
add TestDate as a third field in the key.

The EntryDate field in tblSubjects should be all you need to determine
when a Subject began the study. You do not need a table for every month.

Here are some links where you can find some good information about
proper relational database design.

http://www.mvps.org/access/

http://allenbrowne.com/casu-22.html

http://www.accessmvp.com/JConrad/accessjunkie.html
I am a new user to Access and I am hoping someone can help me. I have
created a database on research subject for a study. I have 14 tables in my
[quoted text clipped - 7 lines]
took and their scores. Every table has one field that is the same, it is the
ID number assigned to each subject and it is the primary key in all tables.
 
J

John W. Vinson

Beetle, I want to thank you so much. It was like a light bulb went off in my
head. Your suggestions on how I should set up my database were wonderfull.
I recreated my database with 4 tables (I know you suggested 3 ,I have an odd
test that I needed to have separate) and it all works, Yeah!!! Sadly I am
going to have to start from scratch and input all the data I have already
collected, but I am ok with that. Thanks again.

Actually you can probably migrate the data from your existing table into the
new ones with some appropriate Append queries. If you'll post the data
structures of the tables someone can help you create such a query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top