relating tables

[dcs68]

Hi,

I have a database that has 4 tables. The first is products (cd's) in which
there is only one instance of a product. The second is a list of all the
songs on the cd. The third is a list of the writers of those songs and the
fourth is a list of the % share for each of the writers. So for example, a cd
may have 10 songs, 5 of those 10 songs were written by a single writre so
that their share is 100%, but the remaing 5 were co-written by 2 writers each
so table 4 shows that those songs had 2 writers each with 50%.

Table 1 (cd's) relates to table 2 (songs) in a one to many relationship,
table 2 relates to table 3 and table three relates to table 4.

I have done a form with sub forms so that you see all cd's, the subform
shows all songs on that cd, then you drill down to the writers for those
songs and drill down again to the % share for each writer

.....my question is, how do I ensure that there is integrity between all the
records and is it ok to use the one form with subforms nerface to edit and
add all data?

Many thanks,

Derek

 

[Roger Carlson]

You set your Relational Integrity in the Relationship window when you
establish your Relationships.

It is certainly OK to use a form/subform interface to add your data to the
tables. On my website (www.rogersaccesslibrary.com), is a small Access
database sample called "Subform3Levels.mdb" which illustrates one way to do
this.

But I would also mention that there is a Many-to-Many relationship between
Songs and Writers. Each Song can have One or More Writers and Each Writer
can write One or More Songs. Therefore, I'd create a "Linking" table
between the two and relate that table to Table 4.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[dcs68]

Thanks Roger!

You set your Relational Integrity in the Relationship window when you
establish your Relationships.

It is certainly OK to use a form/subform interface to add your data to the
tables. On my website (www.rogersaccesslibrary.com), is a small Access
database sample called "Subform3Levels.mdb" which illustrates one way to do
this.

But I would also mention that there is a Many-to-Many relationship between
Songs and Writers. Each Song can have One or More Writers and Each Writer
can write One or More Songs. Therefore, I'd create a "Linking" table
between the two and relate that table to Table 4.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L