Query sorting by date

[munkee]

Hi all,

Something which I feel would have been relatively easy seems to not be coming out the way I wish.

I am using the following sql to query how many nonconformances have occured by month and at what cost.

SELECT Count(tbllog.NCC_ID) AS NCCS, Sum(tblCosts.CostFig) AS SumOfCostFig, Format([DteOccur],"mmmm" & "yyyy") AS TimeLine
FROM tbllog LEFT JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID
GROUP BY Format([DteOccur],"mmmm" & "yyyy")
ORDER BY Format([DteOccur],"mmmm" & "yyyy");

I am trying to order the dates by oldest to newest. However I get the following output (I threw in feb1986 just to prove to myself it was wrong):

NCCS	SumOfCostFig	TimeLine
5	£33,234.00	August2010
10		February1986
1	£30,000.00	February2010
4	£3,790.00	July2010
7	£985.00	June2010
1	£50,000.00	March2010
2	£5,700.00	May2010

As you can see the timeline is not in the correct order. I can see the sql doesnt look quite right as there isn't an actual order to say go ascending/descending so I assume it is how I have tried to format DteOccur which is a Short Date to get it to month/year.

Anyone able to clarify how this should be done correctly.

Thanks,

Chris

[munkee]

I have got further with this and now producing:

SumOfCostFig	Dateofevent	CountOfNCC_ID
£30,000.00	February 2010	1
£50,000.00	March 2010	1
£5,700.00	May 2010	2
£985.00	June 2010	7
£3,790.00	July 2010	4
£33,234.00	August 2010	5

However if I remove the "Count" on NCC_ID I get the true data:

SumOfCostFig	Dateofevent	NCC_ID
£30,000.00	February 2010	585
£50,000.00	March 2010	586
£5,700.00	May 2010	472
£125.00	June 2010	522
£400.00	June 2010	524
£80.00	June 2010	525
£40.00	June 2010	536
£340.00	June 2010	580
£1,260.00	July 2010	521
£2,000.00	July 2010	578
£30.00	July 2010	579
£500.00	July 2010	602
	August 2010	650
	August 2010	656
£33,234.00	August 2010	657
	August 2010	670

As you can see in June shows 5 records to be added to the NCC ID Count. However 7 are shown in the first table when I do a Count.

I think this is because for some reason the count is counting the underlying cost fig table data. Where each ID number has multiple costs assigned to it. My query has summated these costs and grouped by them. However the query Count on NCC ID looks to be counting the ID values in the cost table.

How do I make my query count UNIQUE/DISTINCT as this will then only count the ID once.


Current sql:

SELECT Sum(tblCosts.CostFig) AS SumOfCostFig, Format([DteOccur],"mmmm yyyy") AS Dateofevent, Count(tbllog.NCC_ID) AS Test
FROM tbllog LEFT JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID
GROUP BY Format([DteOccur],"mmmm yyyy"), Month([DteOccur])
ORDER BY Month([DteOccur]);