Dave 14867
Registered User.
- Local time
- Yesterday, 21:58
- Joined
- Jan 1, 2017
- Messages
- 56
Hello,
I don't know SQL at all so please bear with me as I learn. I have a query that runs great and gives me expected results in datasheet view. When I try to create the report based on the query, I get the error
"multi level group by not allowed in subquery"
I copied the SQL statement from a very similar report, modified what was obvious had to be modified to get the data from the correct table and now I get this error. I don't get a similar error on the other report.
Here is the SQL that I believe is causing the issue.
SELECT tblM16034.M16034LotNum, (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT1ID) AS [M13472Lot#1], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT2ID) AS [M13472Lot#2], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT3ID) AS [M13472Lot#3], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT4ID) AS [M13472Lot#4], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT5ID) AS [M13472Lot#5], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT6ID) AS [M13472Lot#6], tblM16034.M16035ID, tblM16034.M22866ID, tblM16034.M16115LotNum, tblM16034.M08082LotNum, tblM16034.[DHR Rev], tblM16034.DOM
FROM tblM16034;
Any help greatly appreciated.
Dave
I don't know SQL at all so please bear with me as I learn. I have a query that runs great and gives me expected results in datasheet view. When I try to create the report based on the query, I get the error
"multi level group by not allowed in subquery"
I copied the SQL statement from a very similar report, modified what was obvious had to be modified to get the data from the correct table and now I get this error. I don't get a similar error on the other report.
Here is the SQL that I believe is causing the issue.
SELECT tblM16034.M16034LotNum, (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT1ID) AS [M13472Lot#1], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT2ID) AS [M13472Lot#2], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT3ID) AS [M13472Lot#3], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT4ID) AS [M13472Lot#4], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT5ID) AS [M13472Lot#5], (SELECT TOP 1 M13472LOTNUM FROM tblM13472 AS T1 WHERE T1.M13472ID=TBLM16034.M13472LOT6ID) AS [M13472Lot#6], tblM16034.M16035ID, tblM16034.M22866ID, tblM16034.M16115LotNum, tblM16034.M08082LotNum, tblM16034.[DHR Rev], tblM16034.DOM
FROM tblM16034;
Any help greatly appreciated.
Dave