[AccessD] [FWD: RE: Complex query with several subparts to it]

David, thank you, and thank Rocky, and anyone else who was thinking on this.
I posted also in AccesslL with the disclaimer that I was already looking on
this list as well, and said there I would post the working answer in this
location as well. Here is the result as arrived at by Stuart Schulman and
myself. It's funny how there is pretty much nothing fancy to it ... and I
apologize to Rocky who was probably right on the money but I had no genuine
SQL to go on so I couldn't see the forest thru the trees.



This simple answer deserves to be in the record books for simplest solution
to a complicated issue! I know it could be done with far more complex
subqueries, but jeez-loueez this is simple and to the point.

ParentID	Count	Representative
333		10	QQQ
444		11	AAA


Select ParentID, cint(Left(Criteria,3)) as Count, Mid(Criteria,4) as
Representative

from
(
SELECT ParentID, Max(format([CountOfParentName],"000") & [ParentName]) AS
Criteria  

FROM
(
SELECT 
   ParentID, ParentName, Count(ParentName) AS CountOfParentName FROM 
   test
GROUP BY
  ParentID, ParentName
) as Query1

Group By
   ParentID
)

Works for


PlantID	ParentID	ParentName
111	333		MMM
112	333		MMM
113	333		NNN
114	333		NNN
115	333		QQQ
116	333		QQQ
117	333		QQQ
118	333		QQQ
119	333		QQQ
120	333		QQQ
121	333		QQQ
122	333		QQQ
123	333		QQQ
124	333		QQQ
125	333		XXX
126	333		XXX
127	444		QQQ
128	444		QQQ
129	444		QQQ
130	444		QQQ
131	444		QQQ
132	444		QQQ
133	444		QQQ
134	444		AAA
135	444		AAA
136	444		AAA
137	444		AAA
138	444		AAA
139	444		AAA
140	444		AAA
141	444		AAA
142	444		AAA
143	444		AAA
144	444		AAA
145	444		BBB