Re: Loop query

From: neenmarie (neenmarie_at_discussions.microsoft.com)
Date: 07/28/04


Date: Wed, 28 Jul 2021 07:17:03 -0700

Thx for you reply. I've only written expressions in queries - and have not used modules or really written VBA. Can you help me more?

>From what you've written it sounds like I need several recordsets in a module.

1) a recordset that gives me "[EmployeeSSNumber],[Absence],[AbsenceDate] By SSNumber, Where [AbsenceDate]+365 < Date()" ....To omit any absence over a year old.
2) the sum of the above record set by SSNumber
3) To credit employees back with an absence if they have not missed any time within 90 days I need to be able to compare a record to the next record with the same SSNumber ....
3A) If [AbsenceDate]+90 < [NextRecordAbsenceDate],-1,0
3B) If there is no "NextRecordAbsenceDate" - If[AbsenceDate]+90 < Date(), -1,0
4) Here comes my real problem. Employees cannot "bank" credits - if their total absences equal 1/2 or 0 at the 90 day drop off instance of 3A or 3B, their total cannot be less than zero. I need each record to total from the previous record to give them what they've earned without dropping below zero. If I don't have a running balance, I can cheat someone out of 1/2 a dropoff.

Thank you for your help. Janine

"Chris Mills" wrote:

> 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?
> >
>
>
>