Calculating Total Hours spend for a product

[iiimriii]

I have 2 tables in my database one is "Products" table and in this table
here are the fields

ProdcutID ProductName UnitPrice Catagory Profit Production time
in hours


and i have another table and it is "Order Details"

OrderDetailID OrderID ProductID UnitPrice Quantity Discount


Finally I have this Query and in this query the fields are as follows

ProductID ProductName SumofQuantity ProductionTimeInhours


the SQL code is this


PHP code:
--------------------
SELECT Products.ProductID, Products.ProductName, Sum([Order Details].Quantity) AS SumOfQuantity, Products.ProductionTimeInHours
FROM Products LEFT JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
GROUP BY Products.ProductID, Products.ProductName, Products.ProductionTimeInHours
ORDER BY Products.ProductID, Sum([Order Details].Quantity);

--------------------



the thing this does is, it can suceefully calculate the sum of order
for a specific product but when i tried to calculate the sum of time
"ProductionTimeInhours" I couldnt do it...is there a way to do it...

lets say for a Product code 01yuz it original production time is 20
minutes. If i have received lets say 20 01yuz then the total time
shoudl be 400 minutes and from that is it possible to calculate the
days it will be spend if 300 hundered orders have been received...if
the daily work hours is 7 lets say....

any help will really be appreciated thanks

 

[John Vinson]

I have 2 tables in my database one is "Products" table and in this table
here are the fields

ProdcutID ProductName UnitPrice Catagory Profit Production time
in hours

What's the datatype of [Production time in hours]? If it's Date/Time
you'll get anomalous results: a Date/Time is really best reserved for
*a point in time* rather than a duration. It's stored internally as a
double float count of days and fractions of a day since midnight,
December 30, 1899; as a result, summing "time" values will swing over
into December 31 and then January 1, 1900. It won't display hours over
24.

I'd store the duration as a Float or Double count of hours, or a long
integer count of minutes. This can be formatted for display and will
sum in a more comprehensible manner.