I am currently trying to refresh my Access skills – it’s been about 6 years since I last did solely Access and about 4 since I did databases proper with Delphi (GNVQ/GCSE/A-Levels etc). I am seriously ashamed I have let it slip for so long and I whilst I can do some things I class myse;f as a Noob once again, yay 
!
So, to brush up I have decided to do a database of my DVD collection, including information on the movies, actors and actresses and (hopefully in the end) a way to keep track of who I have currently lent a DVD too and a history of those lends.
At the moment I have the following three tables:
tblMovies
MovieID (PK)
Movie Title
Genre
Studio
Lead Male
Lead Female
Other Actor / Actress 1
Other Actor / Actress 2
Other Actor / Actress 3
… etc
tblActors
ActID (PK)
Name
Usual Role (eg. Lead Male or Lead Female, base of tblRoleTypes)
… etc
tblRoleTypes
Role Name (eg. Lead Male, Lead Female, Bad Guy, Good Guy etc)
Description
There will obviously only ever be 1 entry for each movie / DVD, but actors and actresses can participate in multiple movies.
At the moment I have a query setup so that it filters ‘Lead Male’ and ‘Lead Female’ roles based on ‘Usual Roles’ in the tblActors when selecting you are selecting the Lead Male and Lead Female in tblMovies. The ‘Other Actor / Actress #’ is done from a simple Lookup that takes the ActID, Name and Usual Role and displays the Name and Usual Role for you to select from.
I’m having difficulty setting up the relationship properly. At the moment the only relationship shown in the relationships windows is tblRoleTypes.Role Name ---- tblActors.Usual Role and tblActors.ActID ---- tblMovies.Other Actor / Actress 1. This means that when I open tblActors and click the [+] I only ever see movies where the Actor / Actress has been placed in ‘Other Actor / Actress 1’. This is particularly confusing me as I did exactly the same (I think) Lookup for Other 2 and 3 as I did for 1.
How can I set this relationship up so no matter whether they were a Lead or an Other then the movie is displayed when clicking the [+] sign? So for example if Milla Jovich was ‘Lead Female’ in Fifth Element and ‘Other Actor / Actress 1’ in Resident Evil Extinction then when I click on the [+] sign next to here record in tblActors then I will see both listed there.
From this I want to be able to run queries so that when I search for all my movies that have, for example ‘The Rock’ in then I get Doom, Walk Tall, Be Cool etc.
Also, how would I go about selecting two fields from a search type form and get the search results, eg. Selecting ‘Vin Diesel’ and ‘Sc-Fi’ getting back Pitch Black, Chronicles of Riddick etc or ‘Vin Diesel’ and ‘Action’ so getting back Fast and the Furious, xXx or selecting ‘All’ and getting back all 4 titles?
I also want to have the combo-box display the name of the actor or actress not he ActID if that is the way to store the records?!
This is really doing my head in as I honestly can’t seem to figure it out. There must be a nice simple method that I am missing. If any of you can shed some light on this then I’d be most grateful!
Cheers
Ricki
! So, to brush up I have decided to do a database of my DVD collection, including information on the movies, actors and actresses and (hopefully in the end) a way to keep track of who I have currently lent a DVD too and a history of those lends.
At the moment I have the following three tables:
tblMovies
MovieID (PK)
Movie Title
Genre
Studio
Lead Male
Lead Female
Other Actor / Actress 1
Other Actor / Actress 2
Other Actor / Actress 3
… etc
tblActors
ActID (PK)
Name
Usual Role (eg. Lead Male or Lead Female, base of tblRoleTypes)
… etc
tblRoleTypes
Role Name (eg. Lead Male, Lead Female, Bad Guy, Good Guy etc)
Description
There will obviously only ever be 1 entry for each movie / DVD, but actors and actresses can participate in multiple movies.
At the moment I have a query setup so that it filters ‘Lead Male’ and ‘Lead Female’ roles based on ‘Usual Roles’ in the tblActors when selecting you are selecting the Lead Male and Lead Female in tblMovies. The ‘Other Actor / Actress #’ is done from a simple Lookup that takes the ActID, Name and Usual Role and displays the Name and Usual Role for you to select from.
I’m having difficulty setting up the relationship properly. At the moment the only relationship shown in the relationships windows is tblRoleTypes.Role Name ---- tblActors.Usual Role and tblActors.ActID ---- tblMovies.Other Actor / Actress 1. This means that when I open tblActors and click the [+] I only ever see movies where the Actor / Actress has been placed in ‘Other Actor / Actress 1’. This is particularly confusing me as I did exactly the same (I think) Lookup for Other 2 and 3 as I did for 1.
How can I set this relationship up so no matter whether they were a Lead or an Other then the movie is displayed when clicking the [+] sign? So for example if Milla Jovich was ‘Lead Female’ in Fifth Element and ‘Other Actor / Actress 1’ in Resident Evil Extinction then when I click on the [+] sign next to here record in tblActors then I will see both listed there.
From this I want to be able to run queries so that when I search for all my movies that have, for example ‘The Rock’ in then I get Doom, Walk Tall, Be Cool etc.
Also, how would I go about selecting two fields from a search type form and get the search results, eg. Selecting ‘Vin Diesel’ and ‘Sc-Fi’ getting back Pitch Black, Chronicles of Riddick etc or ‘Vin Diesel’ and ‘Action’ so getting back Fast and the Furious, xXx or selecting ‘All’ and getting back all 4 titles?
I also want to have the combo-box display the name of the actor or actress not he ActID if that is the way to store the records?!
This is really doing my head in as I honestly can’t seem to figure it out. There must be a nice simple method that I am missing. If any of you can shed some light on this then I’d be most grateful!
Cheers
Ricki