Manually Change 1 to Many or 1 to 1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

I'm not new to database design, however, I am inexperienced with Access. I'm
trying to build the classic Many to Many application for Students and
Classes. So far I've developed the following tables, using tblLink to
establish the kind of relationship we need for a Many to Many scenario.

tblStudents
tblClasses
tblLink [StudentID and ClassID] + Grade
tblInstructors
tblCourses

I'm using the Relationship builder, and for the life of me, I cannot figure
out how to manually establish relationships. I need several One to Many, but
I also need some One to One relationships. Is there a way to tell Access
which type I want? Or does Access always determine the type of relationship?
Am I missing the obvious?

By the way, since this is such a common design, there must be several
examples of the proper design for this application. I would appreciate it if
someone could point send me some links.

Thanks,
Robert
 
You establish a relationship by dragging a field from one table on top of a
field in another table. (If you want a multifield relationship, use
Ctrl+Click to highlight all the desired fields in one table, then drag the
selection onto the second table.)

After you "drop" the field on the second table, a relationship window will
display so that you can make changes and set options. ACCESS will "identify"
the relationship based on whether one or both fields are the primary key in
their respective tables.

Example: TableA has FieldA. TableB has FieldB.

Scenario 1:
FieldA is PK in TableA. FieldB is not a PK in TableB. ACCESS identifies
the relationship as 1 (TableA) to Many (TableB).

Scenario 2:
FieldA is PK in TableA. FieldB is PK in TableB. ACCESS identifies the
relationship as 1 (TableA) to 1 (TableB).

Scenario 3:
FieldA is not a PK in TableA. FieldB is not a PK in TableB. ACCESS
identifies the relationship as a simple join.


Not sure what you're seeking re: design for a junction table for Students
and Classes, but this is a generic representation:


tblStudents
StudentID (PK)

tblClasses
ClassID (PK)

tblStudentClass
StudentID (CPK)
ClassID (CPK)
 
Hi Chris:

[There is no way to *specify* to MS Access the "cardinality" of the
relationship. It is dictated by the index found on the column ]

Thanks for the explanation, I was wondering if I were missing something. I'm
a long time user of a program called A5 where establishing the type of
relationship is done by selecting from a drop down list of 1 to Many or 1 to
1. I kept looking for that in Access and never could find it.

Robert T
 
Hi Ken:

Thanks for the in depth example of setting up a Students-Classes database.
I'm sure that will go a long way towards helping me create this kind of
application in Access.

Robert T
 
Robert T said:
Hi:

I'm not new to database design, however, I am inexperienced with Access. I'm
trying to build the classic Many to Many application for Students and
Classes. So far I've developed the following tables, using tblLink to
establish the kind of relationship we need for a Many to Many scenario.

tblStudents
tblClasses
tblLink [StudentID and ClassID] + Grade
tblInstructors
tblCourses

I'm using the Relationship builder, and for the life of me, I cannot figure
out how to manually establish relationships. I need several One to Many, but
I also need some One to One relationships. Is there a way to tell Access
which type I want? Or does Access always determine the type of relationship?
Am I missing the obvious?

By the way, since this is such a common design, there must be several
examples of the proper design for this application. I would appreciate it if
someone could point send me some links.

Thanks,
Robert
Click to expand...

Robert,

There is no way to *specify* to MS Access the "cardinality" of the
relationship.

It is dictated by the index found on the column (or combination of
columns) when the "Relationship" is established (in the Relationships
Window or in a CREATE TABLE statement).

Table1 Table2 Table3
Index1 Index1
Index2 Index1
1 to 1 Unique Unique
1 to M Unique Non-Unique
M to M Unique Non-Unique
Non-Unique Unique


Sincerely,

Chris O.
 
Robert T said:
Hi Chris:

[There is no way to *specify* to MS Access the "cardinality" of the
relationship. It is dictated by the index found on the column ]

Thanks for the explanation, I was wondering if I were missing something. I'm
a long time user of a program called A5 where establishing the type of
relationship is done by selecting from a drop down list of 1 to Many or 1 to
1. I kept looking for that in Access and never could find it.

Robert T
Click to expand...

I went off and looked at A5 a few days ago (you may have even been the
person who mentioned it around here).

Each product is a different in the amount of "extras" it provides,
some areas can be radically more different than others.


Sincerely,

Chris O.
 
Chris2 said:
Each product is a different in the amount of "extras" it provides,
some areas can be radically more different than others.
Click to expand...

Take a look at the underlying SQL DDL - it's a great leveller.

Jamie.

--
 
Hi Chris:

Every product has it's strengths and weak points. A5 is much more user
friendly than Access and it's programming language, Xbasic, is far more
intuitive than VBasic. It also offers additional features Access can't
compete with yet, such as more flexible and powerful Field Rules [and several
others].

In my opinion, Access makes much better use of Queries, it's obviously
extremely popular. and it has tons of support in the Windows community. In
additiion, one can find many different books on using Access.

Right now my concern is learning how to use Access. It's never easy
transitioning from one product I've used for years to another one that is
very similar, but so different in terms of how the same goals are
accomplished.

So far I really like this forum, the people here are smart, courteous, and
friendly.

Robert
 
John:

For the current version, the answer is no. However, their next release
[Client-Server version] will be a front end for any back end database such as
SQL Server, MYSQL, Oracle, etc. I know they have a deal in place to become
the front end for MySQL and there may be others to follow.

When will that version ship? I don't know but it could be later May or June,
but that's only a guess.

Robert
 
Jamie Collins said:
Take a look at the underlying SQL DDL - it's a great leveller.

Jamie.
Click to expand...

Jamie,

I was thinking of GUI add-ons.

For Access, if all the tables are created via the GUI, there is no
"underlying" SQL DDL. The GUI should really "build" a DDL statement
that same way the QBE grids builds an SQL DML statement (too bad it
doesn't).


Sincerely,

Chris O.
 
John said:
Does SQL underlie A5?
Click to expand...

Does SQL underlie MS Access/Jet? Doubtful. AFAIK SQL (which interests
me) is a high level language which is parsed and converted to something
low level and vendor specific (which does not interest me). A5 may lack
SQL gateway and MS Access may have no tool for throwing out SQL DDL,
however CREATE TABLE DDL perhaps remains the best way of expressing
table schema (Entity Relationship Diagrams are tricky for newsgroup
posts <g>).

Jamie.

--
 
Chris2 said:
My apologies, but as MS Access is relationally-based
Click to expand...

Actually, it is file based, more ISAM than RDBMS. The Jet engine
provides a relational view of the underlying file structure, records,
pages, etc.
SQL is the
beating heart of the software
Click to expand...

SQL is for the convenience of humans, albeit SQL-knowledgeable humans
(as distinct from GUI-only humans <g>). The 'beating heart' of Jet is a
DLL: it may have a SQL gateway but it doesn't use SQL code internally.
Take SQL away and we'd all have to use some obscure product-dependent
machine code (and no said:
It underlies the execution of every Query to
retrieve, update, and append data; to create and modify Tables, etc.
Click to expand...

Not really. SQL code underlies MS Access's Query Builder, if that's
what you mean. SQL is essential limited to the interface layer. Did you
ever wonder what the SQL parser is for?

Jamie.

--
 
Jamie Collins said:
Does SQL underlie MS Access/Jet? Doubtful.
Click to expand...

My apologies, but as MS Access is relationally-based, SQL is the
beating heart of the software (although MS Access provides many
facilities to hide it). It underlies the execution of every Query to
retrieve, update, and append data; to create and modify Tables, etc.

AFAIK SQL (which interests
me) is a high level language which is parsed and converted to something
low level and vendor specific (which does not interest me). A5 may lack
SQL gateway and MS Access may have no tool for throwing out SQL DDL,
Click to expand...

If you threw out SQL, MS Access would stop working.
 
Back
Top