Group daily dates into weekly dates...?

[pablavo]

Sorry to be a pain and ask another date question. The DB I’m working on has backend access tables and a single Linked SQL Server table which I’ll call “SQL table”.

The Access DB is for Agency Staff and my present problem only concerns the SQL table and one Access table.

The Access table has two pertinent columns; Date column and Hours column. These values would be typed in manually to the Access frontend via a continuous form for each employee. The weekly figures (all hours for that week) would be taken from an Agency timesheet and added at the end of the week to the FE. The Weekending date entered happens to fall on a Saturday.

Weekending = 05/07/2008. Hours = 35

Just to mention, there is more information i.e. Rate of pay field. The rest is done by calculated fields; RateOfPay*Hours, VAT etc, however, this wouldn’t be relevant.

the two values (hours, Date) are all that's needed. There are no breaks calculated, no double shifts and no timestamps used.

The SQL Server table is taken from another SQL Server DB which has a third party frontend and all employees fill in regardless of Agency staff or permanent staff. This table stores the hours and dates on a day to day basis:

Date = 16/07/2008. DailyHours = 7

The Access DB has to have the ability to compare both tables values for these employees on a weekly basis. This is why I need to find a way to format, in some way, the SQL Server table values within Access so that it matches the Access tables layout.

So, for the SQL table, I'd have to be able to take a particular day, say a Saturday, use that as the weekending date and add all the hours from the SQL table together on a weekly basis so that all of the daily hours can be added to the week to form the 35 hours. That would mean finding a way to group the daily dates together into weeks so that I can add the sum of hours for that week.

Just to note, I'll be using both tables for the forms record source and linking both with the Employee firstname, lastname and Weekend date fields so that Users can compare the values on the form.

At the moment I’m looking at date calculations like the format function and thinking about possibilities of having this in a query or temporary table to make this possible, however, any help with this would be much appreciated.

Thanks.

 

[namliam]

Use the weekday(YourDate) function to revert any date back...

I.e. Date() - Weekday(Date())
returns July 12 2008, last saturday.... Now add 7 days to get next saturday.

Good luck

 

[LPurvis]

Can you lay out the table structures a little more formally?
e.g. is it something like

SQLTable
---------
SQLTableID (Identity / Autonumber)
DateWorked (DateTime)
HoursWorked (Number - what type... Integer? Decimal? etc)

LocalTable
----------
LocalID (Autonumber)
WeekEndingDate (DateTime)
TotalHours (Number...)

And you want to match the TotalHours against the calculated sum of the HoursWorked fields? Or actually enter the value? (Which is technically redundant data if it's calculatable ;-)

 

[pablavo]

Thanks for the replies, It's quite a long question.

SQL table
-----------
(I'd probably want to use the FirstName, LastName and Date to reference the Local Table)
DateWorked (DateTime)
Hours (Number)

Local Table
------------
ID (Autonumber)
WeekEndingDate (DateTime)
TotalHours (Number)

Yes, I want users to be able to compare the TotalHours with the Hours field to make sure the values are the same for each source.

There are text boxes calulations that multiple the Rate off pay by the amount of hours worked for the staff to name a few.

NamLiam, I looked at the weekday function, however, because I have to manipulate, and group, the dates from the start of the year, I don't think this function would work. For example, if I wanted to take last january and group all the daily dates into weekly dates i.e. every saturday for that week for the SQL table so that I could add the sum of hours for each week, I wouldn't really know how to use this function to achieve this.

Maybe I'd have to do this work on the SQL Server side rather than using Access. what do you think?

Again, thanks for the help

 

[namliam]

Using weekday to substract works... allways....
I.e.
15 jan 2008, weekday(15-jan) returns 3. 15 jan - 3 = 12 Jan 2008
16 jan returns 4. 16-jan - 4 = 12 Jan 2008
Any date betweeen 13-jan and 19 jan will return 12 Jan.
22 Jan becomes 19 jan and 23 Jan becomes 19 jan as well.

This offcourse would be the week starting date, but adding 7 would make it the week ending date.
[yourdatefield] - Weekday([yourdatefield]) + 7
Some examples:

15-Jan-08 19-Jan-08
16-Jan-08 19-Jan-08
31-Jan-08 02-Feb-08
01-Feb-08 02-Feb-08
16-Jul-08 19-Jul-08
17-Jul-08 19-Jul-08

Dont asume something wont work just because you dont understand the solution provided!

 

[pablavo]

Thanks NamLiam.

Perhaps I was assuming, however, I was hoping for elaboration (which you kindly supplied) for the exact reason that I didn't understand how to use the function this way.

Again, thank you

 

[pablavo]

namliam, just thought I'd get back to you and let you and other folks know how I got on.

I tried the weekday function just recently and it does technically work, however, I've been looking at the values from a query with the implemented function and calculating the values from the table with the daily dates.

Most of the values calculate to the same amount but somethings amiss. Some values don't add up so I'll have to work on this.

Anyway, thanks for the help


Paul

 

[namliam]

If something is a miss I think it would be your counting
[yourdatefield] - Weekday([yourdatefield]) + 7
Will allways equate to the next Saturday

And
[yourdatefield] - Weekday([yourdatefield])
will always equate to last saturday, no question about that....

Perhaps you are expecting something else with "edge" values...

I.e. the way this works is....
#23-aug-2008# - Weekday(#23-aug-2008#) +7
23-aug-2008 becomes 23-aug-2007
While
24-aug-2008 becomes 30-aug-2007

But other than that... it should work like a charm.