Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-17-2011, 03:50 AM   #1
henners
Newly Registered User
 
Join Date: Jan 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
henners is on a distinguished road
Database of videos

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

henners is offline   Reply With Quote
Old 01-17-2011, 07:35 AM   #2
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: Database of videos

Welcome to the forum!

Can a tape contain videos of bands from multiple events?
jzwp22 is offline   Reply With Quote
Old 01-17-2011, 07:42 AM   #3
henners
Newly Registered User
 
Join Date: Jan 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
henners is on a distinguished road
Re: Database of videos

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.

henners is offline   Reply With Quote
Old 01-17-2011, 07:56 AM   #4
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: Database of videos

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.
jzwp22 is offline   Reply With Quote
Old 01-18-2011, 09:56 AM   #5
henners
Newly Registered User
 
Join Date: Jan 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
henners is on a distinguished road
Re: Database of videos

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
henners is offline   Reply With Quote
Old 01-18-2011, 09:56 AM   #6
henners
Newly Registered User
 
Join Date: Jan 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
henners is on a distinguished road
Re: Database of videos

Oops, forgot to post the image link

uploadpic.org/view-pic.php?img=133109
henners is offline   Reply With Quote
Old 01-18-2011, 10:22 AM   #7
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: Database of videos

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.


jzwp22 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Store Videos? vvasudev General 1 03-03-2010 07:15 AM
videos in a form thunder Forms 2 02-28-2009 01:51 PM
Returning Loaned Videos leahd Forms 1 02-09-2009 04:25 AM
Videos KenHigg Politics & Current Events 10 11-27-2007 08:44 PM
Training Videos KenHigg General 0 09-09-2005 11:52 AM




All times are GMT -8. The time now is 10:13 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World