Manually Change 1 to Many or 1 to 1

[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

 

[Ken Snell [MVP]]

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)

 

[Guest]

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

 

[Guest]

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

 

[Chris2]

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

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.

 

[Chris2]

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

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.

 

[Jamie Collins]

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

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

Jamie.

--

 

[Guest]

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 Nurick]

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

Does SQL underlie A5?

 

[Guest]

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

 

[Chris2]

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

Jamie.

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.

 

[Jamie Collins]

Does SQL underlie A5?

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.

--

 

[Jamie Collins]

My apologies, but as MS Access is relationally-based

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

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

It underlies the execution of every Query to
retrieve, update, and append data; to create and modify Tables, etc.

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.

--

 

[Chris2]

Does SQL underlie MS Access/Jet? Doubtful.

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,

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