query 'grouping' and 'summing' over a period of time 1

[ps32208]

Hi,

I have an access db where I need to indentify the top ten largest customers together with the value of goods bought over a given period of time. I have created the query using the design view but have hit a brick wall in achieving the result. Because month is the field used in the query to identify the time period, the result shows customers across each month rather than once over the given period and a sum of all the goods bought. How can I achieve this? The SQL statement looks like:

SELECT [Detail - Product_Sales].Month, [Detail - Product_Sales].[Customer Group ID], [Detail - Company].[Customer Group Name], Sum([Detail - Product_Sales].[Sell to Net CLC $]) AS [SumOfSell to Net CLC $], [Detail - Product_Sales].[Report Type]
FROM [Detail - Company] RIGHT JOIN [Detail - Product_Sales] ON [Detail - Company].[Customer Group ID] = [Detail - Product_Sales].[Customer Group ID]
GROUP BY [Detail - Product_Sales].Month, [Detail - Product_Sales].[Customer Group ID], [Detail - Company].[Customer Group Name], [Detail - Product_Sales].[Report Type]
HAVING ((([Detail - Product_Sales].Month) Between "200401" And "200406"))
ORDER BY [Detail - Product_Sales].Month, Sum([Detail - Product_Sales].[Sell to Net CLC $]) DESC;

Any help, or a push in the right direction, would be greatfully received.

Thanks,
Pete.

 

[lupins46]

SELECT [Detail - Product_Sales].[Customer Group ID], [Detail - Company].[Customer Group Name], Sum([Detail - Product_Sales].[Sell to Net CLC $]) AS [SumOfSell to Net CLC $], [Detail - Product_Sales].[Report Type]
FROM [Detail - Company] RIGHT JOIN [Detail - Product_Sales] ON [Detail - Company].[Customer Group ID] = [Detail - Product_Sales].[Customer Group ID]
GROUP BY [Detail - Product_Sales].[Customer Group ID], [Detail - Company].[Customer Group Name], [Detail - Product_Sales].[Report Type]
HAVING ((([Detail - Product_Sales].Month) Between "200401" And "200406"))
ORDER BY Sum([Detail - Product_Sales].[Sell to Net CLC $]) DESC;

 

[PHV]

Something like this ?
SELECT TOP 10 P.[Customer Group ID], C.[Customer Group Name], Sum(P.[Sell to Net CLC $]) AS [SumOfSell to Net CLC $], P.[Report Type]
FROM [Detail - Company] AS C RIGHT JOIN [Detail - Product_Sales] AS P ON C.[Customer Group ID] = P.[Customer Group ID]
WHERE P.Month Between "200401" And "200406"
GROUP BY P.[Customer Group ID], C.[Customer Group Name], P.[Report Type]
ORDER BY Sum(P.[Sell to Net CLC $]) DESC;

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

 

[ps32208]

Very helpful, I see what was wrong and missing. Many Thanks