As a guess, the records being returned by the subquery include duplicates. Something like 11/1/2008 11/1/2008 11/1/2008 11/13/2008 11/13/2008 And then the group by clause is reducing that down to show two records. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Song wrote: Thanks for quick reply. I followed the example
eaeqaaeamamx@yahoo.com wrote: 1. Know your purpose Are you wandering through life with little direction - hoping that you'll find happiness, health and prosperity? Identify your life purpose or mission statement and you will have your own unique compass that will lead you to your truth north every time. 2. Know your values What do you value most? Make a list of your top 5 values
Thanks Allen for furnishing this solution. I just made use of it to achieve a very similar result, in my organization's fundraising database (8 most recent donations per donor...) Regards, Franklin "Allen Browne" wrote: See: Subquery basics: TOP n records per group at: http://allenbrowne.com/subquery-01.html#TopN -- Allen Browne - Microsoft MVP. Perth
Thank you very much "Allen Browne" wrote: See: Subquery basics: TOP n records per group at: http://allenbrowne.com/subquery-01.html#TopN -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ray" <Ray@discussions.microsoft.com> wrote
Perhaps something like this: SELECT Sum(x) AS SumOfX FROM Table1 WHERE Table1.ID IN (SELECT TOP 5 ID FROM Table1 AS Dupe ORDER BY Dupe.y, Dupe.ID); If subqueries are new, see: http://allenbrowne.com/subquery-01.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne
