Steve@trop
Registered User.
- Local time
- Yesterday, 16:52
- Joined
- May 10, 2013
- Messages
- 148
I've been fighting with this problem for a while now. I have a union query that joins the results of about 10 select queries that are all structured the same way but for some reason, two of the select queries are causing an error. I can run all of the queries individually except two of them error out.
The error is:
"A column has been specified more than once in the order by list."
That would be easy to fix but there is NO ORDER BY in the code for these queries! I've narrowed the problem down to the Where clause. When I eliminate part of that clause the error stops. Other than the tables, there is no difference between the clause in the queries that work and the clause in the queries that error out. How do I track this down so I can fix it? Here is the code for one of the queries that isn't working:
The error is:
"A column has been specified more than once in the order by list."
That would be easy to fix but there is NO ORDER BY in the code for these queries! I've narrowed the problem down to the Where clause. When I eliminate part of that clause the error stops. Other than the tables, there is no difference between the clause in the queries that work and the clause in the queries that error out. How do I track this down so I can fix it? Here is the code for one of the queries that isn't working:
Code:
SELECT NorthCompressorRoundT.LoggedAt AS [Time], NorthCompressorRoundT.CompressorID AS ID, NorthCompressorRoundT.CompressorIntegrity AS Integrity, Null AS Integrity2, NorthCompressorRoundT.Status, NorthCompressorRoundT.Notes AS Comments, NorthCompressorT.Type
FROM NorthCompressorT INNER JOIN (LatestCompressorRoundQ INNER JOIN NorthCompressorRoundT ON (LatestCompressorRoundQ.Time = NorthCompressorRoundT.LoggedAt) AND (LatestCompressorRoundQ.ID = NorthCompressorRoundT.CompressorID)) ON NorthCompressorT.AssetNumber = NorthCompressorRoundT.AssetNumber
WHERE (((NorthCompressorRoundT.LoggedAt)=[LatestCompressorRoundQ]![Time]) AND ((NorthCompressorRoundT.CompressorIntegrity)="Known Defect" Or (NorthCompressorRoundT.CompressorIntegrity)="New Defect" Or (NorthCompressorRoundT.CompressorIntegrity)="DFM"));