help with adding data to multiple tables (1 Viewer)

[yabai]

hi, i am new to access and struggling so any help would be great...

i have a db with 3 tables. students contains a Primary Key ('studentid') and then 'firstname' and 'lastname' fields. The two other tables (contactdetails, coursedetails) have 'studentid' as a Primary Key and then they each have other fields.

I set up relationships between the tables (1 to 1) but i would now like to create a form that will allow me to enter data to all three tables at the same time. (this would be ideal as it keeps the data normalised) if this isn't possible i would like to create a form that adds the 'studentid' record to each of the tables.

let me know if you need anymore details but if someone can point me in the right direction it would be cool.

cheers

 

[jeremie_ingram]

You have 1-1 relationships? If so then the data from those tables should be in one table. You would not break down tables that contain unique data about 1 record so there should not be any 1-1 relationships as there should also be no many-to-many.

Double check your table structure to see what your doing. You should be able to easily add the contact details into the students table. You will need a junction table to list out which students are in which classes. If you dont have that then you will have a many-to-many on students to courses relationship (many students in each class and one student in many classes).

I have assisted an individual with a very similiar scenario, so a search of these forums may very well get you exactly what you want.

 

[neileg]

StudentID should not be a primary key in your contactdetails and coursedetails tables. If it is held there at all, it will be a foreign key.

Do a search in these forums, there's plenty of stuff on the type of database you are setting up.

 

[yabai]

cool. i am looking through the past help now and will let you know how i get on.

thanks.

 

[raskew]

Hi -

Sounds as though what you're attempting is very similar to the StudentsAndClasses database template.

You might try setting this up then examine the tables and relationships.
To set it up, click on New Database then Databases than StudentsAndClasses.

HTH - Bob

 

[ScottGem]

One other point to add here. Normalization is the process of reducing or eliminating redundant data ina database. Therefore entering data into multiple tables at the same time does NOTHING to normalize the database. Records should only be added to related tables when you are going to add more than the FK field.

 

[yabai]

Can a very kind person take a look at this to check I am going in the right direction? I have had a look at ClassAssignments db but still find myself struggling...

I now have three tables.

Students
studentID (PK, number)
other fields for address, phone, etc
FirstSupervisorID
SecondSupervisorID

- - -

FirstSupervisor
FirstSupervisorID (PK, autonumber)
First Supervisor

- - -

SecondSupervisor
SecondSupervisorID (PK, autonumber)
Second Supervisor

I think I have that right as there are one-to-many relationships and it makes sense to do it that way as if a supervisor changes their name I just have to change it in one place. I have created the relationships based on the foreign keys in the Students table.

All I need to happen is have a form that displays the records from the Students table along with the name (not FirstSupervisorID) of the First Supervisor. I can't think how to do this in Access.

I would also like a form that has a combo box that draws in the names from the FirstSupervisor table. When you select a name it should display the results of all of the students who have that supervisor in the Students table. I thought I had this by setting up a query with a criteria of [Forms]![frmQuery]![FirstSupervisor] and then having a form try to connect to the query but I got lost.

Any help would be gratefully appreciated as it all seems harder than using mysql and something like php and it is driving me mad.

cheers

mark

 

[ScottGem]

No this is correct. You have created a repeating group. Anytime you have fields like Supv1, Supv2 indicates a repeating group. In such cases you need to use a sub or join table. Also you only need ONE table for all supervisors.

So you come out with something like this:
Students
studentID (PK, number)
other fields for address, phone, etc
- - -
Supervisor
SupervisorID (PK, autonumber)
SupervisorFirst
SupervisorLast
etc.
- - -
StudentSupv
StudentSupvID (PK Autonumber)
StudentID (FK)
SupervisorID (FK)

 

[Ayat]

From ScottGems suggestion, I have a question:

How would he then refer to supervisors as Supervisor1 and Supervisor2 (i.e. if each student had a primary and secondary Supervisor), how would that differentiation come out from the junction table? Would it be through another field (such as Supervisor_Label/Name) that needs to be introduced into the junction table?

In line with this query, could you also comment on the following? Say I have a COntacts table with the name of the person, address, position, department etc. In this organization, the people from this Contacts table act as Project Managers, Product Manager, General Managers etc on different projects on a changing basis. So, how should this scneario be modeled. I tried the following:


CONTACTS
-----------
Contact_id
Contact_name
Address
Dept
etc


PROJECTS
----------
Project_id
Proejct Name
Decription
Time
etc


Here are the junction tables I created:

PRODUCT MANAGER
-------------------
Product_Manager_id
Project_id
Contact_id
Product_Manager_Name


PROJECT_MANAGER
-------------------
Project_Manager_id
Project_id
Contact_id
Project_Manager_Name


GENERAL_MANAGER
-------------------
General_Manager_id
Project_id
Contact_id
General_Manager_Name


Does this look right? Or could this thing instead be done with 1 junction table such as

JUNCTION TABLE
----------------
Role_id
Role_Label
Project_id
Contact_id

Thank you.

 

[ScottGem]

Yes, if you need to identify the primary and secondary supervisor, then you add a field to identify the relation.

As to the other, I would go with the last example. You don't need the manager name, you get that from the ContactID (assuming they are all from a single table). So the best option would be a table to join the contact to the project and identify their role.

 

[Ayat]

thanks ScottGem.

A quick clarification. If I underatand you correctly, in my example, I understand you are saying that the better option is to use the 1 junction table:

JUNCTION TABLE
----------------
Role_id
Role_Label
Project_id
Contact_id

Is Role_id required here? If so, why? and if not, then will project_id and contact_id need to be defined as the joint primary key?

And is Role_Label the one you are mentioning as the field that will state whether the given COntact_id/Project_id combo is a Project or Product or General manager?

Thanks again

 

[yabai]

thank you very much scottgem. that is very helpful and i appreciate your help.

cheers
mark