Assistance with SQL syntax.

[gm_lowery]

Hi everyone,



Im trying to do a left join query in Access using SQL but I am stumped at the syntax and am thus getting a join expression not supported error since I rarely ever use it. Can someone please help format the proper syntax for me?

SELECT Series.SeriesID, Series.SeriesActive, Series.SeriesTitle, Series.SeriesType, Sessions.SeriesID, Sessions.SessionStart, Sessions.SessionEnd, Sessions.LeaderID, Sessions.LocationID, Sessions.MaxParticipants, Sessions.SessionNote INTO [Archive Table]
FROM Participants, Series
LEFT JOIN Sessions ON Series.SeriesID = Sessions.SeriesID

Many thanks,

Michael

[JoeM]

Are you trying to do an Append Query or a Select Query?
If you are doing a Select Query, remove the "INTO [ARCHIVE TABLE]" part.
If you are trying to do an Append Query, your syntax doesn't look quite right (see: http://office.microsoft.com/en-us/ac...001032245.aspx).

Also, what is the "Participants" in your From clause? It it is a third table, you probably need to join that too.

[gm_lowery]

I am actually trying to do a make table query. I dont know if that changes anything.

[gm_lowery]

And yes participants is also a third table.

[pbaldy]

How about

SELECT Series.SeriesID, Series.SeriesActive, Series.SeriesTitle, Series.SeriesType, Sessions.SeriesID, Sessions.SessionStart, Sessions.SessionEnd, Sessions.LeaderID, Sessions.LocationID, Sessions.MaxParticipants, Sessions.SessionNote INTO [Archive Table]
FROM Series LEFT JOIN Sessions ON Series.SeriesID = Sessions.SeriesID

Edit: you weren't using the third table, and without a join it would have resulted in a Cartesian product.

[gm_lowery]

No I need the data from the third table as well. Whether it is a right or left join I am not sure, but it is one of the two.

[pbaldy]

Put the query in design view, drop in the third table and draw the joins manually, clicking on them to edit. The descriptions there should guide you to the proper join. Then switch to SQL view and see what it produced.