Re: Loop query

From: Chris Mills (phad_nospam_at_cleardotnet.nz)
Date: 07/20/04


Date: Tue, 20 Jul 2021 14:45:55 +1200

That's a bit difficult to follow entirely, but not to worry.

> Do I need to rethink the enitre database,
No (generally). The database tables should store just the basic data, what to
do with that data is a separate matter!

> I have this calculating in a query
Well, if standard query and Access facilities don't do what you want, write
your own function! You can write your own function, lets call it CalcAbsence,
in VBA code in a global module, then you can call it from any query. Your
function can then do whatever it likes, control rounding, lookup history to
see if that matters, whatever you can do with code really. Though it might be
possible to do with an "expression", that tends to make a query look awefully
complex. It is, of course, but better to encapsulate your fancy rules and have
much better control, by placing YOUR function in a callable code function. It
might be a bit slow depending, but that's life.

> is there someway to loop through the query to calculate off a total of
absences instead of each current line?
Yes. Your said function can open a recordset independently and loop through
it. Or it can open another query independently. Or, in some cases, you can
refer directly to another query, for instance in the criteria line of a query
you could write "Select Max(This) From That Table/Query".

Chris

"neen" <neen@discussions.microsoft.com> wrote in message
news:C96ECC1E-7FFC-4749-B574-4D29C2337B4E@microsoft.com...
> I have a database that keeps employee attendance. Absences can be 1 day or
1/2 day. Absences drop off after one year and if an employee does not miss
any time in 90 days, they can earn one back so 1 absence drops off. But, they
cannot bank days ( they only drop an absence if they currently have any
accumulated)
>
> I have this calculating in a query and it has been working fine for quite
sometime by calculating ... The new absence, less any dropoff amount by the
"next date missed" with the total less than zero = zero, else the total.
>
> The problem I have just run into is as follows: If the new absence is 1/2
and there is a drop off of 1, the total is a negative 1/2 (can't bank time so
= 0) - But there are other absences, so this is acutually cheating the
employee out of 1/2 an drop off.
>
> Do I need to rethink the enitre database, or is there someway to loop
through the query to calculate off a total of absences instead of each current
line?
>