Results 1 to 11 of 11
  1. #1
    johnmtb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    5

    Relationship Problem

    Hi,



    I am fairly new to using MS Access 2010, on Windows 7.

    I have a problem which I have been unable to solve by checking on google and watching several hours of tutorials.

    My database covers my DVDs and Blu-Ray movies. It comprises four tables, tblMovies, tblActors, tblDirectors and tblProducers. In the tblMovies there are, among others, the following columns: Actor01ID, Actor02ID, Actor03ID, Actor04ID, DirectorID and ProcucerID.

    I am able to form a relationship between the tblMovies, tblDirectors and tblProducers quite easily, but when I want to relate the tblActors to Actor01ID, Actor02ID, Actor03ID, and Actor04ID in the tblMovies, I cannot get it to work. The plan was that the tblActors contains all of the actors and actresses in all of my movies, and Actor01ID, Actor02ID, Actor03ID, Actor04ID are the Male Leading Actor, the Female Leading Actor, Supporting Actor Number One and Supporting Actor Number Two.

    However, if I relate tblActors Primary Key to Actor01ID, I am unable to relate tblActors Primary Key to Actor02ID, etc.

    I have looked into the many-to-many relationships using a joint table, but that does not seem to do the job.

    Please advise me how to create the relationships that I am looking for.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    My advice to you is to use the joint table (e.g. tblMovieActors). A movie has many actors and each actor takes part to many movies.

    Regards,
    John

  4. #4
    johnmtb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    5
    I don’t know what happened here. I replied to both responses separately, but they have not appeared, so I will reply again.

    orange and accesstos, thank you for your suggestions. However, they both basically refer to the same thing. I have tried using a joint table but without success. I could not see how to accomplish the following:

    tblActors -> tblMovieActors -> into four separate columns in tblMovies.

    Could you sketch a relationship that I could try?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    johnmtb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    5
    orange, thank you, but I had already downloaded that and it does not work for me. all the relationships are single. that is, a single connection from one table goes to a single connection in another table.

    I need a multiple output connection:

    tblActors -> tblMovieActors -> into four separate columns in tblMovies.

    there must be some way of accomplishing this.

  7. #7
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    This is a simple schema of a many-to-many relationship.

    Click image for larger version. 

Name:	JointTable.JPG 
Views:	24 
Size:	60.2 KB 
ID:	37008

    You have to forget the "four separate columns in tblMovies". In table of movies mustn't be anything about Actors.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    johnmtb,

    It seems you are not familiar with or are confused with some fundamental concepts of relational database design.
    Here is a link that should clarify these concepts and assist your project.
    I often advise people to describe WHAT they want to support with database in clear, simple English.Then make a model of the things and their relationship with one another. Create/gather some test data and sample scenarios (mock-ups of reports or questions that this "proposed database" must answer) and test the model. Refine/revise the model and/or the data and/or the scenarios when anomalies or shortcomings are found. Get your model working on paper where changes are much easier to implement. Use your vetted model as a blueprint for your database. Do not jump too quickly into physical Access.

    Nobody said any of this stuff was trivial, and I can assure you that getting your tables and relationships designed to support the business is the most critical aspect of database. Implementing bad tables and relationships will lead to a lot of pain and constant workarounds.

    Here is another link to articles in various formats regarding Database Planning and Design.

    Good luck with your project.

  9. #9
    johnmtb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    5
    OKGuys, I will look into both suggestions and try to come up with what I amlooking for.

  10. #10
    johnmtb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    5

    Relationship Problem

    Afterhaving studied the schematic suggested by accesstos, and having downloaded andread the first link by orange and having downloaded and watched the YouTubevideos, nothing has come up with what I require; which may mean it isimpossible.As anexample of what I am trying to do, I created my own schematic which I showbelow:
    Click image for larger version. 

Name:	Relationships.jpg 
Views:	11 
Size:	86.4 KB 
ID:	37039Iappear to have cropped the tblProducers, but I don’t have a problem with thatrelationship. The only problem I have is in relating the single ActorID,tblActors, to Actor01ID, Actor02ID, Actor03ID, and Actor04ID in the tblMovies.Ifthis is not possible, I will have to ignore making it a relational database andinclude the actual names of the actors and actresses in the tblMovies.Ihave written programs in Visual Basic 2010 to manage my databases, so if Icannot do what I want I will have to write an additional subroutine to handleit for me.Whatdo you suggest?
    Attached Thumbnails Attached Thumbnails Relationships.jpg  

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you don't design your tables and relationships to match your requirements, you will be in for a lot of pain trying to customize multiple work arounds that all stem from poor design.

    Your tblMovies is the first issue. It is not normalized and contains multiple entities.
    As Accesstos said - you have to rid yourself of the 4 separate links for Actors in tblMovies.

    You may get some ideas from these links.
    https://www.w3resource.com/sql-exerc...atabase-42.php
    http://www.databaseanswers.org/data_...nt_version.htm
    https://www.imdb.com/interfaces/

    http://www-inf.int-evry.fr/COURS/BD/...tml#section1.1

    Redesign your database; test it with test data and some scenarios; build your database, then revise your Visual Basic 2010 prog.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relationship problem!
    By jamal numan in forum Access
    Replies: 6
    Last Post: 01-29-2012, 02:13 PM
  2. One to many relationship problem
    By Catherine in forum Access
    Replies: 3
    Last Post: 12-27-2011, 04:08 AM
  3. Relationship Problem?
    By j3lena in forum Database Design
    Replies: 1
    Last Post: 01-14-2011, 05:27 PM
  4. Relationship Problem
    By hawzmolly in forum Database Design
    Replies: 4
    Last Post: 07-18-2009, 05:39 PM
  5. Relationship problem?
    By amangill1984 in forum Access
    Replies: 0
    Last Post: 03-04-2009, 08:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums