problem: cornered by forms!

[backBack]

Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical pieces on
the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship one to
many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1, or 2,
or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need composerID. If
a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all at the
same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...

 

[Scott McDaniel]

tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this table; lngJoinID would be
an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each piece can be included on
as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing that information, and
store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.

 

[backBack]

Thanks, Scott,

I'll test your procedures and come back to you!

Still, if other guys have another suggestions about that
form problem, they are very much welcome to send a
message.

Thanks again!

-----Original Message-----
tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this table; lngJoinID would be
an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each piece can be included on
as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing that information, and
store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical pieces on
the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship one to
many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1, or 2,
or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need composerID. If
a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all at the
same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...

.

 

[Greg Kraushaar]

At the level of detail you give, your design looks much better than
most beginners. The problem is likely to be in the detail

tblPeriod
ID Autonumber PK
Description Text
....(Other Fields e.g. Dates)

tblCD
ID AutoNumber PK
Title Text
....(Other Fields e.g. CatNo etc)

(I am assuming each composer can only be in one period)
tblComposer
ID Autonumber PK
PeriodID. LongInteger. Indexed Dupes Ok. No Nulls (FK for short)
...Other Stuff

tblComposition
ID AutoNumber PK
ComposerID FK (Only one composer per piece - ie no Gershwin or G&S)
... OtherStuff

tblTrack (This is your Joining Table)
ID Autonumber PK
CDID FK
ComposerID FK
TrackNumber Assumes one Track per comp & CD - not valid I know
Otherwise, yet another table

Now define your relationships
Specify a one to many enforced relationship from each PK to the
appropriate FK

Define a simple form to enter periods & enter data

Define a simple form to Enter Composer. Use a list or combo for the
Period. Enter Data

Now you need to make choices
do you want to see all your CDs, and the Tracks on one selected CD
This will lead to a Subform - Subform design
This is the hardest to implement

Do you want to see one CD and all the tracks on it
This will lead to a MainForm-SubForm design

Do you want to see OneComposition and all the CDs its on?
Another Main-SubForm

Do you just want to see Your CD titles
A simple Form

Do you want to see a Period and all the CDs that contain music from
that period
A Main-SubForm


Now I will go into the 1Cd All Tracks option

Create a query (qselTrackDetails) that joins tblTrack and
tblComposition
Include tblTrack.CDID and any fields you want to see from either
table.


Now create a continuous form (frmCDSubTrackDetails) using
qselTrackDetails as its recordsource . DONOT enter data yet.

Now create a single record form (frmCDMain) that uses tblCD as its
recordsource

Drag frmCDSubTrackDetails from the database window onto frmCDMain(Open
in design Mode This will create a Subform
Confirm that the Subform properties are set properly
LinkChidlFields Set to tblTrack.CDID
LinkMasterFields Set to tblCD.ID
If they are not already set (Access is pretty smart about this stuff)
you can use the builder to define them, or just type the field names.

Now -At Last- you can open the CD form, and enter data for the CD and
its tracks

NB all the IDs are for the computer's use, not yours. You should never
need to show these on any of your forms or reports.

Also, you may want to add the compositions separately. This way you
can just set up a combo box to select the compostion, rather than type
it all over again

 

[backBack]

Hi!

At first sight (that is, before testing them) you
procedures look OK except for one (important) thing
however, is when you say "[...] ... but you can only add
one piece by a specific composer to a specific album".
That situation of many pieces of a specific composer in a
specific album is very, very frequent and definitely that
problem has to be adressed! Any (other)suggestions about
that frustrating hitch??

Thanks!

-----Original Message-----
tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this table; lngJoinID would be
an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each piece can be included on
as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing that information, and
store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical pieces on
the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship one to
many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1, or 2,
or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need composerID. If
a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all at the
same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...

.

 

[backBack]

Thank you, Greg, for your very detailed suggestions
and... for the good marks you are giving for the beginner
I am.

I'll test and come back to you!

Thanks again!

 

[Roger Carlson]

Creating a form for a many-to-many relationship can be tricky. On my
website (see sig below) there is a small sample database called
"ImplementingM2MRelationship.mdb" which illustrates how to do this.

HTH

--Roger Carlson
www.rogersaccesslibrary.com

 

[Scott McDaniel]

Sorry ... that should be "you can only add a specific piece by a specific
composer to a particular album ONCE" ... you can add as many pieces as you
want to an album, by any compser

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Hi!

At first sight (that is, before testing them) you
procedures look OK except for one (important) thing
however, is when you say "[...] ... but you can only add
one piece by a specific composer to a specific album".
That situation of many pieces of a specific composer in a
specific album is very, very frequent and definitely that
problem has to be adressed! Any (other)suggestions about
that frustrating hitch??

Thanks!

-----Original Message-----
tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this table; lngJoinID would be
an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each piece can be included on
as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing that information, and
store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical pieces on
the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship one to
many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1, or 2,
or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need composerID. If
a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all at the
same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...

.

 

[Scott McDaniel]

The problem is not in your form, it's in your table design. Once you get
your tables straightened out, you will more easily see how to build your
forms to allow for proper data entry. For example, in the example I give,
your "main" table would be tblJoin ... is you base a form on tblJoin and use
the OTHER tables to "feed" tblJoin, you will be able to add as many pieces
as your want to as many albums as you want, for as many composers as you
want.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Thanks, Scott,

I'll test your procedures and come back to you!

Still, if other guys have another suggestions about that
form problem, they are very much welcome to send a
message.

Thanks again!

-----Original Message-----
tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this table; lngJoinID would be
an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each piece can be included on
as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing that information, and
store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical pieces on
the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship one to
many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1, or 2,
or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need composerID. If
a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all at the
same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...

.

 

[backBack]

Thanks, Roger, I'm going to look at your www.

-----Original Message-----
Creating a form for a many-to-many relationship can be tricky. On my
website (see sig below) there is a small sample database called
"ImplementingM2MRelationship.mdb" which illustrates how to do this.

HTH

--Roger Carlson
www.rogersaccesslibrary.com




.

 

[backBack]

Hi!

You mean: due to the "indexed, no duplicates" propriety
attached to composer, album and piece, I will get only
ONE entry for a specific piece (in a specific album by a
specifice composer), am I correct?

-----Original Message-----
Sorry ... that should be "you can only add a specific piece by a specific
composer to a particular album ONCE" ... you can add as many pieces as you
want to an album, by any compser

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Hi!

At first sight (that is, before testing them) you
procedures look OK except for one (important) thing
however, is when you say "[...] ... but you can only add
one piece by a specific composer to a specific album".
That situation of many pieces of a specific composer in a
specific album is very, very frequent and definitely that
problem has to be adressed! Any (other)suggestions about
that frustrating hitch??

Thanks!

-----Original Message-----
tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this

table;
lngJoinID would be

an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each

piece
can be included on

as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing

that
information, and

store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net


Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical

pieces
on

the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship

one
to

many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1,

or
2,

or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need

composerID.
If

a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all

at
the

same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...


.

.

 

[backBack]

That makes sense! I'll test anything that makes sense!

Thanks!

-----Original Message-----
The problem is not in your form, it's in your table design. Once you get
your tables straightened out, you will more easily see how to build your
forms to allow for proper data entry. For example, in the example I give,
your "main" table would be tblJoin ... is you base a form on tblJoin and use
the OTHER tables to "feed" tblJoin, you will be able to add as many pieces
as your want to as many albums as you want, for as many composers as you
want.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Thanks, Scott,

I'll test your procedures and come back to you!

Still, if other guys have another suggestions about that
form problem, they are very much welcome to send a
message.

Thanks again!

-----Original Message-----
tblAlbum
=========
lngAlbumID [PK]
strAlbumName
etc

tblPiece
=========
lngPieceID [PK]
strPieceName
etc etc

tblComposer
=========
lngComposerID [PK]
lngPeriodID [link to tblPeriod]
strComposerName
etc etc

tblPeriod
==========
lngPeriodID [PK]
strPeriod

tblJoins
========
lngJoinID
lngAlbumID [link to tblAlbum]
lngComposerID [link to tblComposer]
lngieceID [link to tblPiece]
Note: the three fields above would be PK for this

table;
lngJoinID would be

an Autonumber and would be the surrogate key

Build a form based on tblJoins ... use combo boxes for the three PK fields,
and base those comboboxes on the appropriate table. Therefore, an album can
be composed of as many pieces as needed, and each

piece
can be included on

as many albums as desired. Additionally, any piece can be associated with
any composer ... but you can only add one piece by a specific composer to a
specific album.

Assuming that Instruments are associated with a particular piece on a
particular album, you'll need another table housing

that
information, and

store the PK of tblJoins in this table:

tblInstruments
==========
lngInstrumentID [PK]
lngJoinID [link to tblJoins]
lngInstrumentType [link to tblInstrumentTypes]
etc etc

tblInstrumentTypes
=============
lngInstrumentTypeID [PK]
strInstrument

With this table, you could add many instruments for a particular piece on a
particular album by a particular composer.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net


Hi every one!

I sincerely hope that you will help me because I'm
cornered by the following problem, which I did not manage
to solve by meyself despite many, many hours of
analyzing, retrying, etc.

I'm trying to create a db to make an inventory of my
collection of classical music. So I have created 5
tables:

1. "album": it gives the title of an album or a CD),
2. "piece": it gives the titles of the musical

pieces
on

the CD,
3. "composer": it give information about composer
4. a "junction" table between album and pieces, presuming
that one album may contain many pieces, and that one
piece may be in many albums.
5. "period": it gives the period of the author (ex.
baroque, modern, etc.)

I'm planning to add other tables (orchestra, instruments,
performrs, etc.) when and if my probles is solved.

Relationships:

- album is linked to piece by a junction table.
- period is linked to composer with a relationship

one
to

many
- composer, in addition to store the secondary key of
period, has a PK linked to piece.

Problem: I am completely unable to create forms (1,

or
2,

or 3) to enter information in album, or piece, or
composer without getting a bip! If I use a form
album+piece, Access complains that it need

composerID.
If

a use a form composer+piece, I also get a bip! As if
composerID and piecesID and albumID are needed all

at
the

same time, together.

Where is the problem? Bad construction of my tables? Of
my relationship? Or what?

I have spent so many hours on that problem, I have
examined and analysed so much every thing, I have started
over so many times...

Please help. I cannot go any further in my training of
Access before this problem's soved!


Thanking you in advance...


.

.