Calculating average weekly hours

[Elmobram22]

Hi guys,

Massive one this...

I have a table with staff in.
I have a table with the start and end of their shifts.
We have four possible locations and four weeks.
So I have four tables for each location.

I have a module that can work out hours and deductions based on time in and out. I built that into a seperate databse working on a one time in and one time out setup.

I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.

I assume you need further information so I have cobbled together a sample database here...

Cheers,

Paul

I assure you it ain't a virus too. Just a database I need help with

 

[plog]

I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.

That should be a simple query to generate, if you had properly structured tables. Yours however are not. You need to start over and set up your tables properly. This process is called normalization (http://en.wikipedia.org/wiki/Data_normalization) check out that link, find a few tutorials and give structuring your tables another shot.

The overridding issue I see with your structure is that you are storing information in table and field names. Tables/fields should contain generic names and specifics should be in individual records.

For example, I see 4 tables named similar to 'TblRotaWeekXav', each with the same structure, 1 per week. When you have similarly structured tables, you only need 1 of those tables. You would move the piece of information (Week Number) from the name, into a field in that one table. That means you would have a 'TblRotaav' table and a new field called 'WeekNum' to designate which week that record was for. However...

You made that error in another way as well. It looks like all the weekly tables have the same structure ('_av', '_comit', '_lh', & '_ps'). Same resolution--make one table and include a field to hold those values, instead of suffixing them to a table name.

Those 2 fixes gets your 16 weekly tables' data into 1 table. But, you've also made that type of error at the field level as well. Instead of 3 fields per day with the day name in the field name (Mon1Start, Mon1Finish, Mon1Sleepin) you create a field to hold which day that is for. That gets your table from 21 daily fields to 4 fields.

You get your data into the structure I described and the query you want is a simple aggregate query (https://msdn.microsoft.com/en-us/library/bb243844(v=office.12).aspx), using built in functions that do the calulcations for you.

 

[Elmobram22]

Sweet thank you. I'll get on the normailsation!

 

[Elmobram22]

In terms of the normalisation I would still like the form to have the same lay out as it has. Is there a way of setting it up with the normalised tables in that way. Like...

ID - Primary Key
StaffID - Foreign Key
Location - Foreign Key
Day - Foreign Key
Time In - Date/Time
Time Out - Date/Time
Sleep In - Yes/No

 

[Elmobram22]

Here is an updated version of the table structure with normalisation. I'm just wondering how I can get it to work with the layout of the form.

 

[plog]

Let's worry about forms later, but to allay your fears, what you can do is have a button, then when it is clicked it automatically inserts default records into your table and the subform populates to generate a form that looks similar to what you have now

Good job on your structure, there's still a few things I see:

What happened to all the '_av', '_comit', '_lh', & '_ps' data? You haven't accomodated that in your database.

There's no need for a Week table, just put the number of the week driectly into tblTime.

Same thing with your day table, just put the words directly into tblTime

What happens on week 5?

 

[Elmobram22]

Hi,

The av etc is now in location and has fuller names. I was thinking that about the tables for weekdays etc but thought I'd be more thorough ha! We have a rolling 4 week rota so basically it goes back to week 1 again. I'll put the days and weeks into the TblTime.

Cheers,

Paul

 

[plog]

What does "goes back to week 1 again"? What do you envision happening? Deleting all the data then repopulating?

It might be time to start talking about what you expect as the output from this thing. What reports/exports do you expect it to provide you?

 

[Elmobram22]

It's just to hold the data for the four week rota. All the equations etc are done based on four weeks so no real need to go any further with it. The next step I am trying to do is adding up all the total hours at all locations over 4 weeks which I have done so that I can figure out things like annual leave allocation etc. We have a separate rota to hold the ongoing data in excel.