Grouping Data

[Sykesjas]

Hi I'm wondering if anyone can help me with my query.

I have a databaase that captures the amount of hours and employee works on a project. they way the data is captured is through these field names

Job ID
Employee Name
Week Commencing
Monday
Tuesday
Wednesday
Thursday
Friday

the number of hours are entered into the days of the week.

What I want to do is create a total for the week and group the totals by each month.

when i create a report based on a query I have done preivously I manage to group the data by month, however it shows all the data per week commencing. I just want a total figure for the month rather than show all the records against week commencing and also group the data by Job ID

Hope this makes sense, I have attached a screenshot to show my current problem

 

[Galaxiom]

Using separate fields for days of the week is not really suited to your goal because it means selecting arbitrary fields from records. Better to use individual records based on dates.

The week commencing data would remain quite easy to retrieve from this alternative structure.

Continuing with your current structure will probably throw more problems at you later but you can work around this current problem by making a union query to get all the days of the week data into a single field and calculating a date field from the day of the week and commencing date.

Calculate the individual dates by adding the number of the day to the commence date with DateAdd. Then Month() to derive a Month field. Then group on that field.