# Need to calculate two fields, notes vs. hours.

Hi experts,

I have two fields in a query, one for total minutes of a shift  (Duration) and one displaying total count of notes related (CountOfNotes).

For every two hours there must be a note.

Now I need to display in bold (conditional formatting) every record that has less notes than required.

How do I accomplish that in a non performance cost fashion?
LVL 6
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertCommented:
Since you didn't mention how you'll display your result, you can export your datas to Excel, and use Excel's conditional formatting.
0
Analyst Assistant Commented:
You could get the notes per hour with this expression:

NotesPerHour:[CountOfNotes]/[Duration]*60

That could be used in conditional formatting but unfortunately not in a query, you would need to do that in a form/report based off the query.
0
Author Commented:
Hi experts,

unfortunately not in a query, you would need to do that in a form/report based off the query
Yes, I have a form bound to that query, excuse me for leaving that out.

I tried yours as follows
``````NotesPerHour: DCount("*","SNVNotesQry","SNV_ID = '" & [SNV_ID] & "'")/Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))*60
``````
See attached what I'm getting.

Also what do I put in the conditional formatting logic?
Untitled.png
0
Author Commented:
Just to clarify the logic needed in formatting.

If there are shifts for less then two hours then no note are required.
Same goes if shift are 5 hours, only 2 notes are required. (for the two sets of 2 hours each)
In other words, only two hours counts.

Thanks,
Ben
0
Analyst Assistant Commented:
Ben

For the errors you'll need to check for 0 values being returned by the 'duration' calculation.

Apart from that the results are showing the no of notes per hour.

Not quite following your logic for the formatting.
0
Author Commented:
Hi,

For the errors you'll need to check for 0 values being returned by the 'duration' calculation.
The duration should either has some positive numbers or zero (as seen in attached).
Not quite following your logic for the formatting
I basically need to make sure each two hours shift has a note record.

Therefore for 4 hours I need two notes, same is for 5 hours, but for 6 hours I already need 3 notes.
For 1 hour no no needed, same for 1.5, but when there are already 2 hours one note record is required.

Hope that's clear.

Thanks,
Ben
0
CIOCommented:
Try with:

``````NotesPerHour: Int(DCount("*","SNVNotesQry","SNV_ID = '" & [SNV_ID] & "'")/Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour],0),Nz([Shift_From_Minute],0),0),TimeSerial(Nz([Shift_To_Hour],0),Nz([Shift_To_Minute],0),0)))*60)
``````
/gustav
0
Author Commented:
@Gustav,

Still getting errors, see attached.

I mainly need a yes/no field in my query indicating if missing notes.

Thanks,
Ben
Untitled.png
0
Author Commented:
Hi Experts,

I got it to work as follows
``````MissingNotes: (Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))/120)-nz(DCount("*","SNVNotesQry","SNV_ID = '" & [SNV_ID] & "'"),0)
``````
And then at the conditional formatting I put in
``````[MissingNotes]>0
``````

Thanks,
Ben
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

CIOCommented:
If that works, I would say that you can skip all the Nz stuff, as Nz([Shift_From_Hour])  will return an empty string for Null values, and an empty string is not an accepted parameter value for TimeSerial , thus it should fail.

/gustav
0
Author Commented:
@Gustav,

Tried removing just the first instance of nz() and already got some errors.
See attached last column.

Thanks,
Ben
Untitled.png
0
Author Commented:
Problem solved.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.