Sum of Week Totals

[ASWilliams]

Hello Access Forum,



I have a database on SQL Server for which I will be creating a front end in Access. I currently need a query that is proving difficult for me write.

I have a Volumes table with columns [AccNo], [ISOWeek], and [Volume]. I have another table that stores basic account information; the key columns for this post are [AccNo] and [StartDate]. What I need is the total annualised volume for customers with a start date 52 weeks old or greater than a specified ISO week. What I need my query to do is this:

1. Loop through each of the 52 weeks in a year
2. for each week, determine which accounts have a start date 52 weeks or greater
3. sum the volume for all accounts, based on their start date (as in point 2)
4. sum the total of sums to generated the annualised total volume.

I'm able to convert dates to ISO Weeks and write the conditional statements to identify the accounts I need. The big I'm struggling with is how to effectively loop through each week (rather than get the account status for a single week, then sum the volume for those​ accounts over a year - this would be incorrect).

Any advise on what kind of query construct(s) I need on this will be much appreciated. I'm guessing there's going to be a subquery or two, but I'm not quite sure where to start.

Many thanks in advance

[ASWilliams]

I've managed to find a solution to this problem by using CROSS APPLY:

I first created a table variable containing all the week numbers I need to search. I then created a table-valued function that returns all the account numbers identified as having a start date that meets the criteria for the given week. I then use:

SELECT *
FROM @WeekNumbers
CROSS APPLY fn_myFunction

This essentially runs the function (returning a table) for each week number in the @WeekNumbers table.