I have a table called Episodes that I want to join with a table called Characters; since there can be multiple characters in multiple episodes, I've also created a table called CharEp to join the two. The primary key in Episodes is a composite key of Seasons and EpNum (two, 2-character fields that always result in a unique string). The primary key in Characters (CharID) is autovalued. CharEp should join the two; right now I've created two fields in that table: ChID and EpID where EpID should contain the composite key from Episodes.
In my mind what I want to do is join the tables something like this:
Code: Select all
INNER JOIN CharEp
ON Episodes.Season + Episodes.EpNum = CharEp.EpID
but I don't actually know HOW to achieve that in OO.
I suspect that I might just be approaching the whole setup wrong, so here I am asking for any help for the best way to set up the relationship between the two tables, Characters and Episodes; nothing is populated yet at this point so I can scrap and rework anything without any harm. Any input is greatly appreciated, thanks in advance!