Field to Calculate Time Elapsed

[TTime]

I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard

 

[ScottGem]

First we don't store calculated values. Second, I don't understand your database, you only have a Time In not a Time Out. I also couldn't find your Calc code.

Calculating elapsed time is easy:

DateDiff("n",TimeIn,timeOut) will give you the time in minutes. You then divide by 60 to get hours and use the Mod function to get remaining minutes.

 

[TTime]

Scott, Hi. Hope you are well. I've tried to adapt your sugestion - see attached. Are the queries correct in design - any solutions as to why the Machine Downtime produces negative figures? If this is OK how do I incorporate the query with the source tbl in a single form so all details relating to each call can be viewed on a single screen? e.g. Cretae a form based on the qry and attach it as a subform? Thank you for helping me out once again, it's very much appreciated. Regards, bernard

 

[ScottGem]

No attachment!

 

[TTime]

Scott, Sorry - our network went AWOL for a while. I think I have worked-out how to apply the query(s) to reflect the associated records. Would you please take a look at the attached and let me know if there are any major improvements I need to make (other than cosmetic ones)? Regards, Bernard

 

[ScottGem]

Several things.

1) You don't need a CallPlacedDay field. That can be extracted from the CallDate. That also means you can eliminate your Days table.

2) It looks like you have several lookup tables. These should be named with a tlu prefix to designate them. Its also not recommended to create lookup fields on the table level. This should be done on the form level only. Also the way you are using them doesn't require setting relations. The only reason to set relations is if you need to get more info from the related table.

3) People's names should be broken out into at least first and last. So your Team Leaders table needs work. This also means you should use an autonumber field for the primary key for that table. Then store the PK value as a Foreign key in the Incidents table.

4) Your IncidentLog should be broken out into the Incident Log and an Action Log. The Incident log should show only the info about the initial call. You should then have an ActionLog that details each action taken with respect to an incident.

 

[TTime]

Scott, Thank you for taking the time to provide me with all these tips. I shall work on the database accordingly. Kind regards, Bernard