Calculation and IIF advice please

[Zoedith]

I am trying to do a report for oilfield jobs, using access 2007. My database is designed with a job information table and a job run table. Each job has at least one run. I have a query that asks for the job number and links the apropriate job information and the runs associated with it. My report will show this run data for the customer to see why a run was ended (failures or normal rig operation). The sample would loook similar to this:

Run, DD Failure, Begin Time, End Time, Total Time, and Down Time.

I was able to calculate the total time with this code =Int(DateDiff("n",[DDBeginDrilling],[DDEndDrilling])/60) & "h" & DateDiff("n",[DDBeginDrilling],[DDEndDrilling]) Mod 60 & "m"

However, I am not sure if I am even doing this down time the "right" way. We would like to tell the customer the amount of time it took between the end time of the current run to the begin time of the next run.

I think I need to do an IIF statement that states, If the DD failure is yes then down time should equal Begin time (from next run)-End time(from current run). I do not know how to tell the report the begin time is from the next run (this could just be a brain freeze on my end).

I am wondering if it is wise to put this calculation in the report or if i need to build a query.

 

[lark_tilly]

I would do it by adding code to the Detail_Format event in the report.
It would save the End Date from the previous record and use it in the calculation for the current record. I don't how you'd do it with a query.
Are you running a new report for each job or are all the jobs on one report grouped by job or how is it set up?

 

[Zoedith]

more info....

It could be both. I have it set up to ask for a specific job number. If they put in a partial number (I might remove the partial ability now that I think about it) it lists that job followed by the runs and then if there is a similar job number it will show the information and runs for that job. The purpose is for that person to know that specific job number and pull only that job number, this report is to go in the end of the well folder.

The request I received to calculate down time, would normally be included with the run that had the failure. No failure, no down time reported by us. So I am still trying to figure out if it is possible to make a calculation with the begining time of the future run.

In rare cases there could be a failure and not have another run. In that case no down time would be calculated because we would have been removed from the rig, hence no next run.

 

[lark_tilly]

The attached has a combo box. Pick a job and the report will print.
If you look at the code for the report, you'll see how it calculates the
down time and how it keeps track of whether or not to calc a down time value. If you're not familiar with code, then open the report in design view,
go to the View menu and click Code. That will display the code page for the report. What happens is that every time a line in the detail is formatted, the code executes and affects the values for that line.

 

[Zoedith]

I am getting a "you are trying to open a read only database" error when I try to go look. Is there anything I am doing wrong?


*edit* I did figure out how to view it, manipulating it now.

 

[The_Doc_Man]

This cannot be done in a query - at least not without a LOT of gyrations.

Search this forum for "Previous Record" to see lots of posts on why for queries, there IS no previous record.

Doing it in the context of a report, particularly with the suggestion to hold the "previous" time in some VBA slots from one record to the next, is highly possible. VBA can track things that are syntactically not meaningful in SQL.