UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Query Inner Join    
 
   
Hasse46
post Dec 2 2010, 12:59 PM
Post #1

UtterAccess Member
Posts: 23
Hi,
I have the following query that returns an error (Incorrect syntax near 'GROUP' and 'M') I don't see where the problem is!
Can anybody see it?

CODE
SELECT M.mainid, M.serialno, M.testdate, M.testtype, T1.parametername, T1.parametervalue, T1.parametertext, T1.testdataid
FROM (dbo_tblmain AS M INNER JOIN dbo_tbltestdata AS T1 ON M.mainid = T1.mainid) INNER JOIN [SELECT T2.mainid, T2.parametername,
Max(T2.testdataid) AS MaxtestdataidPermainid
FROM [dbo_tbltestdata] AS T2
GROUP BY T2.mainid, T2.parametername
]. AS SQ ON (T1.testdataid = SQ.MaxtestdataidPermainid) AND (T1.parametername = SQ.parametername) AND (M.mainid = SQ.mainid)
WHERE (((M.serialno) Like "030-0405-002-00142*") AND ((T1.parametertext)="HT Test"))
ORDER BY M.mainid;


Regards
Hans
Go to the top of the page
 
+
Jeff B.
post Dec 2 2010, 01:17 PM
Post #2

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
Have you tried copying that SQL statement into the SQL view of a new query and switching to design view? You might be able to spot something that way...
Go to the top of the page
 
+
Hasse46
post Dec 2 2010, 01:25 PM
Post #3

UtterAccess Member
Posts: 23
Yes, but the problem is that it would not let me open it in design view because of the error. This is the popup respons I get (Syntax error in FROM clause).

The origin of this query I got from another post and I could run that query but this also had the same errors in the query.
The problem started when I tried to modify it and I couldn't save it because of the error.

/Hans
Go to the top of the page
 
+
Jeff B.
post Dec 2 2010, 01:43 PM
Post #4

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
So that's not the same error you reported in your original post... you're getting two different errors?

This portion appears puzzling ...

GROUP BY T2.mainid, T2.parametername ].
Go to the top of the page
 
+
Hasse46
post Dec 2 2010, 02:04 PM
Post #5

UtterAccess Member
Posts: 23
This is strange, suddenly I could read the design view!?
What I can not see is the T2 - I think it is just a name for the dbo_tbltestdata table that I'm using.
But as I said I can't see it in the design view...

/Hans
Go to the top of the page
 
+
Hasse46
post Dec 3 2010, 10:56 AM
Post #6

UtterAccess Member
Posts: 23
I went ahead and redid everything using a subquery instead of the suspicious T2.

/Hans
Go to the top of the page
 
+
Jeff B.
post Dec 3 2010, 06:45 PM
Post #7

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
Does that mean it's working now? Consider posting your (successful) SQL statement. Other folks may be looking for exactly the same solution in the future...
Go to the top of the page
 
+
Hasse46
post Dec 7 2010, 05:33 PM
Post #8

UtterAccess Member
Posts: 23
Yes it's working. I did a subquery subqRTTimeDate and put that in this one:
CODE
SELECT subqRTTimeDate.mainid, dbo_tblmain.serialno, dbo_tblmain.testtype, dbo_tblmain.testdate, dbo_tblmain.invalidtest, dbo_tblmain.stationid, subqRTTimeDate.MaxOftimedate, dbo_tbltestdata.testdataid, dbo_tbltestdata.parametervalue, dbo_tblstatus.status
FROM ((subqRTTimeDate INNER JOIN dbo_tblmain ON subqRTTimeDate.mainid = dbo_tblmain.mainid) INNER JOIN dbo_tbltestdata ON dbo_tblmain.mainid = dbo_tbltestdata.mainid) INNER JOIN dbo_tblstatus ON (dbo_tbltestdata.mainid = dbo_tblstatus.mainid) AND (subqRTTimeDate.mainid = dbo_tblstatus.mainid)
WHERE (((dbo_tbltestdata.parametername)="Cold_End") AND (([subqRTTimeDate]![MaxOftimedate])=[dbo_tbltestdata]![timedate]))
GROUP BY subqRTTimeDate.mainid, dbo_tblmain.serialno, dbo_tblmain.testtype, dbo_tblmain.testdate, dbo_tblmain.invalidtest, dbo_tblmain.stationid, subqRTTimeDate.MaxOftimedate, dbo_tbltestdata.testdataid, dbo_tbltestdata.parametervalue, dbo_tblstatus.status
HAVING (((dbo_tblmain.serialno)="030-0405-002-00161"))
ORDER BY subqRTTimeDate.mainid, dbo_tblmain.serialno, dbo_tbltestdata.testdataid;


/Hans
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 07:10 AM