problem in tables relationships

[shery123]

hi all,
i intend to design a institute management database.therefore i have 4 tables
1)institutes table (contains the names of all of institutes )
ID autonumber , Institute_Name
2)courses table (contains all of the courses existed in every institute)
ID autonumber , Course_name , INS_ID "to connect to institutes table"
3)students table (contains the names of students in each course in institute
ID autonumber, Student_name, CRS_ID "to connect to courses table"
4)subjects table (contains the subjects included in every course)
ID autonumber, Subject_name, CRS_ID " to connect to course table "
---------------------------
provided that every student can not be registered in more than one course and can not be in more than one institute each time.
---------------------------------------------
the problem is that , when making a subform i can not enter nor update data also ms access made student name and subject name fields in same table but in real they are two different tables.
please help

 

[plog]

provided that every student can not be registered in more than one course and can not be in more than one institute each time.

Is that true? A student can only ever take one course? That smells wrong.

the problem is that , when making a subform i can not enter nor update data

This sounds like you have a form based on a query. You shouldn't do that. Any time you need a form to act on data, it should be based on a table, not a query.

also ms access made student name and subject name fields in same table but in real they are two different tables.

Access doesn't make tables, you do. And you explicitly said you made it so that those 2 fields were in different tables. This requires more explanation because what you described isn't accurate.

 

[Jarod]

This sounds like you have a form based on a query. You shouldn't do that. Any time you need a form to act on data, it should be based on a table, not a query.

OMG why? I did that in my DB

 

[plog]

It just makes things simpler. Often queries are not updateable so that's a non-starter right there--which was the original poster encountered. Then people don't understand their relationships properly and the data gets inserted in unexpected ways (blank data in some fields, multiple records in some tables, without foreign keys, etc.).

For forms where users edit/add/delete data: Use only one table as its record source.

 

[Jarod]

So, exception being when I need calculated fields, or requiring multiple tables?

 

[plog]

No exceptions to this statement:

For forms where users edit/add/delete data: Use only one table as its record source.

 

[Jarod]

I'm confused now, what are the uses of queries?

I'm taking inspiration from this ms-access2010.com/tutorials/forms2.html to record my sales. Queries are used.

I don't want to redo my database

 

[plog]

Queries are used for reports and forms that don't allow interaction with data (menus, search pages, etc.)

 

[Jarod]

Would that explain the troubles I have with all the refresh and requeries I need in my db? My forms not updating my tables until closed?

 

[plog]

I don't exactly understand what method you are employing. On a form, if you have an input for First Name and you change it from Sally to Steve, it should immediately change in the underlying table without any further action.

 

[Jarod]

In some subforms I noticed the tables are not updated until the form is closed. But anyway, I'll clean my db of unnecessary queries and see after that. Thanks alot for this vital piece of info. But not for the added hours of work.

 

[Jarod]

Going through the cleaning, access makes much more sense now. Imagine, everyting was going through queries. Somehow learning access, I read somewhere and it got stuck in my mind that it HAD to be like that.

Thanks again.