Query data not correct, calculation for some records incorrect

[MushroomKing]

Hello everyone. Im new here

I wonder if anyone could help me out with some issues i have on my query.

My query has 2 time fields. I calculate the difference between the time in minutes. The data seems correct, BUT not for EVERY record. I cannot place where it goes wrong or how it gets that number.

Can anyone help? Many thanks!

SELECT STATS_master_query.employee_ID, STATS_master_query.intime, STATS_master_query.outtime, Sum(DateDiff("n",[intime],[outtime])) AS duration, STATS_master_query.direction
FROM STATS_master_query
GROUP BY STATS_master_query.employee_ID, 
STATS_master_query.intime, STATS_master_query.outtime, STATS_master_query.direction
HAVING (((STATS_master_query.direction)="timer"));

I put a picture in the attachment.
I circled red which data is wrongly calculated.
No idea how it gets these values.

 

[plog]

You are not simplying calculating the difference between times. You are summing all the differences for that one employee_ID:

Sum(DateDiff("n",[intime],[outtime])) AS duration

637, 7:37, 8:05, 84

For that one record in your query, you are adding up 3 records in the underlying data source. (84/28 = 3).

Without understanding the big picture, I don't know how to help you. Why is this an aggregate query? What are you ultimately trying to accomplish?

 

[arnelgp]

Maybe you need to create a function in a Module and then call it in the query:

Public Function TimeToMinute(in as date, out as date) as double
Dim ret as double
TimeToMinute = ((hour(out)*60)+minute(out))-((hour(in)*60)+minute(in))
End function


Now on your query:

Duration: Sum(TimeToMinute(intime, outtime))

 

[plog]

Maybe you need to create a function in a Module and then call it in the query:

Ahh, good ole arnelgp. Never change, you're a hoot.

https://www.access-programmers.co.uk/forums/showthread.php?t=299391

 

[MushroomKing]

Hi dudes

Wauw. I'm not sure :S im such a rookie trying to understand it all.
I just thought its weird because all the other values are spot on.

What im trying to get is just the difference between the time, and in another query, i sum that difference column and have a total time (present).

TimeToMinute = ((hour(out)*60)+minute(out))-((hour(in)*60)+minute(in))

The (out) and (in) parts, are they related to the name of my field intime and outtime?

Or is this also part of access/vba functions?