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

Arthur Fuller fuller.artful at gmail.com
Mon Jun 27 13:18:46 CDT 2011


It works in Access but does not work in SQL Server, due to the CInt call.
This can be fixed easily, however.

A.

On Mon, Jun 27, 2011 at 1:51 PM, William Benson (VBACreations.Com) <
vbacreations at gmail.com> wrote:

> 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
> )
>
>



More information about the AccessD mailing list