[AccessD] Complex query with several subparts to it

William Benson (VBACreations.Com) vbacreations at gmail.com
Sun Jun 26 22:26:58 CDT 2011


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 





More information about the AccessD mailing list