Database of videos (1 Viewer)

Hello,

I'm tasked with making a database of an archive of videos.

Now here's the bit I'm having trouble with. On each video set are a few live performances from three or four bands. Each band has several songs in their sets and each video was filmed at an event. So there can be several videos for one event and an artist can have played at one or more event.

Is this making sense?

Anyway, attached is the database relationship table I've made so far. I'm pretty rusty with access.

What I eventually want to be able to do is search for an artist and the database will tell me what evemts they've played at and what the code is on the tape so I can find it in the physical world.

Or I want to search by year and see who played at that year's event and what songs they played.

Is my relationship table looking right? When I try to make a form to fill in all the information, some fields won't let me type, when I go into table mode it says I need related data. I haven't got to grips with making forms without using the wizard.

Thanks so much in advance by the way. I'm really rusty with this program and could use with some help.

i55.tinypic.com/2j46gly.jpg

Henners

Welcome to the forum!

Can a tape contain videos of bands from multiple events?

A tape contains several bands from one event. As far as I know anyway. They're all professionally recorded and I don't think any tapes were re-used over multiple events.

OK, then if an event has many tapes associated with it (one-to-many relationship), you need an events table

tblEvents
-pkEventID primary key, autonumber
-txtEventName
-dteEvent (event date)

tblTapes
-pkTapeID primary key, autonumber
-fkEventID foreign key to tblEvents
-TapeID
-TapeType (you actually could create a table to hold all of the relevant tape types and then use a foreign key here instead, your call)

Since an artist can occur on many tapes, it would be best to have a table to hold just the basic artist information

tblArtists
-pkArtistID primary key, autonumber
-txtArtistName


Now juction the tapes and artists on that tape

tblTapeArtists
-pkTapeArtistID primary key, autonumber
-fkTapeID foreign key to tblTapes
-fkArtistID foreign key to tblArtists

Now an artist can have many songs on each tape

tblTapeArtistSongs
-pkTapeArtistSongID primary key, autonumber
-fkTapeArtistID foreign key to tblTapeArtists
-songname
-StartTime
-EndTime



Technically speaking an artist may perform the same song at multiple events. This would require a little different structure.

tblArtists
-pkArtistID primary key, autonumber
-txtArtistName

tblArtistSongs
-pkArtistSongID primary key, autonumber
-fkArtistID foreign key to tblArtist
-txtSongName

tblTapeArtists
-pkTapeArtistID primary key, autonumber
-fkTapeID foreign key to tblTapes
-fkArtistSongID foreign key to tblArtistSongs
-StartTime
-EndTime


Also technically speaking, start and end times are just 2 times, so they represent the many side of a one-to-many relationship. Full normalization, would warrant that the 2 times be records in a related table, but I think most people would just put the 2 time fields in the table as I show above. I'll leave that call to you.

Hello again,

I'm still struggling with trying to make the forms work. I'm totally rusty at Access

Anyway, here's the relationships table I've made with your instructions. I can't seem to make a referential integrity join between the Events and Tapes tables.

Thanks in advance,

Henners

Oops, forgot to post the image link

uploadpic.org/view-pic.php?img=133109

You'll have to fix the referential integrity issue. What error did you receive when you attempted it?

I would check to make sure fkEventID in tblTapes is a long number datatype and that pkEventID in tblEvents is an autonumber datatype & is the primary key. If you have any data in the tblTapes for which the fkEventID is null, that may also throw back an error.