David McAfee
davidmcafee at gmail.com
Mon Jun 27 12:01:47 CDT 2011
well, if this was in SQL server you could write a udf like this:
using table:
PlantID ParentID ParentName
116 333 MMM
117 333 MMM
119 333 NNN
120 333 NNN
122 333 XXX
124 333 QQQ
125 333 QQQ
126 444 ABC
ALTER FUNCTION [dbo].[udfTableToList] (@ParentID AS INT) RETURNS
VARCHAR(1000) AS
BEGIN
DECLARE @str VARCHAR(1000)
SELECT @str = ISNULL(@str,'') + ParentName + ',' FROM (SELECT
DISTINCT ParentID, ParentName FROM tblTemp2 WHERE ParentID = @ParentID) A
RETURN RTRIM(LEFT(@str,LEN(@Str)-1))
END
/*
--Call the udf like this:
SELECT ParentID, Count(ParentID) AS CountOfParentID,
dbo.udfTableToList(ParentID) AS ParentName
FROM
(SELECT Distinct ParentID, ParentName from tblTemp2) A
GROUP BY ParentID
*/
returns:
ParentID CounofParentID ParentName
333 4 MMM,NNN,QQQ,XXX
444 1 ABC
You could do something similar in VBA, if you aren't using SQL Server.
HTH
David McAfee
On Mon, Jun 27, 2011 at 3:51 AM, William Benson <vbacreations at gmail.com>wrote:
> There are no "other tables". I need a bit more here please. And I need to
> know how to get only one of something that has the same count as the Max
> count of something yet to be fetched.
>
> Bill Benson
> Owner
> VBACreations, LLC
> On Jun 27, 2011 2:49 AM, <rockysmolin at bchacc.com> wrote:
> >
> >
> >
> > -------- Original Message --------
> > Subject: RE: [AccessD] Complex query with several subparts to it
> > From: <rockysmolin at bchacc.com>
> > Date: Sun, June 26, 2011 11:43 pm
> > To: "Access Developers discussion and problem solving"
> > <accessd at databaseadvisors.com>
> >
> > I'd start with a query that had just parent ID and parent name - make it
> > a summation query, group on Parent ID and Count on ParentName. Then you
> > could join that query to the other tables to get the plant id.
> >
> >
> > ROcky
> >
> >
> > -------- Original Message --------
> > Subject: [AccessD] Complex query with several subparts to it
> > From: "William Benson \(VBACreations.Com\)" <vbacreations at gmail.com>
> > Date: Sun, June 26, 2011 8:26 pm
> > To: "'Access Developers discussion and problem solving'"
> > <accessd at databaseadvisors.com>
> >
> > A query is making my head hurt - but I have to solve it - so any help
> > from
> > sql experts greatly appreciated.
> > I have tabular data with PlantId, ParentID and ParentName. More than
> > one
> > plant share a ParentID, which is fine, but I have found that the data
> > is bad
> > because for the same ParentID, there are more than one ParentName as
> > well,
> > which is very bad. So I want to point out these situations to the user
> > and
> > get them to select one of the parent names as an override - to go in a
> > mapping table. I have decided to present the info on a userform using a
> > combo and a listbox. The combo to show defective parentids, the listbox
> > to
> > show the parentnames which occurred for a given parentID selected in
> > the
> > combo.
> >
> > I am not too concerned with the sql to populate the listbox, I can get
> > there
> > on my own. But the sql to populate the combo with these features is a
> > little
> > complicated for me, if someone can help that would be very appreciated:
> >
> > Col 1: ParentID
> > Col 2: Count of Distinct Parent Names encountered
> > Col 3: Most frequently appearing Parent Name for a given parentid
> > Criteria: Count(distinct ParentNames)>1 for a given parentid
> >
> > The challenge I am up against is, how to pick that Most-Used Parent
> > Name.
> > because for some parentids, there is a tie between two or more
> > parentnames).
> > In the event of a tie, I want either the first or last occurring value,
> > I
> > don't care which:
> > PlantID ParentID ParentName
> > 116 333 MMM
> > 117 333 MMM
> > 119 333 NNN
> > 120 333 NNN
> > 122 333 XXX
> > 124 333 QQQ
> > 125 333 QQQ
> >
> > ParentID Count Example - OR
> > 333 4 either MMM or NNN... don't care which
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>