Well, first things first I guess.
If this is something that needs to be completed in a short time frame, then
you might want to consider searching to see if someone has a pre-existing
music DB template that might work for you (perhaps with some slight
modification) or if there is an Access consultant in your area that may be
able to help.
On the other hand, if you have some time to develop it and you are the type
of person
(like myself) that enjoys a challenge, then welcome to the world of Access.
Be forewarned that Access has a steep learning curve. I first got into it a
few years ago when a relative was looking to create an inventory database for
their business. I suggested that Access might work well. They said "can you
do it?", to which I responded "Yea, I think so". Little did I know what I was
getting myself into. Luckily, they weren't in a big hurry.
You will probably want to get a book (maybe more than one) to help learn the
basics. Jeff Conrad has a list of good Access books on his website at;
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
There are also plenty of experts out there who are willing to share their
knowledge with the rest of us mere mortals on their websites. In addition to
the afformentioned Jeff Conrad, here are some other links that you may find
very helpful;
http://allenbrowne.com/tips.html
http://www.mvps.org/access/
http://www.databasedev.co.uk/
Then there are the discussion groups like this one. There are plenty of
people who are willing to spend some time to help you solve a problem (WHY we
do this, however, is somewhat of a mystery). I have probably learned as much
from this group as I have form any other source.
As far as your DB goes, from the list of fields you posted, it looks like
you will need several tables. Some of them will be relatively small, with a
limited amount of records, that will just be used to look up a value (a
*lookup* table). You also appear to have some One-to-Many and some
Many-to-Many relationships. I can't tell you *exactly* how to set them up
but here are my initial thoughts. You'll need to tweak things and use your
own field names, etc. (table names are preceded with tbl, field names are
listed under the asterisks).
tblAlbums
*******
AlbumID (Primary Key)
GenreID (Foreign Key to tblGenres)
ArtistID (FK to tblArtists)
RecordCompanyID (FK to tbl RecordCompanies)
AlbumTitle
ReleaseDate
(other fields related to the Album)
tblSongs
*******
SongID (PK)
SongTitle
DateRecorded
TrackLength
(other fields rlated to the song)
tblAlbumSongs
***********
AlbumSongID (PK)
AlbumID (FK to tblAlbums)
SongID (FK to tblSongs)
tblGenres
*******
GenreID (PK)
GenreName
(other fields related to Genre)
tblSubGenre
*********
SubGenreID (PK)
GenreID (FK to tblGenres)
(other fields related to SubGenre)
tblRecordCompanies
***************
RecordCompanyID (PK)
RecordCompanyName
StreetAddress
City
State
ZipCode
(other fields related to record company)
tblArtists
*******
ArtistID (PK)
ArtistName
(Etc.)
tblMusicians
*********
MusicianID (PK)
LastName
FirstName
(Etc.)
tblBandMembers
************
BandMemberID (PK)
ArtistID (FK to tblArtists)
MusicianID (FK to tblMusicians)
tblBandPositions
************
PositionID (PK)
PositionName
Let's analyze some aspects of this table structure. A band can have many
musicians. Likewise a musician can be in more than one band (ie John
Coltrane), so you have a Many-to-Many relationship between Bands and
Musicians. In this example tblArtists holds information about the Album
Artist, wether it's a band or a solo artist. tblMusicians holds information
about individual musicians and tblBandMembers is used to define the
relationship. Getting back to your example about John Coltrane. You're right
in that he can be an Album Artist or a band member, or he could be both at
the same time. For one of his solo albums, his name would appear in
tblArtists as the Album Artist. In this case his name is more of a *title* so
it would appear in a single field as "John Coltrane". Then, when selecting
the band members from tblMusicians he would also appear there (after all, he
is a member of his own band). In this case his name is a *name* so it is
divided into LastName and FirstName
Likewise with Albums/Songs. An album has many songs (obviously), but a song
can also be on more than one album (original album, greatest hits album,
etc.) so that
MtoM relationship is defined by tblAlbumSongs.
The last table (tblBandPositions) might be an example of a *lookup* table.
It would hold a limited number of records like "Lead Singer", "Drummer",
"Bass Player", etc. and you would just select values from this table when
needed.
I would strongly suggest that you lay this out on paper before you start
actually creating any new tables. This will help you decide which fields
belong where and the types of relationships you will have. Also, when you
think you are ready to start creating tables, I would start with a new blank
DB. Create some tables the way you think they should be, put in a little bit
of *test data*, then create a few forms and queries to test if you are able
to extract and display data the way you want to.
This post is getting a little long winded I suppose, but here is some final
food for thought.
In most cases you will probably want to use AutoNumber as your Primary Key.
Don't get hung up (as many people seem to) on wether the PK increments or
sorts the way you think it should. PK's are generally "not for human
consumption", as the experts like to say. They don't need to make sense to
you, they only need to make sense to Access.
When you are designing your tables, watch for repeating fields. For example,
in your current table you have Band Member 1, Band Member 2, Band Member 3,
etc. If you catch yourself doing this when you are designing your new tables
- STOP. That is a definite clue that you need to re-think it.
Also, don't include fields that would store calculated values. For example,
in your current table you have a field for Number Of Tracks. This is a value
that could easily be calculated by having Access count the number of songs on
a particular album. When dealing with values like this, ask yourself if it is
something that can be calculated. If it is, then it should not be a field in
your table.
Don't work directly in the tables, except maybe to put in some test data at
the very beginning of the design process. Data input should be done with
forms. If you have experience with Excel (or another spreadsheet program),
don't make the mistake of thinking that Access is like a spreadsheet. While a
table may look similar to a spreadsheet at first, Access absolutely does NOT
work the way a spreadsheet does.
This is just my opinion, but when it comes to creating forms, I don't use
tables as the recordsource. I create a query based on the table, then use the
query as the recordsource of the form. I think queries are more flexible than
tables. It's easier to add criteria, sort records the way you want to, etc.
When you do get around to creating forms, don't worry too much about how the
form looks at first. Get all the necessary controls on the form and make sure
everything works the way you want it to, then start making it look how you
want. Trust me, it sucks to spend a lot of time making a form *look* just
right, only to find out that it doesn't *work* right.
Finally, if you get stuck on something specific, post a question in this
group and there should be someone who is willing to help. Just try to make
sure your questions are specific, clearly worded and you include as much info
as you can about your situation. You may notice, looking through some of the
posts, that alot of the questions that don't get answered are ones that don't
make any sense or ones that just say "my database isn't working, what's
wrong?" (or something similar).
Anyway, I hope this is helpful and good luck if you decide to jump into it.