Access time Calculation

[rjclancy]

Hi All...im trying to setup a query in ms access but its realy starting to bug me...ill try and explain as good as possible...

i have 3 fields, 'Name', 'time_logged_in' and 'lines_per_day'

i need to get the number of lines people do per minute...so this is my query

1 and a half hours would be 1.5

SELECT Name, ROUND(Sum(time_logged_in *24),2) AS Time_Logged_in, Sum(lines_per_day) AS Total_Lines_Picked, SUM((time_logged_in / 60) * lines_per_day) as lines_Per_minute
FROM Sheet1
GROUP BY Name

when i run the query 'time logged in' is perfect...its the 'lines_per_minute' thats wrong......

I dont know how fare im off, but i hope anyone can help me



Kind Regards

Ronan

 

[pr2-eugin]

Hello Ronan, Could you give us a example.. What is the result you expect and how far is it differing from what you are expecting?

 

[rjclancy]

O.k...my first result,,,

Name:ronan, Total_Time:1.5, Total_Lines:346, Lines_per_minute:0.13******

The lines_per_minute should result to 8.65 witch is the result of ((1.5 / 60) * 346)....

I hope you can figure this out

Thanks for the quick reply

 

[paulwilliams]

Hi RJC. Perhaps I am missing something but if someone is "logged in" for 1.5 hours and they pick 346 lines. Then the number of lines per minute is 346/(1.5*60) = 3.84 (rounded to 2dp as in your query).

In your query, you are dividing a number (believed to be total hours logged in) by 60, which would summerise minutes as hours. As you are reading hours and wanting minutes, shouldn't this me a *60 instead?

If this is not the case, I think we will need some more detail about the relationship between your figures.

 

[rjclancy]

im tring to do your calculation but its giving me an overflow error???

maybe i eft out some info....

i have a table with data in it as follows....the first for lines

Name TimeLoggedIn Numberoflines
RonanC 00:35 300
RonanC 00:55 337
PaulW 01:30 400
PaulW 00:59 370

So what i need to do is create a query that will group the name, calculate the time, calculate the number of line and then have another field that will show lines per minute...
I think its going wrong from the group by...The reason i was multiplying the timeLoggedIn by 24 was because it gave me a result like 1.5 would mean an hour and a half....Hope this makes more sense....