cannot add teacher code

[Guest]

I have the following objects:

Table Name Field Names
Program Data Program Code(Prim. Key), Program Name

Class Data Class Code (Prim. Key), Class Name

Subjects Data Program Code, Subject Code, Quarter. There is no primary
key because students from different programs may take the same subject.

When I create a query and select the fields: Class Code, Subject Code and
Quarter, the query displays the correct records. No problem here.

Next: I add, to the query, the table [Instructor Data] with the fields:
Class code, Instructor Code, Subject Code and Quarter. No primary key (i have
a instructor data table with primary key: Instructor Code) and the query
returns no records because instructor codes have nto been entered for the
corresponding class code-subject code...

That is what I want to do, use a form that displays all the subjects per
class so the user can just enter the teacher codes without having to re-enter
the subject codes (that's what she does right now and it leads to a lot of
typos).

Is this because I have many-to-one-to-many relationships?

Any help will be appreciated.

Thanx {

 

[Jeff Boyce]

See comments in-line...

I have the following objects:

Table Name Field Names
Program Data Program Code(Prim. Key), Program Name

Class Data Class Code (Prim. Key), Class Name

Subjects Data Program Code, Subject Code, Quarter. There is no primary
key because students from different programs may take the same subject.

Nothing in this list of fields mentions students -- if this table is as
described, you've assembled information about what program is offering what
subject in what quarter ... nothing about classes, nothing about students.

When I create a query and select the fields: Class Code, Subject Code and
Quarter, the query displays the correct records. No problem here.

Next: I add, to the query, the table [Instructor Data] with the fields:
Class code, Instructor Code, Subject Code and Quarter. No primary key (i
have
a instructor data table with primary key: Instructor Code) and the query
returns no records because instructor codes have nto been entered for the
corresponding class code-subject code...

Is there a question here?

That is what I want to do, use a form that displays all the subjects per
class so the user can just enter the teacher codes without having to
re-enter
the subject codes (that's what she does right now and it leads to a lot of
typos).

Don't force the user to enter anything. If you already have a table that
lists valid values, use a combo box to display those, and let the user pick.

Is this because I have many-to-one-to-many relationships?

Without more description of tables and relationships, it's hard to tell.
You tell us, is there a one-to-many relationship between ?xxxx and ?yyyy in
your real world?

Any help will be appreciated.

Thanx {

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

See my comments...

See comments in-line...



Nothing in this list of fields mentions students -- if this table is as
described, you've assembled information about what program is offering what
subject in what quarter ... nothing about classes, nothing about students.

** Yes, i have a table with students enrolled in a class code adn a table
where students grades will be entered.

** OOps, sorry! I forgot to mention that the field Program Code (from the
table Program Data) is a field in the table Class Data

When I create a query and select the fields: Class Code, Subject Code and
Quarter, the query displays the correct records. No problem here.

Next: I add, to the query, the table [Instructor Data] with the fields:
Class code, Instructor Code, Subject Code and Quarter. No primary key (i
have
a instructor data table with primary key: Instructor Code) and the query
returns no records because instructor codes have nto been entered for the
corresponding class code-subject code...

Is there a question here?

** no question, just a description of the problem, question follows

Don't force the user to enter anything. If you already have a table that
lists valid values, use a combo box to display those, and let the user pick.

** that's my problem! How can i display the subject codes when there is no
teacher entered yet.

Without more description of tables and relationships, it's hard to tell.
You tell us, is there a one-to-many relationship between ?xxxx and ?yyyy in
your real world?

** Please don't be sarcastic... No need for it....
** I find a solution to my problem...
**Thank you for insight and comments...

 

[Jeff Boyce]

When I'm sarcastic, I'll make sure you know. My comment about the real
world relationship of the data is quite germane to answering your question.
How your specific situation regards the pieces and how mine might aren't
necessarily the same. So I asked to gain more insight before offering more
specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

See my comments...

See comments in-line...



Nothing in this list of fields mentions students -- if this table is as
described, you've assembled information about what program is offering
what
subject in what quarter ... nothing about classes, nothing about
students.

** Yes, i have a table with students enrolled in a class code adn a table
where students grades will be entered.

** OOps, sorry! I forgot to mention that the field Program Code (from the
table Program Data) is a field in the table Class Data

When I create a query and select the fields: Class Code, Subject Code
and
Quarter, the query displays the correct records. No problem here.

Next: I add, to the query, the table [Instructor Data] with the fields:
Class code, Instructor Code, Subject Code and Quarter. No primary key
(i
have
a instructor data table with primary key: Instructor Code) and the
query
returns no records because instructor codes have nto been entered for
the
corresponding class code-subject code...

Is there a question here?

** no question, just a description of the problem, question follows

Don't force the user to enter anything. If you already have a table that
lists valid values, use a combo box to display those, and let the user
pick.

** that's my problem! How can i display the subject codes when there is no
teacher entered yet.

Without more description of tables and relationships, it's hard to tell.
You tell us, is there a one-to-many relationship between ?xxxx and ?yyyy
in
your real world?

** Please don't be sarcastic... No need for it....
** I find a solution to my problem...
**Thank you for insight and comments...

Regards

Jeff Boyce
Microsoft Office/Access MVP