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
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 LambertFabrice 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
NorieAnalyst 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
bfuchsAuthor 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

Open in new window

See attached what I'm getting.

Also what do I put in the conditional formatting logic?
Untitled.png
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bfuchsAuthor 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
NorieAnalyst 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
bfuchsAuthor 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
Gustav BrockCIOCommented:
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)

Open in new window

/gustav
0
bfuchsAuthor Commented:
@Gustav,

Still getting errors, see attached.

And what about the logic?

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

Thanks,
Ben
Untitled.png
0
bfuchsAuthor 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)

Open in new window

And then at the conditional formatting I put in
[MissingNotes]>0

Open in new window


Thanks,
Ben
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Gustav BrockCIOCommented:
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
bfuchsAuthor Commented:
@Gustav,

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

Thanks,
Ben
Untitled.png
0
bfuchsAuthor 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.