Specific Relationships Help in BASE

[mtorbett]

Hi all,

Recent Access convert but still relatively new to databases. I’m hoping someone can help me out with an issue I’m having.

I have two tables, Actors and Shows, linked by a Characters table. The Characters table contains the FKs of ActorID and ShowID as well as a character name. I’ve tried populating the fields and running a query, but instead of being able to assign a character to an actor and a show, it’s bringing over all the actors and assigning the same character to each of them, instead of 1-to-1. Could someone take a look at what the issue is? ODB file attached.
CMUCostumeStock.odb (97.7 KB)

[RobertG]

@mtorbett

instead of being able to assign a character to an actor and a show

The foreign keys are in table character. So you have to add a show and an actor to the character table, not a character to an actor or a show.

[mtorbett]

Thanks! That’s what I’m attempting to do - by “adding a character” that joins the actor and show FKs.

This is a screenshot of the Characters table and the single entry I have:

But when I run a query, it has assigned all actors and all shows to that character rather than one actor and one show.

How would you suggest fixing that?

[mtorbett]

Here’s what happens when I run the query.

[cpb]

you failed to join your tables using the relationships which you previously created.
you have what is known as a cartesian join.
a cartesian join creates a result set that combines every row from the first table with every row from the second table.
I used alias’s in place of table names which results in cleaner, easy to read SQL.

SELECT
	s."Title" AS "Show Title", 
	s."Season", 
	a."First Name" || ' ' || a."Last Name" AS "Actor Name", 
	c."CharacterName" AS "Character Name", 
	c."ID", 
	c."ShowID", 
	a."First Name", 
	a."Last Name" 
FROM 
	"Shows" s
join
	"Characters" c
		on s.ID = c."ShowID"
join
	"Actors" a
	on c."ActorID" = a.ID
ORDER BY 
	"Show Title" ASC, 
	"Character Name" ASC

[mtorbett]

Thank you SO MUCH for this!