William Benson
vbacreations at gmail.com
Mon Jun 27 05:51:44 CDT 2011
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