SQL query help

[coolpeebs]

Hav an ms-access table in the following format...

Customer ID Year Amount

1000 1990 100
1000 1990 100
1000 1991 500
1000 1995 200
1001 1992 1000
1002 1989 300
1002 1990 200

is it possible to run an sql query so that I get the data in the below mentioned format

i need no. of distinct years and the sum of amounts for all yrs. against each customer

Customer ID Year Amount

1000 3 900
1001 1 1000
1002 2 500

 

[mashelford]

On your query view right hand click where you see the fields you mention - on the Customer ID field select group by , then the year field select count , then the amount field sum - or the SQL view would be :

SELECT [TBL-Customers].[Customer ID], Count([TBL-Customers].Year) AS CountOfYear, Sum([TBL-Customers].amount) AS SumOfamount
FROM [TBL-Customers]
GROUP BY [TBL-Customers].[Customer ID];


The above is known as group by qry.

 

[coolpeebs]

thnx..it's working...hav really forgotten sql..

cheers,
peebs

 

[PHV]

Jet SQL doesn't support the Count(DISTINCT) predicate.
You may try this:
SELECT A.[Customer ID], Sum(B.Tmp) AS [Year], Sum(A.Amount) AS Amount
FROM yourTable A INNER JOIN (
SELECT DISTINCT [Customer ID], [Year], 1 AS Tmp FROM yourTable
) B ON A.[Customer ID] = B.[Customer ID] AND A.Year = B.Year
GROUP BY A.[Customer ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244