IIf question

I will try this lookup and see what happens.

The purpose of the time field is to store a constant value. Each physician has an alotted time available (the constant) and I want to pull that value to do math to calculate % utilization.
 
That works ... now I need to figure out how to get the correct number(s) format.

Let nme know what you think from the previous post.
 
...now I need to figure out how to get the correct number(s) format.

Then I need to figure out what you're trying to do.

What does the 90, for example, represent? Total minutes allowed? Total hours?

And you're trying to compare that to what? The total anesthesia time per doctor per month as calculated by the report?
 
Each physician has so many hours per month. The 90 was an arbitrary number. Total TPC (Total procedure time) is the sum of the individual events. Te goal is to show their % utilizzation of their time. There are other variables to be subtracted but I thought once I figured out the formula to get the hours available on the form I could get the rest and simly do the division.
 
To continue ... it would seem smple to just subtract the time values. This doesn't work however because short time format does not allow more that 24 hours. In most caes the total hours will be between 48 and 72 hours.

Is there an answer to this??
 
Is there an answer to this??

Yes. Don't format it as Short Time. What you need to do is return the total number of minutes then divide by 60 to get the hours as a number. You can do this in the query with a calculated field like;

TPC: DateDiff("n", [Anesthesia Start Time], [Anesthesia End Time])/60

Then in your report you sum this field in a text box in the footer;

=Sum(TPC)

Then you divide that value by the allowed hours from your Physicians table to get the percentage of allowed time used. I have re-attached your db with those modifications.
 

Attachments

Okay I will look at what you did and be back with any additonal questions.

Thanks
 
I believe I understand what you did?

Can you refresh me on the dateDiff and the "n" variable?

Do I need to be concerned about the values that show for time in the query ... not being in a shorter format? I will need to add a discriptor to the TCP field in the report so they will understand why the math (subtaction) looks wrong.

Dan
 
Can you refresh me on the dateDiff and the "n" variable?

DateDiff returns the difference between two Date/Time values. The interval argument for Minutes is "n".

Do I need to be concerned about the values that show for time in the query ... not being in a shorter format?

I personally wouldn't worry about it because the query is not the final output, just the Record Source, but if it's an issue for you you can format that field as a standard number with 2 decimal places. Do this in the properties for that field in the query design.

I will need to add a discriptor to the TCP field in the report so they will understand why the math (subtaction) looks wrong.

Not sure what you mean by the math looks wrong. If you mean how 1 hour 30 minutes is 1.5 in the TPC field, you could format that back to a "hh:mm" display with an expression like the following in the TPC text box in the detail section of the report;

=Format(Int([TPC]),"00") & ":" & Format((([TPC]*60) Mod 60),"00")

so 1.5 becomes 01:30
 
Thanks for the explanation and the suggestion for changing the format. I will try it later and let you know if anthing unexpected happens.

thanks again for your help.
 
Nothing unexpected at this point ... I have two other variables that need to be accounted for; 1st is vaction hours taken in any given month and 2nd is Management requested time off in any given month.

What would be the best way to deal with this? I was thinking a seperate table? Would vid be the one to many key still?
 
Another question ... If I use the "vid" as the foreign key, can I then use Visitdate as a second join to the new table with a date field?
 
Can anyone help with this???

I am having a brain cramp but here it goes.

I need to add another table that has a date field (as well as others) and store dates seperate from the Visits table, but yet pull the data from the other tables via queries. I thought making the relationship from visit date to the date field in the new table would work but when preview the queris they are empty and when I remove the relationship the original data appears. (I have loaded no data into the new table yet):banghead:

Thanks
 

Attachments

I'm not sure what you're trying to accomplish here with these last few questions. Are you tracking vacation time for doctors? What is "Management requested time off"? What do either of these have to do with the Visits table?
 

Users who are viewing this thread

Back
Top Bottom