SubQuery Error

[aboondocksaint2]

So i am trying to get this subquery to work where only the records with the two cities Chicago or Gary are shown.

Please keep in mind i am very new to MS Access and databasing in general

I keep getting this error

Syntax error in query expression 'tblGuest.GuestFirstName
(SELECT tbleGuest.City
FROM tblGuest
WHERE tbleGuest.City="Chicago" OR tblGuest.City="Gary".

This is my code

 SELECT tblGuest.GuestID, tblGuest.GuestLastName, tblGuest.GuestFirstName, tblGuest.City, tblGuest.PhoneFROM tblGuest
ORDER BY tblGuest.GuestLastName, tblGuest.GuestFirstName
(SELECT tblGuest.City
FROM tblGuest
WHERE tblGuest.City="Chicago" OR tblGuest.City="Gary");

[RuralGuy]

I'm a long way from good with SQL but try:

SELECT GuestID, GuestLastName, GuestFirstName, City, Phone
FROM tblGuest
WHERE City = "Chicago" Or City = "Gary"
ORDER BY GuestLastName, GuestFirstName;

...and see how it works.

[aboondocksaint2]

Originally thats what I had and it does work. But since this is a homework assignment and it wants a subquery does that qualify?

[RuralGuy]

Nope! The requirement you have does not lend itself to a SubQuery.

[aboondocksaint2]

What do you mean?

[RuralGuy]

Homework...oh boy. What choices do we have for tables and data? What do you have to work with or do you just make it up?

[aboondocksaint2]

So I started out with a query called qryGuestData the first three line of code are what was there when I open it up in SQL view. Then the assignment asks me to copy that query and paste a new one called qryGuestDataSubquery. In which it wants me to modify it to a subquery that filters out everything until it only shows records that contain chicago and gary in the city column. Let me know if thats what you needed to know.

[RuralGuy]

Yes, that's the info I was looking for and now I'm stumped too. I'm pretty good with real world problems but contrived ones in homework stump me pretty easy. It was why I went ughh... when learning it was homework. I'm sorry but I would not have even responded if I'd known. School zooms past my knowledge very quickly.

[aboondocksaint2]

well thanks for the attempt i appreciate it. If all else fails I will just get knocked off a few points. I already completed the assignment earlier the way you did it then when I was taking a test I realized that I had done it the wrong way. I have till tomorrow at midnight to submit it so hopefully someone can help me out before then.

[lfpm062010]

Here is some additional help.

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

I have never used a subquery at "ORDER BY" level (or even know that is possible). I have seen subquery in "SELECT", "WHERE", or "FROM". It is all depends on what you are trying to do.
Looking at your query, I don't get why you need a subquery. They are selecting from the same table and the subquery is just selecting couple city. There is no join or anything. So, I don't get what your intention is for the subquery. What you are trying to do can simply be done using a where clause instead of subquery.

[aboondocksaint2]

Well the reason that I am using a subquery is because its a homework assignment or else I would just use the suggestion provided above.

[lfpm062010]

TRY THIS: This will make use of the subquery. Not sure if this what you trying to accomplish.

SELECT tblGuest.GuestID, tblGuest.GuestLastName, tblGuest.GuestFirstName, tblGuest.City, tblGuest.PhoneFROM tblGuest
WHERE tblGuest.City IN (SELECT tblGuest.City FROM tblGuest WHERE tblGuest.City="Chicago" OR tblGuest.City="Gary")
ORDER BY tblGuest.GuestLastName, tblGuest.GuestFirstName
;

[aboondocksaint2]

Woohoo! Ok I see what I did wrong now thank you so much for your help! All of you guys!