SQL error

[craigh]

I keep getting a sql error (missing operator) with this. what am I doing wrong?

SELECT u.[Part Description], u.[Part Number], a.[Last Name], a.[First Name]


FROM Uniforms u INNER JOIN uniform_assignments ua
ON u.uniformID = ua.uniformID JOIN [Band Addresses] a
ON ua.studentID = a.StudentID
WHERE (((ua.[Date Assigned]) Is Not Null) AND ((ua.[Date Returned]) Is Null));

[ranman256]

Sorry, but it looks ok but I dont see the reason for alias' on the join fields.
Did you try without the alias'.

[craigh]

Hi ranman256 - thank you for your reply.

I did try it without the alias' first and got the same error. :-(

I used the alias' to try to simplify the query a bit (at least make it more readable).

The full error is:

Syntax error (missing operator) in query expression 'u.uniformID = ua.uniformID JOIN [Band Addresses] a
ON ua.studentID = a.StudentI'

[June7]

You are building this in query designer? Not VBA?

Could try:
AS a
AS u
AS ua

Access usually fills in the AS keyword if neglected.

[John_G]

I don't think you can use just JOIN by itself - you have to specify INNER, LEFT, RIGHT, or FULL JOIN.

John

[June7]

Doh! good eyes John.

If building in query designer, that should not happen. Build in designer then switch to SQL View to get statement if you want to see the syntax to use in VBA.

I've never seen FULL used.

[ssanfu]

John is right, plus Access adds parenthesis after the keyword FROM:

SELECT u.[Part Description], u.[Part Number], a.[Last Name], a.[First Name]
FROM (Uniforms u INNER JOIN uniform_assignments ua ON u.uniformID = ua.uniformID) 
INNER JOIN [Band Addresses] a
ON ua.studentID = a.StudentID
WHERE (((ua.[Date Assigned]) Is Not Null) AND ((ua.[Date Returned]) Is Null));

[June7]

Access adds more parens than needed. Sometimes very annoying.

I do wonder if INNER join will produce desired output.

[craigh]

John is right, plus Access adds parenthesis after the keyword FROM:

SELECT u.[Part Description], u.[Part Number], a.[Last Name], a.[First Name]
FROM (Uniforms u INNER JOIN uniform_assignments ua ON u.uniformID = ua.uniformID) 
INNER JOIN [Band Addresses] a
ON ua.studentID = a.StudentID
WHERE (((ua.[Date Assigned]) Is Not Null) AND ((ua.[Date Returned]) Is Null));

This code works! Thanks so much for your help.

I do wonder if INNER join will produce desired output.

You are exactly right. I'm not getting what I hoped

What I am trying to do is get a list of ALL uniforms. Then if the uniform is currently assigned, show the person it is assigned to. The assignments are based on a date range (hence the WHERE statement). But the current WHERE is excluding any uniform that is currently unassigned. I can get just the unassigned uniforms with a query like this:

SELECT Uniforms.[Part Description], Uniforms.size, Uniforms.[Part Number]
FROM Uniforms
WHERE ((((Uniforms.uniformID) Not In (select uniformID from uniform_assignments where [Date Returned] is null)));

Does anyone see an easy way of combining these to produce the desired result? (I originally was trying to use a LEFT JOIN but didn't get that to work either )

[craigh]

I tried using the Query Designer (I think that's what its called - shows tables at the top and fields below) with the successful query above and just dbl clicked on the relation between Uniforms and uniform_assignments and changed it to a LEFT JOIN. I get an error then about ambiguous query.

SELECT u.[Part Description], u.[Part Number], a.[Last Name], a.[First Name]
FROM (Uniforms AS u LEFT JOIN uniform_assignments AS ua ON u.uniformID = ua.uniformID) INNER JOIN [Band Addresses] AS a ON ua.studentID = a.StudentID
WHERE (((ua.[Date Assigned]) Is Not Null) AND ((ua.[Date Returned]) Is Null))
ORDER BY u.[Part Description], u.[Part Number];

like here:

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

And they suggest two queries... this is new to me. How do I do two queries at the same time?

[craigh]

I got it!

SELECT *
FROM uniform_assignments INNER JOIN [Band Addresses] ON uniform_assignments.studentID = [Band Addresses].StudentID
WHERE (((uniform_assignments.[Date Assigned]) Is Not Null) AND ((uniform_assignments.[Date Returned]) Is Null));

SELECT Uniforms.[Part Description], Uniforms.size, Uniforms.[Part Number], [Band Addresses].[Last Name], [Band Addresses].[First Name]
FROM Uniforms, uniforms_helper_all, Uniforms LEFT JOIN uniforms_helper_all ON Uniforms.uniformID = uniform_assignments.uniformID
ORDER BY Uniforms.[Part Description], Uniforms.[Part Number], Uniforms.size;

I didn't understand the two query thing until I figured out I actually had to make two query objects and treat the helper like a table. then it made sense.

Access is a bit weird about FORCING the query to include all the table/query names in the second query with the JOIN statement after (with the comma). and if you save the query and re-open it they disappear causing an error. You have to retype them in again in order to make any edits.

anyways - thanks everyone for your help!