Tables and all that.

[DavidM]

Hi.

I compile the list of music played at a group.

I am just starting in Access 2003 after using a Sinclair
QL and Psions Archive for many years, so please bear with
me.

I have one database with 2 tables - Composers and Music.

The Key is Composer/Composer, which consists of the
composers surname, and initials when there is more than
one composer with that surname, e.g. Bach, C.P.E; J.C and
J.S. to name but 3 of several.

In Music I have Composer,(which is the same as
Composer/composer) (plus title, opus and date played).

In Composer, the fields relevant to this enquiry are
composer, surname and forenames.

What I want is for Composer/Surname to default to be the
same as Composer/composer, which it is for 95% of the
time - the other 5% I would just delete the initials.

Can this be done, and what do I show as the default value
in composer/surname?

I hope this makes sense, and that someone can enlighten
me. It seems I am not even a 'Dummy' as I've failed to
understand 'Access 2003 for Dummies'

Ta muchly.

 

[Allen Browne]

Assuming that any piece of music has only one composer, you need these
tables to create your library of the pieces your group can play:

Composer table: one record for each composer
-----------------------
ComposerID AutoNumber primary key
Surname Text
Initials Text
BirthDate Date/Time
DeathDate Date/Time
Comments Memo

Music table: one record for each piece of music
---------------
MusicID AutoNumber primary key
MusicTitle Text title of this piece of music.
MainKey Text home key (e.g. C#)
ComposerID Number F.k. (foreign key) to Composer.ComposerID
ComposeDate Date/Time date finished.

Now go to the Relationships window (Tools menu), add both tables to the
window, and drag Music.ComposerID onto Composer.ComposerID. Access pops up a
dialog to create this relationship. Check the box for Referential Integrity,
and then click Ok.

You also want to record what you played, when, and form whom. One client can
hire you for many concerts. One concert contains many pieces of music. You
therefore need 3 more tables like this:

Client table: one record for each person/company that hires you.
---------------
ClientID AutoNumber primary key
ClientName Text
Phone Text
....

Concert table: one record for booking to play.
------------------
ConcertID AutoNumber primary key
ConcertDate Date/Time date and time of booking
ConvertVenue Text location
ClientID Text who hired you. F.k. to Client.ClientID
Comments Memo

ConcertMusic table: one record for each piece played in a concert.
-------------------
ConcertMusicID AutoNumber primary key
ConvertID Number Which concert. F.k. to Concert.ConcertID
MusicID Number Which work. F.k. to Music.MusicID
SortOrder Number 1 for 1st piece, 2 for 2nd, ...

That's the basics. You may need a more complex structure if you want to
track multiple composers for any one piece of music (or composers and
arrangers), or which members of your group actually played in any given
convert (or possibly even in any given piece of music played at a convert),
and so on.