3-Way Query: Syntax Error (missing operator) in query expression

[CJM]

I'm more used to SQL Server than Access, but I'm working on an Access DB at
the moment.

I have three tables: Location, Hotels, PrefHotels

Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.

In my query, I want to list the preferred hotels (chains) for a given
location...

The SQL I have so far is:

Select H.HotelName, H.TelNo, H.Notes, H.URL
from Hotels H
Inner Join PrefHotels P on H.HotelID = P.HotelID
Inner Join Locations L on P.LocID = L.LocID
where l.LocID = 2

While this works in SQL Server, it doesnt in access.

Can you tell me what the Access-friendly syntax would be?

Thanks

Chris

 

[Matthias Klaey]

I'm more used to SQL Server than Access, but I'm working on an Access DB at
the moment.

I have three tables: Location, Hotels, PrefHotels

Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.

In my query, I want to list the preferred hotels (chains) for a given
location...

The SQL I have so far is:

Select H.HotelName, H.TelNo, H.Notes, H.URL
from Hotels H
Inner Join PrefHotels P on H.HotelID = P.HotelID
Inner Join Locations L on P.LocID = L.LocID
where l.LocID = 2

While this works in SQL Server, it doesnt in access.

Can you tell me what the Access-friendly syntax would be?

Thanks

Chris

AFAIK the As keyword is required if you use aliases:

Select H.HotelName, H.TelNo, H.Notes, H.URL
from Hotels As H
Inner Join PrefHotels As P on H.HotelID = P.HotelID
Inner Join Locations As L on P.LocID = L.LocID
where L.LocID = 2

HTH
Matthias Kläy

 

[John Vinson]

While this works in SQL Server, it doesnt in access.

Access (or to be precise, JET) is picky about parentheses. Try

Select H.HotelName, H.TelNo, H.Notes, H.URL
from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
Inner Join Locations L on P.LocID = L.LocID
where H.LocID = 2

I also changed the 1.LocID to H.LocID - I'm not sure if the numeric
table reference works or not, but the alias does.

 

[CJM]

John, Matthias,

Thanks for your replies.

It appears that is was the missing parentheses that was the problem...

I also wondered if the 'As' keyword was mandatory in Jet SQL, but it appears
it isn't.

Thanks

Chris

 

[baiwei]

Hi Chris,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

As you can see from John's code, that 'AS' is not mandatory in JET, you can
just us 'AS' or not use it for table reference. The numeric
table reference won't work based on my test.

If you have more questions, please feel free to post new message here and I
am ready to support!


Best regards

Baisong Wei

Microsoft Online Support