Many-to-Many Relationships

[GG Castillo]

Hi, I can't seem to find the info I'm hunting for in the beginners thread for
IMPLEMENTING MTM relationships once the tables have been made and the
relationships have been created. Where do I go from there .. have rented too
many books, none address many-to-many implementation. So far, all have only
described how to set the relationships up with the two tables and the
junction table. Has anyone read a book that goes into great detail on the
end use in forms, record, queries, etc after the relationships are created???

One example of mine (and there are MANY of these types of relationships in
my database) is this:

My database is for keeping all of my literature, supplies and equipment
organized.
Literature comes in many forms in the crafting industry ... from books, to
magazines to single patterns and lots more in between.

One Publisher has many imprints
One imprint publishes many books
One book has many patterns and instructions
One pattern can be found in more than one book and one instruction can be
found in many books
One magazine has many patterns ... same as for books above
Now, one pattern can also have more than one designer and one designer can,
of course have many patterns credited to his/her name; however one book may
have an author or two, but the designers used for each pattern in the book
are usually different than the author(s) who wrote the book. This continues
with the many to many relationships and the patterns: MTM patterns to craft
types, MTM patterns to supplies; MTM patterns to equipment; and finally MTM
Patterns to Basic Instructions. This said, I'm pretty sure I have all the
tables I need set up and all the relationships set up correctly. What I
don't know how to do or where to begin is creating the forms that will allow
for easy entry of huge collection of literature, supplies and equipment.

I've created forms individually, with sub-forms for the one-to-many
relationships with no problem, but when it comes to doing it for the
many-to-many I don't know what to do. Everything I've tried thus far doesn't
seem to work. Shoot, I've even tried to give up and just buy a book database
program, but no way do they even compare to the information I want to be able
to access. Patterns are only vaguely similar to chapters in a book, and even
then book databases that I've found don't address chapters at all ... only
the basic Publisher, Title, Purchase Date, stuff.

I need to be able to find an individual pattern by supplies used and/or
craft type etc., what book(s) it's in or if it's a single pattern cut from a
long lost magazine and now stored in a binder in a sheet protector and where
the binder or book is located.
Then I need to know what supplies or equipment I already have and what I
will need to complete the project.

So, I'm assuming this will need to be done in reports or queries. Once I
get the forms down, I'll ask about how to create reports that will print out
only the pattern name, description and supply list on a 4x6 or 3x5 card to
take to the local supply house rather than cart a bunch of books and patterns
with me. Kind of like a recipe card with the ingredients but not the
instructions.

I apologoze for such a long explanation/description, but after reading
through almost every beginner post in hopes of the same need, I see that most
quetioners were asked for more information, so I hope I have satisfied this.

 

[Ken Sheridan]

So as not to complicate matters I'll just deal with one relationship type,
that between Books and Patters which would be modelled with tables like this:

Books----<BookPatterns>----Patterns

where BookPatterns is the table modelling the many-to-many relationship type
between the Books and Patterns entity types (the so-called junction table),
having columns BookID and PatternID referencing the keys of the other two
tables respectively. To represent this in a form you could have a form based
on the Books table (in reality you'd use a sorted query based ion the table
as the RecordSource). In that form you'd have a subform based on the
BookPatterns table linked to the parent form on BookID.

The subform would most probably be in continuous form view and would have
just one control, a combo box bound to the PaternID column in BookTables,
with a RowSource property of:

SELECT PatternID, Pattern
FROM Patterns
ORDER BY Pattern;

Other properties of the combo box would be as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero to hide the first (PatternID) column.

To record patterns for the current book its simply a case of entering as
many rows as necessary in the subform, selecting a different pattern from the
list in each case.

You then have the problem of what to do if a pattern is not yet in the
Patterns table, and therefore not in the combo box's list. This is handled
by means of the combo box's NotInList event procedure, which fires if you
type a value into the combo box which is not in the list. The code in the
NotInList event procedure would open another form bound to the Patterns table
and pass the value you entered into the combo box to this form so that you
can then enter data into other fields in the Patterns table for this new
pattern The code would go like this:

Private Sub cboPatterns_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of patterns?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmPatterns", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmPatterns closed
DoCmd.Close acForm, "frmPatterns"
' ensure pattern has been added
If Not IsNull(DLookup("PatternID", "Patterns", "Pattern = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Patterns table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In the frmPatterns Open event procedure you'd put the following code, which
sets the DefaultValue property of the control bound to the Pattern field to
the value you entered in the combo box in the subform:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.Pattern.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Because frmPatterns is opened in dialogue mode, using the acDialog setting
of the OpenForm method, the calling code will pause until it is closed (or
hidden) so its possible to then confirm that the new record was added and add
it to the combo box's list by setting the return value of the Response
argument to acDataErrAdded.

Note that I've not included any error handling in the code at this stage,
but it would be desirable to do so once its been tested.

When it comes to retrieving the information on patterns etc you are quite
right that this would be done by means of queries, on which forms and/or
reports could be based. Creating these queries is not going to be a trivial
task, given the complexity of the logical model, but provided the latter has
been well thought out and accurately reflects the entity types and
relationship types in the reality, it should not be unduly difficult.

Ken Sheridan
Stafford, England

 

[GG Castillo]

Thanks for such a concise and rapid response. I'll try what you've said and
let everyone know how it worked. What are your favorite reference sources
(books) on implementing relationships?
--
GG Castillo
Cro-Quiltin'''' Designs

So as not to complicate matters I'll just deal with one relationship type,
that between Books and Patters which would be modelled with tables like this:

Books----<BookPatterns>----Patterns

where BookPatterns is the table modelling the many-to-many relationship type
between the Books and Patterns entity types (the so-called junction table),
having columns BookID and PatternID referencing the keys of the other two
tables respectively. To represent this in a form you could have a form based
on the Books table (in reality you'd use a sorted query based ion the table
as the RecordSource). In that form you'd have a subform based on the
BookPatterns table linked to the parent form on BookID.

The subform would most probably be in continuous form view and would have
just one control, a combo box bound to the PaternID column in BookTables,
with a RowSource property of:

SELECT PatternID, Pattern
FROM Patterns
ORDER BY Pattern;

Other properties of the combo box would be as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero to hide the first (PatternID) column.

To record patterns for the current book its simply a case of entering as
many rows as necessary in the subform, selecting a different pattern from the
list in each case.

You then have the problem of what to do if a pattern is not yet in the
Patterns table, and therefore not in the combo box's list. This is handled
by means of the combo box's NotInList event procedure, which fires if you
type a value into the combo box which is not in the list. The code in the
NotInList event procedure would open another form bound to the Patterns table
and pass the value you entered into the combo box to this form so that you
can then enter data into other fields in the Patterns table for this new
pattern The code would go like this:

Private Sub cboPatterns_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of patterns?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmPatterns", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmPatterns closed
DoCmd.Close acForm, "frmPatterns"
' ensure pattern has been added
If Not IsNull(DLookup("PatternID", "Patterns", "Pattern = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Patterns table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In the frmPatterns Open event procedure you'd put the following code, which
sets the DefaultValue property of the control bound to the Pattern field to
the value you entered in the combo box in the subform:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.Pattern.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Because frmPatterns is opened in dialogue mode, using the acDialog setting
of the OpenForm method, the calling code will pause until it is closed (or
hidden) so its possible to then confirm that the new record was added and add
it to the combo box's list by setting the return value of the Response
argument to acDataErrAdded.

Note that I've not included any error handling in the code at this stage,
but it would be desirable to do so once its been tested.

When it comes to retrieving the information on patterns etc you are quite
right that this would be done by means of queries, on which forms and/or
reports could be based. Creating these queries is not going to be a trivial
task, given the complexity of the logical model, but provided the latter has
been well thought out and accurately reflects the entity types and
relationship types in the reality, it should not be unduly difficult.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Of the general purpose primers on Access I particularly like John L Viescas's
'Running Microsoft Access' (Microsoft Press).

For an introduction to VBA programming in Access Evan Callahan's 'Microsoft
Access/Visual Basic Step by Step' (Microsoft Press) is easy to follow and,
while not taking things to a very high level, provides a solid basis on which
to build.

At a more advanced level the 'Access Developer's Handbook' by Paul Litwin,
Ken Getz and Mike Guderloy (Sybex) covers the subject in great detail, and
contains a vast amount of useable code.

A useful and easy to read little book on the theoretical basis of the
database relational model is Mark Whitehorn and Bill Marklyn's 'Inside
Relational Databases With Examples in Access' (Springer).

For a highly authoritative but quite abstract explanation of the relational
model Chris Date's 'An Introduction to Database Systems' (Addison Wesley) has
for many years been regarded as a definitive work on the subject. Its by no
means an easy read, however.

For SQL Joe Celko's 'SQL for Smarties' (Morgan Kaufmann) is a wealth of
information on how to write queries. It deals with standard SQL, however,
and is not Access oriented. In fact Joe's views on Access do not bear
repetition where they might be read by people of a sensitive disposition<G>.
Even so it is worth its weight in gold.

Ken Sheridan
Stafford, England

Thanks for such a concise and rapid response. I'll try what you've said and
let everyone know how it worked. What are your favorite reference sources
(books) on implementing relationships?

 

[Guest]

Many-to-Many queries are not updatable in Access.
They are good for reports and views, but when you want
to add to or change data, you will have to update just
one table, or just one-to-many record.

I hope this knowledge saves you some work and agony :~)

(david)