Calculated Fields in Query, based on Fields in other Rows

[briand2]

I use a table to record time spent on various projects. When I start working on a particular project, I add a row to the table, this row including a field showing the Date that I started. When I (temporarily) have to stop that project to work on another one, I add another row to the table, the Date on that row showing the date that I started work on that second project, etc, etc. I then return to the first project and add another row to show that I am working on it again, and so on.

At the moment, I move the table to Excel and use formulae that compare the dates on the first and second rows (and so on) to determine the duration of each task. In Excel, each row therefore contains a field (cell) that holds the duration of the task for which the start date is shown on that row.

I hope I've been reasonably clear!! Anyway, what I would like to do is perform the same calculations in Access as it is much better at producing reports, etc for the projects. I have read (and tried) KB article Q101081 "ACC: Referring to a Field in the Previous or Next Record", but that is specifically for calculating fields on FORMS. I have tried altering it to perform the same calculations on queries, but I can't get it to work.

Could someone please explain how I could achieve the results that I need?

Thanks in advance,

Brian

 

[sko]

Brian,
Why not have both a start time and a stop time on each record entered in the table?

tblTimeSheet
ProjectID (linked to tblProjects)
StartDate
StartTime
StopDate
StopTime

Enter as many start and stop times as you need for the duration of the project.


You could build a main form that displays the project with a subform that shows all the start stop times relating to that project. Use calculated fields on the form or report to show the time elapsed with grand totals for the project to date.

 

[briand2]

Sko,

Thanks. The reason I only have a START time against each task is that I use a handheld (Palm) to keep track of my work.

When I am about to START a particular task, I create a new record in my Palm database and that record automatically gets a date/time stamp. When I start another task, I do the same thing again. This means that the duration of each task is found by subtracting its START date/time from the START date/time of the next task.

At the moment, I upload the Palm data to a csv file and then I use VBA to read that file into Excel and perform various calculations, formatting, etc. However, my Palm database can upload (via a "conduit&quot directly to an Access table so that is what I want to do now (it will be much simpler). The trouble is that I will then have to carry out the calculation to determine the duration of each task within Access, based only on START date/time of task being held in a field on that task's row/record and STOP date/time of that task being the START date/time of the next task.

Any further comments would be gratefully received!!!

Thanks,

Brian