Query for weeks, getting aggregate func. error

[MartyO]

Hi there,
I'm trying to do a simple calculation with dates (getting the number of
weeks in the school year that an employee is working), and then write the
number to a field in an update query... and I keep getting the aggregate
function error. Here is a sample,
UPDATE tblEmployee, SchoolDays SET tblEmployee.AttnBPBegBal =
DateDiff("ww",Min([SchoolDays]![Date]),Max([SchoolDays]![Date])-3)
WHERE (((tblEmployee.HireDate)<=#8/27/2007#));
and I get "You've tried to execute a query that does not include the
specified expression "AttnBPBegBal' as part of the aggregate function".

I tried also to create the field on the fly instead of an update query and
get the same error, but it always refers to the first field in the query grid
as the problem.

What am I missing?
Thanks in advance for your help!!
Marty

 

[Michel Walsh]

It seems SchoolDays is a query using GROUP BY, DISTINCT, or an aggregate
(SUM, COUNT, MIN, MAX, ... ) Such queries are NOT updateable.

It also seems that SchoolDays cannot run all by itself.


Vanderghast, Access MVP

 

[MartyO]

Hi Michel,

Actually SchoolDays is just a table with a record for each school day of the
year.

Marty

It seems SchoolDays is a query using GROUP BY, DISTINCT, or an aggregate
(SUM, COUNT, MIN, MAX, ... ) Such queries are NOT updateable.

It also seems that SchoolDays cannot run all by itself.


Vanderghast, Access MVP

Hi there,
I'm trying to do a simple calculation with dates (getting the number of
weeks in the school year that an employee is working), and then write the
number to a field in an update query... and I keep getting the aggregate
function error. Here is a sample,
UPDATE tblEmployee, SchoolDays SET tblEmployee.AttnBPBegBal =
DateDiff("ww",Min([SchoolDays]![Date]),Max([SchoolDays]![Date])-3)
WHERE (((tblEmployee.HireDate)<=#8/27/2007#));
and I get "You've tried to execute a query that does not include the
specified expression "AttnBPBegBal' as part of the aggregate function".

I tried also to create the field on the fly instead of an update query and
get the same error, but it always refers to the first field in the query
grid
as the problem.

What am I missing?
Thanks in advance for your help!!
Marty

 

[Michel Walsh]

Ah, indeed, the culprits are right there, in this query:
MIN(schoolday.date), MAX(schooldays.date)

Use DMIN("date", "schoolday") and DMAX("date", "schoolday") instead.



Vanderghast, Access MVP

Hi Michel,

Actually SchoolDays is just a table with a record for each school day of
the
year.

Marty

It seems SchoolDays is a query using GROUP BY, DISTINCT, or an aggregate
(SUM, COUNT, MIN, MAX, ... ) Such queries are NOT updateable.

It also seems that SchoolDays cannot run all by itself.


Vanderghast, Access MVP

Hi there,
I'm trying to do a simple calculation with dates (getting the number of
weeks in the school year that an employee is working), and then write
the
number to a field in an update query... and I keep getting the
aggregate
function error. Here is a sample,
UPDATE tblEmployee, SchoolDays SET tblEmployee.AttnBPBegBal =
DateDiff("ww",Min([SchoolDays]![Date]),Max([SchoolDays]![Date])-3)
WHERE (((tblEmployee.HireDate)<=#8/27/2007#));
and I get "You've tried to execute a query that does not include the
specified expression "AttnBPBegBal' as part of the aggregate function".

I tried also to create the field on the fly instead of an update query
and
get the same error, but it always refers to the first field in the
query
grid
as the problem.

What am I missing?
Thanks in advance for your help!!
Marty

 

[MartyO]

Michel,

Duh!!! I'm sorry I just read this again and realized what you were saying.
Because I'm using the MIN and MAX function, I can't do an update. Can I do it
with visual basic or will I run in to the same issue with a recordset?

Thanks!
Marty

Hi Michel,

Actually SchoolDays is just a table with a record for each school day of the
year.

Marty

It seems SchoolDays is a query using GROUP BY, DISTINCT, or an aggregate
(SUM, COUNT, MIN, MAX, ... ) Such queries are NOT updateable.

It also seems that SchoolDays cannot run all by itself.


Vanderghast, Access MVP

Hi there,
I'm trying to do a simple calculation with dates (getting the number of
weeks in the school year that an employee is working), and then write the
number to a field in an update query... and I keep getting the aggregate
function error. Here is a sample,
UPDATE tblEmployee, SchoolDays SET tblEmployee.AttnBPBegBal =
DateDiff("ww",Min([SchoolDays]![Date]),Max([SchoolDays]![Date])-3)
WHERE (((tblEmployee.HireDate)<=#8/27/2007#));
and I get "You've tried to execute a query that does not include the
specified expression "AttnBPBegBal' as part of the aggregate function".

I tried also to create the field on the fly instead of an update query and
get the same error, but it always refers to the first field in the query
grid
as the problem.

What am I missing?
Thanks in advance for your help!!
Marty

 

[Michel Walsh]

The easiest way will be to use DMin and DMax VBA domain functions, if
possible.

Sure, you can also loop over a recordset and set the fields value with a
scalar value coming from whatever 'tool' you can reach from VBA, but that
sounds more a plan B than a plan A.


Vanderghast, Access MVP

Michel,

Duh!!! I'm sorry I just read this again and realized what you were saying.
Because I'm using the MIN and MAX function, I can't do an update. Can I do
it
with visual basic or will I run in to the same issue with a recordset?

Thanks!
Marty

Hi Michel,

Actually SchoolDays is just a table with a record for each school day of
the
year.

Marty

It seems SchoolDays is a query using GROUP BY, DISTINCT, or an
aggregate
(SUM, COUNT, MIN, MAX, ... ) Such queries are NOT updateable.

It also seems that SchoolDays cannot run all by itself.


Vanderghast, Access MVP


Hi there,
I'm trying to do a simple calculation with dates (getting the number
of
weeks in the school year that an employee is working), and then write
the
number to a field in an update query... and I keep getting the
aggregate
function error. Here is a sample,
UPDATE tblEmployee, SchoolDays SET tblEmployee.AttnBPBegBal =
DateDiff("ww",Min([SchoolDays]![Date]),Max([SchoolDays]![Date])-3)
WHERE (((tblEmployee.HireDate)<=#8/27/2007#));
and I get "You've tried to execute a query that does not include the
specified expression "AttnBPBegBal' as part of the aggregate
function".

I tried also to create the field on the fly instead of an update
query and
get the same error, but it always refers to the first field in the
query
grid
as the problem.

What am I missing?
Thanks in advance for your help!!
Marty