Re: Loop query

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

  • Next message: neenmarie: "Re: Loop query"
    Date: Wed, 28 Jul 2021 06:50:01 -0700
    

    Thx for you reply. It sounds like what I need, but is beyond me. I've only worked with expressions in a query, not modules and don't know VBA. Can you help me more?

    >From what you've written, it sounds like I need VBA to put in a module for the following:
    1) a recordset in a module that gives me [employee SocNumber],[absence],[date of absence] where [date of absence]+365 >Date() ..... (To give me only absences within the last year - year old absences are not counted)
    2) a recordset in a module that gives me the sum on the above absences by SS#
    3) I then need to take care off 90 day drop-offs while keeping a running balance, so...
    3A) Can I find the next absence date (next record) per SS#? To be able to compare each current record's absence date to the next record's absence date (by SS#) to be able to say something like "If [AbsenceDate] + 90 < [NextAbsenceDate],-1,0"
    (This would allow me to credit the employee with an absence for going 90 or more days without missing any time)
    3B) If there is no "NextAbsence", I would need something like..."If [AbsenceDate] + 90 < Date() or Now(), -1,0".

    I hope that all makes sense. And thank you so much 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?
    > >
    >
    >
    >


  • Next message: neenmarie: "Re: Loop query"