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]);