syntax error on query, operator missing - need help

[lorirobn]

Hi -

I need help with a query. I am getting a error when I try to run (error highlights the word "INNER"). The error message is Syntax error/missing operator.

My query is:

SELECT A.RoomID, Max(ActivitySeqNo) AS MaxSeqNo
FROM tblPestsActivityDetails AS A
GROUP BY RoomID  
INNER JOIN
[SELECT RoomID, ActivitySeqNo, ActivityDate FROM tblPestsActivityDetails]  AS M 
WHERE  (M.RoomID = A.RoomID)  AND (M.ActivitySeqNo = MaxSeqNo) ORDER BY 1
;

I probably have more mistakes, but can't get past the error on INNER.

Any suggestions? I am stumped. I have not worked much with using 2 of the same table, and labeling them.

In case it helps, what I am trying to do is find the max seq number for each room on a table. The table contains multiple records for a room, and I want to find the latest one (thus, max seq number). I want to examine a different field on this same max record, and that is why I have the inner join.

THANKS.

 

[PHV]

I guess you wanted something like this:
SELECT A.RoomID, A.ActivitySeqNo, A.ActivityDate
FROM tblPestsActivityDetails AS A INNER JOIN (
SELECT RoomID, Max(ActivitySeqNo) AS MaxSeqNo FROM tblPestsActivityDetails GROUP BY RoomID
) AS M ON A.RoomID = M.RoomID AND A.ActivitySeqNo = M.MaxSeqNo
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

Oh, I can't believe it was that easy. Thanks once again, PHV - it worked like a charm.

Am I correct to surmise that what I was doing wrong was to have the Max value in the first Select statement, when it should be in the 2nd Select? Can the Max not be in the first Select?

My logic for doing it that way was: first get the Max sequence number, THEN use it to get the correct record. That's why I put the Max statement first. Does it not work like that?

Would you be able to explain why the Max goes in the 2nd select? Even though it worked, I would like to try to learn HOW it worked...the underlying logic that is used in the SQL.

Thanks,
Lori

 

[lespaul]

your error was actually caused by using [ ] around the SELECT clause for the INNER JOIN instead of parens ( )

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lorirobn]

OHHHHH!!!!!!!!
That just fixed the weird problems I was having yesterday with this query (sometimes it worked, sometimes it didn't).
Amazing what simple parentheses will do...
Thanks so much, Leslie.
Lori

 

[lorirobn]

Oops, spoke too soon...

It is very strange what has been happening with this query.

On Wednesday, I created the query from PHV's code above. It worked fine. I set up a report to run from the query, and got an error. I went back to the query and tried to run it, and it no longer worked. The error was: "The Microsoft Jet Database Engine cannot find the input table or query 'SELECT RoomID, Max(ActivitySeqNo), AS MaxSeqNo FROM tblPestsActivityDetails GROUP BY RoomID'. Make sure it exists and that its name is spelled correctly".

I figured out that when I make a change or type the query in from scratch and then run it, it works. But - when I do a SAVE, I get the above error.

The same just happened with Leslie's suggestion. I changed brackets to parens, ran it, and it worked. I got all excited and thought that was the answer. I did a SAVE, tried to run it again. Poof - same error happening again.

I have no idea what is going on.
I no longer 'need' the query as I have gone through with an alternative, but it would be nice to learn what is going on and how to do this correctly.

Thanks.
Lori

 

[lorirobn]

another PS - it's not all that clear when it works and when it doesn't, but the query sometimes works, sometimes doesn't.
????