CODEWITHSUNDEEP
ms-accessms-access-2007
tim daniels
tim daniels

Reputation: 43

Is it possible to calculate a total cost per calendar week in Access?

I have an attendance form set up for a holiday club based on the Access 2007 Students template. Works exactly how I want it to after a few modifications.

Something the client has requested includes noting down the cost for a child per day. This is easily done but I'm also wondering if it's possible to auto-calculate a total cost per calendar week in Access as well?

So for instance perhaps another table that uses the student details but only displays a total cost - with a record navigation starting from Monday for each week? But how would I begin doing this?

Upvotes: 2

Views: 129

Answers (2)

HansUp
HansUp

Reputation: 97131

If you want weekly cost per student, you can use DatePart() in a GROUP BY query:

SELECT
    y.Student,
    Year(y.[Attendance Date]) AS year_number,
    DatePart('ww', y.[Attendance Date], 2) AS week_number,
    Sum(y.Cost) AS SumOfCost
FROM YourTable AS y
GROUP BY
    y.Student,
    Year(y.[Attendance Date]) AS year_number,
    DatePart('ww', y.[Attendance Date], 2) AS week_number
ORDER BY 1, 2, 3;

The third DatePart() argument is firstdayofweek, and 2 is the value of the VBA constant vbMonday. But you can't use the constant name in a query, so that's why I supplied the constant's value instead.

Note DatePart() supports an optional 4th argument, firstweekofyear. If the week numbering from my suggestion is unsatisfactory, see whether you can use firstweekofyear to adjust the numbering to your liking. If that still doesn't number the weeks as you wish --- perhaps your organization uses a custom week number strategy --- you may need to create a reference table which stores the week number for each Attendance Date.

Upvotes: 1

msiudut
msiudut

Reputation: 251

I'd try something like this in SQL.

Select [Student], [AttendanceDate], [Cost]
From [ModifiedStudentsTable]
WHERE Year([AttendanceDate])* 53+DatePart("ww", [AttendanceDate]) = Year(Date())* 53+DatePart("ww", Date()) - [VariableEnteredInYourForm]
;

[VariableEnteredInYourForm] is a value

1 = last week
2 = the week before that.

Upvotes: 0

Related Questions