[AccessD] select distinct per year

On 21 Apr 2003 at 12:50, Pedro Janssen wrote:

> Hi Susan,
> 
> i thought this could be done with a select distinct, but i wasn't sure
> 
> This what i have
> 
> TableA
> 
> Date                           num1            num2
> 01-02-2000               5,2                   4,1
> 02-03-2000               4,6                   4,3
> 01-04-2001               4,2                   6,5
> 14-07-2001               6,2                   3,1
> 
> 
> This is what i want;
> in this example the calculation is sum
> 
> Date                          num1              num2
> 2000                          9,8                    8,4
> 2001                          10,4                  9,6
> 

Note , I renames your field "Date" to nDate. It's not a good idea to 
use reserved words as field names.


SELECT Year([nDate]) , Sum(num1) , Sum(num2)
FROM tNumbers
GROUP BY Year([nDate])

You can build this (or at least a  longer version including table 
naes etc)  directly in Query Design by going to View - Show Totals, 
creating a field as "nYear:Year([nDate])" , adding Num1 and Num2 as 
selected fields  and then setting Grouping as  "Group By" for the 
first  field and  "Sum" for Num1 and Num2. 

Then click on SQL View to see what it looks like. You can copy/paste 
the resulting SQL into a string if you need to use it as a record 
source or in a DoCmd.RunSQL command
















-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.