Join expression not supported

[ZOR]

I had a query which worked okay.

SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.NName, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, Audio.AudioType AS A, Audio_1.AudioType AS b, Audio_2.AudioType AS C, Audio_3.AudioType AS D
FROM (Audio AS Audio_3 RIGHT JOIN (Audio AS Audio_2 RIGHT JOIN (Audio AS Audio_1 RIGHT JOIN (Audio RIGHT JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) ON Audio_1.AudioID = TXMASTERS.Audio2) ON Audio_2.AudioID = TXMASTERS.Audio3) ON Audio_3.AudioID = TXMASTERS.Audio4) LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
ORDER BY TXCLIPS.Start;

However I have created a junction table to take care of the field NName in table TXCLIPS being common to records. The NName is now called Athlete in a table AthleteNames.

I created a new query which now includes the Junction table and the AthleteNames table, but this now throws a a dialogue box saying the Join expression is not supported. Any ideas, thanks

New query

SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, AthleteNames.Athlete, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, Audio.AudioType AS A, Audio_1.AudioType AS b, Audio_2.AudioType AS C, Audio_3.AudioType AS D
FROM AthleteNames INNER JOIN (((Audio AS Audio_3 RIGHT JOIN (Audio AS Audio_2 RIGHT JOIN (Audio AS Audio_1 RIGHT JOIN (Audio RIGHT JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) ON Audio_1.AudioID = TXMASTERS.Audio2) ON Audio_2.AudioID = TXMASTERS.Audio3) ON Audio_3.AudioID = TXMASTERS.Audio4) LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1) INNER JOIN JUNCTION ON TXCLIPS.ID2 = JUNCTION.ID2) ON AthleteNames.AthleteID = JUNCTION.AthleteID
WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
ORDER BY TXCLIPS.Start;

 

[maxhugen]

I'm not sure, but your second query looks like its doing an Outer Join on TXCLIPS, followed by an Inner Join between TXCLIPS and JUNCTION.

Try changing that Inner join to an Outer join.

HTH

Max Hugen
Australia