Previous records info into different field in next record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help - Help - Help

I have a report to run using a date field. I need to compare the persons last absent date with the next date he gets an absent/tardy mark which starts the cycle all over again. If there has been 31 days lapsed without an absent/tardy they get to drop a point. I will be using a query to get the information from since there will be 200+ people to track. The query would allow me to have the manager run a report for a certain person for detail , and hopefully I can figure out a report that would just show bottom line totals for everyone.

My thoughs on the report would be

Date Occurance Last Date Point Val Days lapsed Reward Point
2/17/04 Absent 1
3/ 22/04 Absent 2/17/04 1 34 -1
3/26/04 Tardy 3/26/04 .5 4 0

Totals 1.5


Is this possible?

I am not very good at VB code
I see other example and try to copy
But I don't always know when I am supposed to change some of the text in the code to reflect my field names, form names, etc.

Example KeyName
KeyValue
F.RecordsetClone
RS =

I have tried to look at some books but they all seem to take for granted that you already know a lot of this stuff.

Trying to Learn --- Thanks
KentuckyDiane
 
KentuckyDiane said:
I have a report to run using a date field. I need to compare the persons last absent date with the next date he gets an absent/tardy mark which starts the cycle all over again. If there has been 31 days lapsed without an absent/tardy they get to drop a point. I will be using a query to get the information from since there will be 200+ people to track. The query would allow me to have the manager run a report for a certain person for detail , and hopefully I can figure out a report that would just show bottom line totals for everyone.

My thoughs on the report would be

Date Occurance Last Date Point Val Days lapsed Reward Point
2/17/04 Absent 1
3/ 22/04 Absent 2/17/04 1 34 -1
3/26/04 Tardy 3/26/04 .5 4 0

Totals 1.5
Click to expand...


It's not really feasible to keep track of a value from one
report detail to another, but you can get the previous date
by looking it up in the table. Try setting the LastDate
text box's expression to something like:

=DMax("occurancefield", "thetable", "occurancefield < #" &
occurancetextbox & "#")

The days lapsed text box can then use the expression:

=DateDiff("d", lastdatetextbox, occurancetextbox)

And the Reward text box can use this expression:

=IIf(dayslapsedtextbox > 31, 1, 0)

It'll get more elaborate when you try to mix the tardy days
into the equation, but work out this part first so you can
get a feel for how it works.
 
Marshall, this is working great, BUT, as I add more employees than one to my table, the dates start getting interchange. I have tried several different sort senerios, but nothing works. Do you have any suggestions??????

My table would look like this--------

OccID Date Name
1 1/23/04 Diane
2 2/16/04 Diane
3 3/18/04 Diane
4 7/18/04 Diane
5 6/2/04 Chiara
6 6/13/04 Chiara
7 7/23/04 Chiara

But when I run the report This is what it looks like

Date Last Date
Chiara 6/2/04 3/18/04
Chiara 6/13/04 6/2/04
Chiara 7/23/04 7/18/04

Diane 1/23/04
Diane 2/16/04 1/23/04
Diane 3/18/04 2/16/04
Diane 7/18/04 6/13/04

IT SHOULD LOOK LIKE THIS

Chiara 6/2/04
Chiara 6/13/04 6/2/04
Chiara 7/23/04 6/13/04

Diane 1/23/04
Diane 2/16/04 1/23/04
Diane 3/18/04 2/16/04
Diane 7/18/04 3/18/04

ANY IDEAS - THANKS for your help. THis is the closes I have come.
Diane in Kentucky
 
KentuckyDiane said:
Marshall, this is working great, BUT, as I add more employees than one to my table, the dates start getting interchange. I have tried several different sort senerios, but nothing works. Do you have any suggestions??????

My table would look like this--------

OccID Date Name
1 1/23/04 Diane
2 2/16/04 Diane
3 3/18/04 Diane
4 7/18/04 Diane
5 6/2/04 Chiara
6 6/13/04 Chiara
7 7/23/04 Chiara

But when I run the report This is what it looks like

Date Last Date
Chiara 6/2/04 3/18/04
Chiara 6/13/04 6/2/04
Chiara 7/23/04 7/18/04

Diane 1/23/04
Diane 2/16/04 1/23/04
Diane 3/18/04 2/16/04
Diane 7/18/04 6/13/04

IT SHOULD LOOK LIKE THIS

Chiara 6/2/04
Chiara 6/13/04 6/2/04
Chiara 7/23/04 6/13/04

Diane 1/23/04
Diane 2/16/04 1/23/04
Diane 3/18/04 2/16/04
Diane 7/18/04 3/18/04
Click to expand...


This requires that you include the name field in the Dmax's
where argument:

=DMax("occurancefield", "thetable", "occurancefield < #" &
occurancetextbox & "# AND [namefield] = """ & nametextbox &
"""" )
 
Back
Top