complicated relationships

  • Thread starter Thread starter Holton
  • Start date Start date
H

Holton

I'm not sur eif this can be discussed here due to it's
complications, but:

I'm trying to design a database that has very specific
requiremnets. Basically I've got a form to enter artist
information. Each artist can have performed in multiple
operas and each of the operas can have had multiple
different artists playing the same roles. So when I'm in
the main artist form, using a command buttom, open a form
that shows JUST that artists operas and roles that he/she
has perfomed in, AND, and here's the tricky part, add to
that list from that form. You see my problem. I can't add
records to a form based on a query and I can't add the
artistID to either the opera or role tables, cause each
role or opera can have multiple artist. Maybe this whole
thing is over my head and I should give up, or I may need
to delve into VB code, which I'm not that familiar with.
Should I pass this assignment off to a professional, or
is there a simple solution I'm not getting. Help.
 
Hi

Off the top of my head, here's a summary for you to start with. I hope
others will chime in with more ideas and critiques...

From your description, I would start with 3 basic tables and some link
tables. The basic tables would be tblArtist, tblOpera, tblRole.

We know that there is a many-to-many relationship between tblArtist and
tblOpera, so I would add a link table tblArtistOpera. This table contains
instances of a particular artist performing in a particular opera. From your
description an artist performing in a particular opera can have multiple
roles, so there's *at least* a one-to-many relationship between
tblArtistOpera and tblRoles. (note: that I mentioned *at least* since this
relationship can be a many-to-many. If a particular role in a particular
opera can be played by multiple artists, then there's a many-to-many
relationship between tblArtistOpera and tblRole).

I know this is more conceptual an answer than a detail one, but you should
get more comments and critiques from others before proceeding.


HTH,
Immanuel Sibero
 
I agree with the previous message. I would like to add
that the query for your form does not need to contain all
of the information that you want to display. It should
only contain the 1:1 info. For items that would be
1:many, you should insert subforms.

For example, maybe your form record source would only
contain fields that are 1:1 for each artist (tblArtist).
The lookup tables for Operas and roles would not be a
part of the forms record source.

But, there would be a table that contains links of
ArtistID, OperaID, and RoleID (keyed on the combination
of the three). A subform would use a query joining the
link table and the associated lookup data. The link
between the main form and the child form would be the
artist ID, so only the records from the link table that
match the current artist would show.

So, to add a new record, you would go into the subform
and just select an Opera and Role. The artist name would
be added automatically for you. Of course it is very
important to key the tables properly and define the
relationships in the relationships window. Also, if you
define the lookup fields in the join table, the combo box
settings will pass through to your forms, so you may want
to do that in the beginning.

You could use the same idea, but have other forms that
use the Opera or Role as the primary data source, and
display lists of the matches.

Hope this helps.

-Ted

To go a little further
 
Back
Top