"Multi-level GROUP BY clause not allowed in subquery"

[jonbales]

I'm having trouble with this Access report query. It works in Database View, but I receive an error when trying to use it on a report:

SELECT	Trim([F_NAME] & " " & [M_Name] & " " & [L_Name]) AS MemberName,
	PERSON.L_NAME, PERSON.F_NAME, PERSON.M_NAME,
	PERSON.Level,
	PERSON.LocalNo,
	PERSON.SSN,
	PERSON.Trade,
	PERSON.LayoffIndicator,
	PERSON.OrigInitDate,
	(SELECT OrigCert
		FROM Skills
		WHERE Skills.Skill="EMPLOYEE TOOLS" AND Skills.Card_Num = PERSON.Card_Num) AS DateIssued,
	Company.C_NAME, 
	Company.CompanyNo
		FROM PERSON
			INNER JOIN Company ON PERSON.CompanyNo = Company.CompanyNo
				WHERE (((PERSON.Level)<>"D" And
					(PERSON.Level)<>"HM" And
					(PERSON.Level)<>"W" And
					(PERSON.Level)<>"HP" And
					(PERSON.Level)<>"AA") AND
					((PERSON.LocalNo)=UA_LocalNo()))
						ORDER BY PERSON.L_NAME, PERSON.F_NAME, PERSON.M_NAME;

 

[PHV]

I receive an error
Any chance you could post the whole error message ?
Anyway, what about this ?

SELECT Trim(P.F_NAME & ' ' & P.M_Name & ' ' & P.L_Name) AS MemberName
, P.L_NAME, P.F_NAME, P.M_NAME, P.Level, P.LocalNo, P.SSN, P.Trade
, P.LayoffIndicator, P.OrigInitDate, S.OrigCert AS DateIssued
, C.C_NAME, C.CompanyNo
FROM (PERSON P
INNER JOIN Company C ON P.CompanyNo = C.CompanyNo)
INNER JOIN Skills S ON P.Card_Num = S.Card_Num
WHERE S.Skill = 'EMPLOYEE TOOLS'
AND P.Level NOT IN ('D','HM','W','H','AA')
AND P.LocalNo = UA_LocalNo()

For a report don't use the ORDER BY clause in SQL but the sort/group feature of the report.
BTW, what is UA_LocalNo() ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I expect you might have been attempting to sort in the report on the DateIssued field. I would try PH's suggestion.

Duane
Hook'D on Access
MS Access MVP