CODEWITHSUNDEEP
sqlms-access
gavisonfire
gavisonfire

Reputation: 13

Group by week showing random intervals in Microsoft Access Report

I have a query in MS Access (using Access 2010) where I would like to sort the result by weeks together with a sum of a certain field and count of number of records that week. This data would then be used in a report/graphs.

The query is currently as follows:

SELECT Clients.MemberID, ProcessControl.TransferID, Clients.Forename, Clients.Surname,
Clients.Agent, ProcessControl.PensionProvider, ProcessControl.AppInDate,  
ProcessControl.FundsRecDate,ProcessControl.ActualAmount, 
(DatePart("ww",[FundsRecDate])) AS WorkWeek
FROM Clients INNER JOIN ProcessControl ON Clients.MemberID = ProcessControl.MemberID
WHERE (((ProcessControl.FundsRec)=True));

This produces the data that I require, but obviously it is not grouped. The date it needs to be grouped by is found in "FundsRecDate" and the amount that I need to sum is "ActualAmount."

I realise that at this point I haven't grouped any data. I used the report wizard to group by week. The 'Grouping' section on the design view of the report reads as:

Group on FundsRecDate  >  from oldest to newest  > by week  > with no totals...etc

When this report is produced, the report is giving me weeks at random intervals, for example, week commencing 15/01/2012, then 18/01/2012 followed by 24/01/2012.

I also tried sorting by 'WorkWeek' using DatePart but this combined week one from 2012 with week one from 2013, which is not what I require - I need it to the results in chronological order, sorted by week.

Any assistance as to how I can achieve this would be greatly appreciated!

Upvotes: 1

Views: 529

Answers (1)

elc
elc

Reputation: 1952

That doesn't look random, that looks like sorted as text (insofar as its safe to judge from a sample size of 3). I'd say rather than using the wizard you should add week and year as separate columns in a report feeder query and do your sorting and grouping on those columns. (year as a separate grouping column should solve the combined transition week problem you mention)

Upvotes: 0

Related Questions