tracking copies of the same record

[artsalive]

I run a touring film circuit called Flicks in the Sticks. We run over 500 films a year and are a registered charity.

I couldn't run it without my Access dbase, but there is a problem which I would really like to crack.

My main table is the Events table which carried info like which film, which promoter, which date etc. I can have up to 6 screenings for any one date.

Another table is the Films table. I sometimes have three copies of a film and at the moment I have as many records as I do film titles. Which means inputting all the information 3 times.

What I would like is to have one record for each film, but also a field that says how many copies of it I have.

Then when I am in the events database I choose the film and which copy I am going to use (also making sure that I am not using a copy that is already in use). But this is the bit I am not sure how best to achieve.

Screenshot attached if this might help.

I am pretty good at fighting my way through to a solution, I need help in knowing where to start!!

Thanks

 

[charityg]

Off the top of my head, it seems like the normalized database design would be two tables.

One table to store the unique film ID, and another table to store the film descriptions.
Like this

tblFilm
FilmID
DescriptionID

tblFilmDescription
DescriptionID
FilmName
FilmRunningTime
etc. etc.

Join the tables on the DescriptionID.

 

[pcEars]

Just guessing:

tblFilms
FilmID
CopyID
Title
(other info)

tblEvents
EventID
EventDate
Promoter
FilmID
CopyID
(other info)

In short, if you have multiple copies of the same film, and may send them out to multiple events on the same date, then I'd say you're gonna need to account for the multiple copies somewhere... I'd suggest using the FilmID AND CopyID as a multi-field primary key. If there's only 3 each, maybe it's not so bad in terms of data entry. In any event, each copy is a capital asset, is it not?

 

[artsalive]

Thanks Guy's - food for thought. I'll have a play and see how far again but I suspect... I'll be back.