rockysmolin at bchacc.com
rockysmolin at bchacc.com
Mon Jun 27 01:47:41 CDT 2011
-------- 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