Rocky Smolin
rockysmolin at bchacc.com
Mon Jun 27 13:51:50 CDT 2011
Cool! R -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson (VBACreations.Com) Sent: Monday, June 27, 2011 10:52 AM To: 'Access Developers discussion and problem solving' Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com