Referencing data from previous records

[JeepsR4Mud]

I'm designing a form where the user (my supervisor), would like to know the percent change from the previous week.

One of these areas is overtime.

I have a field, OTHours.

How can I reference the value form the previous week's weekly report so that the I can note the percentage change?

Also, to complicate it a bit further. We have 34 sites, so, it simply isn't a matter of referencing the previouis record. What needs to be referenced is the record for the previous week, belonging to a particular facility.

I'll need to do this in a record as well, but I'm assuming that if I can get the value to appear in a form, the procedure is similar.

Thanks so much.

Gayle Ann

 

[Dave Eyley]

Hi Gayle,

I think a possible solution to this is -

Have a table containing your facilities (say, called 'Facilities'"
Have a table with all the records (sa, called ('Records')

Create the form (say, called 'FormName')

Create 2 combos on a form
1. called 'Facilities' and,
2. called 'Records'

Make them cascading, which means let the data list shown in combo 2 depend on the selection of combo 1.

So, the RowSource in Combo 1 would be the table 'Facilities' and would list all of the facilities available.

In combo 2's Rowsource create a query using both tables make sure their linked in the query) and put the date ref field for the OT in the left hand column (with sort set to 'Descending'), put the facilitiy name (or code) in the next column and in the criteria put the combo 1's name i.e.

Forms!FormName!Facilities

When the user selects a facility and then clicks on the Records combo the user will see a list of dates in descending order. She then selects the date required and views the record.

You will have to decide how the recordd is to be viewed - if you choose to use a subform, then create the form and set the Master/Child properties to the linking field of combo 2 (Records) and the same field of the subform.

If you choose to open a new form, then put all the records fields on the new form and on the AfterUpdate property of the Records Combo use -

DoCmd.OpenForm "Form2",acNormal,,"[DateRecord]=Forms!FormName!Records"

Remember to requery Combo 2 if the user clicks again on Combo 1, to refresh the records to the new selection.

HTH Dave Eyley