comparing UserID and JobDate with previous record and populate blank collumns

[handyman1]

Can anybody help me
I have searched the forum but cannot work out how to solve my problem

2 years ago I created a database (with lots of help from this forum) that records customer and job details for a Free Handyman Service. The service has unexpectedly incresased fivefold in the last couple of months and the new funders require info on numbers of clients, visits, tasks and individual jobs done

The old jobs table was never set out to record this info but does record the following which can be used to work out visits and number of tasks done for each user

Sample of data JobId is primary key (table sorted UserID then JobDate)


JobID JobDate UserID JobType Job Description Items HandyMan
1 1/1/2005 100 Plumbing Tap Washer 2 PM
2 1/1/2005 100 Safety Smoke Alarm 2 PM
3 5/3/2005 100 House Change Curtains 4 PM
4 4/7/2005 200 Safety Smoke Alarm 1 PM
5 5/7/2005 200 Plumbing Tap Washer 1 PM
6 5/7/2005 200 Security Door Lock 1 PM
7 9/8/2005 200 Carpet Door Thresh 4 PM
8 5/9/2005 305 Safety Smoke Alarm 2 PM
9 5/9/2005 420 Plumbing Tap Washer 1 PM
10 6/1/2006 420 Safety CO Alarm 1 PM


There are present 10500 jobs in Jobs Table and 3000 Users in the customer table
I need to automatically populate 2 new collumns VisitCount and TaskCount

I think I have the right idea but can't but it into code

add columns VisitCount and TaskCount and SortOrderNumber to Jobs Table

sort Jobs Table (UserID ascending, JobDate ascending) and set SortOrderNumber to be the new primary key and fill it with autonumber (this will keep the recordset in the order that I want to compare the rows)

create RecordSet of Jobs table (use as current row) CUR
create Clone of RecordSet of Jobs Table (use as previous row)PRE

MoveFirst of CUR Make VisitCount =1 and TaskCount = 1
For Next loop or Do Until EOF
MoveNext of CUR
if CUR UserID = PRE UserID then Call sub SameUser
else CUR(VisitCount)=1, CUR (TaskCount)=1
MoveNext of Cur
MoveNext of Pre
Loop or Next
End
Sub SameUser
If CUR(JobDate) = PRE(JobDate) then
CUR(VisitCount) = PRE(VisitCount)
CUR(TaskCount) = PRE(TaskCount)+1
Else CUR(VisitCount)= PRE(VisitCount)+1
CUR(TaskCount)=1
End Sub


I think if coded this would loop through each row and compare the Current UserID with the Previous UserId and if they are the same then compare the Current JobDate with the Previous JobDate to check if the User had more than one Task done on the same day; if yes the keep the VisitCount the same and add one to the TaskCount. If not the same reset the TaskCount to 1 and add 1 to the VisitCount.
If the Current UserID is different from the Previous UserId then reset both Current TaskCount And VisitCount to 1
loop through until end of file

How do I code this ? I would be most grateful for any advise or constructive criticism

Handyman1

 

[boblarson]

You do NOT need to store this information. You should be able to pull together the counts via a query to display at any time. Storing calculated values, especially of this type, flies against all that a relational database system is designed for.

 

[handyman1]

Sorry I missed out that the jobs table needs to be improved in order for it to be used to handle new fields like visit , tasks, time taken, travel time, etc
fields that are being used for jobs entered in to a new table since start of april, but I am trying to use the calculated fields to populate the vist and task counts fields in order to align the old and new jobs table as much as possible so that I use the old data as a comparison to the new data year on year

How do I use a query to loop through the grouped userId and sorted jobdate in order to enter data into visitcount and taskcount