calculate calendar days for a month

[spottednewt]

I have a table where each record has a defining field WEEK_ENDING_DATE. Then I have a field for each day of the week in that same record (M, T, W and so on) Each day field is for entering the number of hours a person has worked that day in that week. Now I need to calculate the number of hours worked for each calendar day of the month. So I need some way of translating the S field to sunday, M field to Monday, and figure out which days to count for the weeks based on the calendar month of the week_ending_date.

Any ideas?

 

[MichaelRed]

Please take some time to familiarize yourself with the site. There are several to numerous threads which attempt to advise newcommers of the opprotunities. One is the Search capability, which includes access to a large (vast?) historical record of threads. For common issues, like date / time expressions, there are likely to be a more rapid availabililty of the information you seek than through posting of new threads. Last Day of a/the month has been answered often.



MichaelRed

 

[BoxHead]

spottednewt,

The first thing to check out is

http://r937.com/relational.html

You have a table structure that looks like someone was trying to store data in a manner that would make a nice report rather than in a useful structure.

If your table had fields for empID, workDate, and workHours you would have the information ready to report by day, week, month, quarter, etc. Now your challenge is to to try and get the exosting data into that starting format.

You could use a seven-part Union query like

SELECT EmpName, S AS WorkHours, DateAdd("d",-6,[Week_Ending_Date])AS WorkDate FROM myTable
UNION
SELECT EmpName, M AS WorkHours, DateAdd("d",-5,[Week_Ending_Date])AS WorkDate FROM myTable
UNION
SELECT EmpName, T AS WorkHours, DateAdd("d",-4,[Week_Ending_Date])AS WorkDate FROM myTable
UNION

etc...

This assumes your week ending date is Saturday and the field 'S' contains the hours worked for the Sunday before DateAdd("d",-6,[Week_Ending_Date])

I'd get the Union query working and saved and then use it as a basis for a make-table query. I'd also suggest getting the data entered is this fashion from the start.

Once you have the data somewhat normalized, MichaelRed's suggestion on searching the site for Date Reporting will give you a lot of 'on-point' guidance.

HTH





John

Use what you have,
Learn what you can,
Create what you need.