Average on date only not record?

[jillnoble]

Hi , this is my first post, so please forgive my ignorance and I hope I've put in in the right place!
I need to work out an average pay of an employee for the week. I've run a very basic report to try and show you what I mean.
This is all to do with calculating employees average pay over the last 52 weeks of full working weeks to enable us to calculate their holiday pay, but I can't even get past the first stage!
I basically want a average of their weeks pay over the number of days worked, however, they could've done 2,3,4... jobs in one day so I want Access to just work out the average if the date is different? Is that possible?
Thanks in advance...

 

[jillnoble]

Thank you for your reply MajP, I received it on my email but not on here? Not sure if I'm doing this right, as I said first Forum!
Please see attached the Design view for the previous report, I think that is what you described? However, this is still averaging on records, not dates? Thanks again

 

[MajP]

In the date footer what is the control source of the calculated control? Same for the Employee footer.
The employee footer may be problematic because I think you want an average of the weekly average. Doing a calculation on an already calculated control normally does not work.
I would think in the week footer
=Avg([basic] + [Overtime])
in the employee footer
=Avg(Avg([basic] + [Overtime]))

not sure if the latter will work. If not we can do a query and then do a dlookup to get the average of the average. The prior should work.

 

[Pat Hartman]

If either could be null, use the NZ function.

=Avg(Nz([basic]) + Nz([Overtime]))

Both levels should be the same calculation.

 

[jillnoble]

Hi, thank you to you both for trying to help, I've tried both of those with the same result. It's still working out the average on each record, whereas I need the average for the week but only for the different days in that week? I don't think I'm explaining too well!

As the first example above, the first week only details 5 days, although there are 8 records so the calculation currently is £720.00/8 whereas I need the £720.00 to be divided by 5 i.e total number of days in that week.

Am I making myself any clearer or just making it worse

 

[MajP]

That is clearer. You actually want the daily average for that week not the weekly average for that week.
So you need to figure out the number of discrete days actually worked in that week. Is it going to always be the first day to the last day, or can there be dates in between that they are not paid. In the first case they are paid on 5,6,7,8,9. Is there ever the possibility it could be 5,7,9?

If always from the first to the last that can be done on the report, but if there could be gaps I would do a seperate query to figure out the discrete days worked per week and then use a dlookup to help in the calculation.

 

[jillnoble]

Sorry I didn’t make that clear before and thank you for sticking with me!

There will always be entries for Monday to Friday, but some of them maybe zero, if off sick for example. However, I do need Sat to Friday, as they may’ve done overtime at the weekend.

The report I sent was just a very basic one done by the report wizard to show you what I need to try and achieve. However, that’s just the first step, I somehow need to totally discount some weeks further along the line which include certain tasks determined by another field and these would include off sick days.

What I’m trying to do, somehow, and I’m not sure it’s even possible at the moment, is to have a report, with a date parameter, which works out the average earnings, but only for full working weeks, not including weeks with certain tasks, OS (Off Sick) OH (On holiday) etc. But I don’t expect you to do my job for me, just wanted to explain what my end result needs to be. I’ve attached another example to try and explain

Thanks again

 

[MajP]

Is there anyway you can make a query with
EmployeeID, paydate, pay, and whatever the far right column is (I think it is overtime pay). If possible also include the non full week identifiers (OH) sick. Or possibly that is another table. Then export that into excel so I have some real data to play with.

If I was doing this I think it will be to complicated to do as a calculated control so I would build 1 or more external queries to do this and use a dlookup call in the footer to get the results.

In a group by query I would group by employee ID, date, week no, and count of days, Sum of pay and get the sum of pay divided by the number of days. This would give you the average daily pay not the average payment.
Depending on how the table is designed that query could filter out the special weeks. Of may have to make another query that identifies the "special weeks (OH, Sick)" and use that to help remove them for that employee for that week. I have to see you table structure.

Then I would make a function
Public Function GetDailyAverageForWeek(EmployeeID, WeekNo) as currency
'use a dlookup on the above query with employeeid, WeekNo
' Possibly use a second dlookup here to get the adjusted value for the "Special Week"
end Function

In the forms footer the calculated control would be
=GetDailyAverageForWeek([EmployeeID],[txtWeekNo])

The grouping is done in the report using a week number by formatting the date. Could add this to the underlying query to simplify things.

What I’m trying to do, somehow, and I’m not sure it’s even possible at the moment, is to have a report, with a date parameter, which works out the average earnings, but only for full working weeks, not including weeks with certain tasks, OS (Off Sick) OH (On holiday) etc.

The above is definitely doable, but like I said may be much easier to do some of these calculations outside of the report and use a function call in the report to get the results.

Better than an excel if you can strip this down to just the report, queries, and tables needed to run that report an import into a new database. Remove or scramble and personal identifying information. That would help. This may sound complicated, but if you could get me that I think it would take 10-15 minutes for me to demo. About as long as typing this reply.

 

[jillnoble]

Hi MajP, Thank you loads for all your time.

I've attached a very scaled back version just including what you require and left me on there as an employee with random wages. I hope that's what you mean!

 

[MajP]

I did a quick demo before you sent this. I took three simple queries to do it so it is more explainable. See if the results are what you are thinking;

 

[MajP]

Note sure how far along you are with this, but if you are just starting out you have a lot of problems in the design of your tables which if you do not address now it will cause a ton of headaches down the road. If this is already in use with lots of data then you may just have to work around it. If you are just starting out, I would seriously spend time cleaning up the tables. There is a lot wrong with database normalization and problems with naming. If that ship has sailed I will look at working around it, but understand you will be putting band aids on top of band aids if you plan to use this long term.

 

[jillnoble]

Those results look great I've just got to work out how to get those into my DB.

I totally know what your saying, I knew nothing really about databases when I started creating, I just happened to be able to goggle a couple of computer issues and solve them in our very small office and therefore got the IT badge and because I have a bit of excel knowledge, it fell to me when it came to upgrading our DB. Used Access as already had it as part of the MS package, started with a completely blank sheet and a dummies guide and it has evolved from there. However, we have now been using it for 6 years and its served us well so far, I'm able to create queries and reports on pretty much all of the data and to suit individuals needs, if not I use google, this one just got me stumped! Don't get me wrong, I love it and would love to start all over again and do it properly this time but unfortunately, I am way past that point now.

Thank you so much again.

 

[MajP]

See query

 

[jillnoble]

This is brilliant, thank you so much!
Only problem I've got now is when I add the task to your query it breaks it all down again. I need to somehow exclude weeks with specific Tasks as these weeks are not allowed to be part of the average calculation but I'll try and have a play with that .

Thank you again for all your help

 

[MajP]

Only problem I've got now is when I add the task to your query it breaks it all down again. I need to somehow exclude weeks with specific Tasks as these weeks are not allowed to be part of the average calculation

Can you provide more details on this. Where does the task type come from and what gets excluded?

 

[jillnoble]

I've just realised, its only one job number I need to exclude - 2018 - I've excluded it from the query which is fine and then the report. I just need to now exclude those weeks with less than 5 days. Not sure if I'm going about it the right way though but I am trying.

 

[MajP]

Not sure if I'm going about it the right way though but I am trying

I would think that is easy since the query has days worked for each week. DaysWorked > 4
But what do you want to do in the footer for a week with less than 5 days? Show nothing in the footer or exclude the week from the report all together?

 

[jillnoble]

I need to exclude that week altogether. So I need to run a report/query with days average of 52 full working weeks pay (Job 2018 isn't a working day so the whole week needs to be excluded) as from Jan 2021 onwards that's what they will get paid every time they take a leave day.

 

[MajP]

See query for report. You group on year, weeek number, employee. Or employee, year, week number
Since the values for the average are there already no need to do a calcualation. Just include the averages from the query in the footer.

 

[jillnoble]

Thanks again MajP, You'll have to start charging me!

Ran a very quick report and it's excluded weeks 5, 7, 8, 10 + 12 but then included week 15 which also includes job 2018? If I try and exclude that within the query it just messes all your good work up! Perhaps I've been staring at this screen too long today! (Too many other 'New' reports etc to do, I'm supposed to do accounts and payroll!) Maybe I need to look at it with fresh eyes tomorrow as I want to try and understand what you're doing too, not just copying, wont learn anything like that!