using the results of a query as criteria within a update query

[Gint3232]

I am having difficulty attempting to update all records in a table that match the value -1 in a field named [Hours] with the average value to that to that same field.
At the moment the sql view shows the following

UPDATE tbl_Staff SET tbl_Staff.hours = Avg([hours])
WHERE (((tbl__Staff.hours)=-1));

but when I try to run it throws an error stating ..You tried to execute a query that does not include the specified expression 'hours' as part of an aggregate function(what ever that means)
Can anyone suggest the correct sql to accomplish this or explain what and why it may be not running
I also have a created a separate query,

SELECT Avg([hours]) AS AverageHours
FROM Tbl_staff

but not sure if or how to use the two queries together if at all possible!

Many thanks to everyone for taking the time to read!

 

[plog]

I would use DAvg (https://www.techonthenet.com/access/functions/domain/davg.php) to get the average and use it in an UPDATE query. This SQL will do what you say you want to do:

UPDATE tbl_staff SET tbl_staff.hours = DAvg("hours","tbl_staff")
WHERE (((tbl_staff.hours)=-1));

However, I don't think its what you actually want to do. You are including all those -1 values in the Average which brings it down signficiantly:

hours
-1
15
15
15

That average is 11 (44 / 4). If you exclude the -1 the average is 15 (45 /3). To omit the -1 values from the average you need to add criteria to the DAvg. Consult the documentation I provided for that.

 

[RogerCooper]

The 'aggregate" error is result of using the same field name "hours" in 2 different spots in way that confuses Access. Separating this into 2 queries would work if this was a select query but it will not work for an update.

You will need to create a query that averages 'hours' in 'Tbl_staff' excluding 'hours' of -1. This query will have only 1 record as output. Then you can use the dlookup function to get value in the update query.

 

[Gint3232]

The 'aggregate" error is result of using the same field name "hours" in 2 different spots in way that confuses Access. Separating this into 2 queries would work if this was a select query but it will not work for an update.

You will need to create a query that averages 'hours' in 'Tbl_staff' excluding 'hours' of -1. This query will have only 1 record as output. Then you can use the dlookup function to get value in the update query.

Thanks, as I said in my OP I have already created a query that averages 'hours' within 'Tbl_staff', I can easily amend to excluding 'hours' of -1, but not sure of the rest your suggestion ..meaning how to incorporate a Dlookup function into the same query!

 

[RogerCooper]

You would have the update value be something like this

dlookup("[Average Hours]","Qry_staff Averages")

Plog's suggestion is similar to mine, but he skips the separate query by using davg.

 

[Gint3232]

You would have the update value be something like this

dlookup("[Average Hours]","Qry_staff Averages")

Plog's suggestion is similar to mine, but he skips the separate query by using davg.

Thanks for your patience, I amended the sql to reflect what I think was your suggestion. as follows:

UPDATE tbl_staff SET tbl_staff.hours = DLookUp("[hours]","Qry_AverageHours")
WHERE (((tbl_staff.hours)=-1));

note Qry_AverageHours now filters the -1 out
I now get an error message "unknown"

 

[RogerCooper]

Try giving "hours" in "Qry_AverageHours" a different name.

 

[Pat Hartman]

When you create totals queries, Access renamed the aggregated data so
Avg(Hours) becomes AvgHours. So, unless you specifically assigned an alias, Access picks the name. Run the query and check the names.

 

[Gint3232]

When you create totals queries, Access renamed the aggregated data so
Avg(Hours) becomes AvgHours. So, unless you specifically assigned an alias, Access picks the name. Run the query and check the names.

Thanks for your suggestion, Yeh! I already had an alias within the query qry AverageHours, I just realized that none of these averages even if I got them to run would be a close to true and fair representation of averages, So I am thinking the best possible way would to
1. divide/split the whole range of figures into 0-99hrs, 100-199hrs,200-299hrs etc etc.
2 . Get a total count of each in each range.
3 get which ever range has the the top most figure(highest value),
4 Now get the average (not sure which average to use though) figure within whatever range had the highest count and then whatever that figure is would the closest value to the a true average.

The whole reason for these calculations are for when we have 20 or 30 new staff every couple of months that may transfer over into our dept mid financial season, These new comers will be each be allocated the average O/T hours taken from our existing staff records. So it has to be fair as there are a just few individuals the really go overboard on overtime and because of those few our conventional way of calculating averages just aren't fair enough.. So I am attempting to get the fairest way of getting a nearest to the true average possible.
Please help me with this if you can

So I'll try and explain where I coming from with an example:
If I said to someone I'll sell you my house for whatever the average sold price was price in a large housing estate, you might be happy at that thinking It'll be a fair price, but if 198 out of two hundred all sold for say 100:00 dollars and say two houses sold for 10,000:00 then I'd be expecting you to sell me the house for 100:00 and not a cent more(mode average), but if I calculate this using my bog standard (Mean) average, It'll actually calculate to nearly 200:00, which is of course not fair selling price and you would probably tell me to forget it and keep your money correct!
So we could calculate using (mode) method if the figures I gave were a true reflection, But, we all know figures are not that straight forward to work with (as the majority of houses would not sell for the exact same amount in the real world).
So taking the same example ,I think I would get the fair amount if I split the the sales figures into ranges 0-99, 100-199 etc!, then the range that had the highest "Count" as in within each range which had the most sales hits, then this would be the range that I worked an average on to gain a truer average, a fairer average, As in the case above I'd be selling for 100:00 which is and would be a fair price ,Yeh!

So back to the drawing board this is what I really need to achieve if it possible, but I need some directional help with it, as I am not great with MS Access / Group queries or what ever I need to do.
Does any one agree with me or am I just plain wrong! with this?

 

[Gint3232]

Try giving "hours" in "Qry_AverageHours" a different name.

Hi Roger,

I think I have found the fairest way to do this maths wise, but vba group query wise Not sure!,
https://gcseguide.co.uk/maths/statistics/average/
As below is the fairest example in my given situation

When you are given data which has been grouped, the mean is Sfx / Sf , where f is the frequency and x is the midpoint of the group (S means ‘the sum of’). Example: Work out an estimate for the mean height.
Height (cm) Number of People (f) Midpoint (x) fx (f multiplied by x)
101-120 1 110.5 110.5
121-130 3 125.5 376.5
131-140 5 135.5 677.5
141-150 7 145.5 1018.5
151-160 4 155.5 622
161-170 2 165.5 331
171-190 1 180.5 180.5
Sfx = 3316.5
Sf = 23
mean = 3316.5/23 = 144cm (3s.f.)


Any ideas as to how to implement this, I be forever grateful.

 

[jdraw]

These new comers will be each be allocated the average O/T hours taken from our existing staff records. So it has to be fair as there are a just few individuals the really go overboard on overtime and because of those few our conventional way of calculating averages just aren't fair enough.. So I am attempting to get the fairest way of getting a nearest to the true average possible.

So after reading this several times, I am left wondering what it means in simple terms.
Your org has work that always requires O/T. You want all employees to get an equal share of such O/T?
My experience suggests that many orgs want to minimize O/T costs. And in many orgs there are the keeners who want every second of O/T they can get; and those that want nothing to do with O/T.
O/T is often assigned based on a seniority basis (unionized worker approach);
or assigned based on lowest wage earner basis (management minimal cost approach).

Good luck with your project.

 

[Gint3232]

Your org has work that always requires O/T.
You want all employees to get an equal share of such O/T?
Many Orgs want to minimize O/T costs, ours included,
In many orgs there are the keeners who want every second of O/T they can get; and those that want nothing to do with O/T.

Yes correct on all of the above, but not on the rest

So no, not ..O/T is often assigned based on a seniority basis (unionized worker approach);
or assigned based on lowest wage earner basis (management minimal cost approach).

Our Org, bases allocation on the person with the least amount of accrued hours, gets asked first. So for that reason its crucial that new comers get allocated a fair average if they come in mid-end financial period, and are not not affected(given an unusually higher figure) by as you rightly put it a small minority of Keeners!(which throws the conventional way of Calc Aver' to an unfair balance!!

I am thinking the only way I know to achieve this is to create individual queries for each group of figures then work form there, although I am hoping someone with experience can tell me that theirs an easier way through VBA. Dlookup and Dcount which ever would run faster
Can anyone help me solve this Dilemma it'll make a few individuals unhappy but a lot more staff happy.

 

[Gint3232]

Thanks, everyone for all your input I did exactly as in the link
here and therefore now have working true average.

 

[RogerCooper]

So it has to be fair as there are a just few individuals the really go overboard on overtime and because of those few our conventional way of calculating averages just aren't fair enough.. So I am attempting to get the fairest way of getting a nearest to the true average possible.
Please help me with this if you can

A median would make more sense than a mean for this situation.