I am trying to create a relational database but am very new to Access. I have an excel spreadsheet of student information (First name, Last name) which I imported into a table with a primary key of an Auto ID number. I created another table that is comprised of various student activities, each with its own unique 3 letter code which is the primary key of that table. Since this is a many-to-many relationship (many students participating in any given activity, one student participating in many activities), I created a third table that has two primary keys - one is the Auto ID number from the first table and the other is the Activity Code from the second table. I created relationships between the 3 tables linking the primary keys from the first two tables to the same primary key in the linking table. What I would like to be able to do is pull up a student's name and enter in the activities that he/she participates in and then eventually create a list of student who participate in a given activity. My question is: have I set it up properly to do this and how do I do this data entry and data analysis once it is set up properly. I have tried doing forms and queries but they don't give me what I need. Thanks....
Many to Many (1 Viewer)
- Thread starter learner
- Start date
- Local time
- Today, 04:01
- Joined
- Sep 12, 2006
- Messages
- 15,659
you have set it up properly
student (id, firstname, lastname, dob, etc)
activity (id, activityname, coursdeduration, etc)
activitymember (studentid, activityid)
-------------------
so now when you pull up a student you need to store information about the activities he does in the activitymember table
for this you need the dbs forms set up in a certain way
ie you need a main form, showing the student
and a sub form showing the activities for that student. (ie based on the activivitymember course)
[there sre other methods but this will show you some techniques]
access can automatically link these two together so as you step through the students all their activities are brought up
------------------------
you can then use a similar technique to scroll through courses, and identify all the members of those courses.
----------------------------
try this - create a main form for the students - you probably already have one.
create another form, based on a query that links activity table AND the activitymembers, so you see the studentid and the activityID AND activityNAME in your query (because an id only generally isnt that useful)
now create a sub form in the main form - you can do this by dragging the second form into the detail section of the first form, and moving the controls around, to get apleasnat layout
if Access doesn't set the link automatically, do it yourself - in the mainform, select the new subform, and in properties select the link:
studentid in master form, and studentid in child form
------------------
now as you step through the students in the main form, the sub form will automatically show you the matching courses.
student (id, firstname, lastname, dob, etc)
activity (id, activityname, coursdeduration, etc)
activitymember (studentid, activityid)
-------------------
so now when you pull up a student you need to store information about the activities he does in the activitymember table
for this you need the dbs forms set up in a certain way
ie you need a main form, showing the student
and a sub form showing the activities for that student. (ie based on the activivitymember course)
[there sre other methods but this will show you some techniques]
access can automatically link these two together so as you step through the students all their activities are brought up
------------------------
you can then use a similar technique to scroll through courses, and identify all the members of those courses.
----------------------------
try this - create a main form for the students - you probably already have one.
create another form, based on a query that links activity table AND the activitymembers, so you see the studentid and the activityID AND activityNAME in your query (because an id only generally isnt that useful)
now create a sub form in the main form - you can do this by dragging the second form into the detail section of the first form, and moving the controls around, to get apleasnat layout
if Access doesn't set the link automatically, do it yourself - in the mainform, select the new subform, and in properties select the link:
studentid in master form, and studentid in child form
------------------
now as you step through the students in the main form, the sub form will automatically show you the matching courses.
