Time Values

[aftabn10]

I am trying to write a query to calculate time values but the values appear in number format rather than time. The following is the query that I have wrote:

SELECT Agents.LOB, Sum([ExternalShrinkage-Manager].[External Shrinkage]) AS [SumOfExternal Shrinkage]
FROM Agents INNER JOIN [ExternalShrinkage-Manager] ON Agents.[Manager Name]=[ExternalShrinkage-Manager].[Manager Name]
GROUP BY Agents.LOB;

I have set the format as Date/Time and changed the format to Short Time but it still shows the results as Values rather than Time.

Could somebody help me please.

 

[WayPay]

So, what is the date field here? is it the LOB or the SumOfExternalShrinkage? I've no idea what either means.

 

[Rabbie]

What values are being displayed? Remember that Access store times as a decimal of a day so 0.25 = 06:00 am and 0.75 = 06:00 pm. Is your query displaying a fraction or something else?

 

[aftabn10]

Within my query I have added SUM within the Total: field, but when I dont use this it displays the separate values for each LOB (line of business) in a time format. Im not too worried about the AM and PM i just need a total i.e. 15 hours plus 13 hours should give me 28 hours. Also it is the ExternalShrinkage that has the format Date/Time.

the following is a sample of what i am getting:

(When SUM is used)

LOB | Sum of ExternalShrinkage

A | 0
B | 2.708333
C | 5.4375

(When SUM is not used)

A | 00:00
A | 00:00
B | 06:30
B | 06:30
C | 05:00

Hope that makes sense.

 

[Rabbie]

At the risk of repeating myself 24 hours is store in Access as 1.000 when you use date/time format.

How are you getting your initial values. If they are being entered as a number of hours then you will need to divide them by 24 if you are going to store them in Date/time Form. If you are calculating them as the difference between two times then you need to understand what is actually going on.

 

[aftabn10]

Rabbie, thanks for your reply. Currently all of the values have been entered as number of hours. So would i divide these by 24 in the query before Summing the values?

Rabbie, this is the current query:

SELECT Agents.LOB, Sum([ExternalShrinkage-Manager].[External Shrinkage]) AS [SumOfExternal Shrinkage]
FROM Agents INNER JOIN [ExternalShrinkage-Manager] ON Agents.[Manager Name]=[ExternalShrinkage-Manager].[Manager Name]
GROUP BY Agents.LOB;

Where would i add the division? hope its ok for you to show me as i really dont no.

Thanks in advance.

 

[Rabbie]

What I would do is to display the sum in Number format. Then you don't need to worry about the division.

 

[aftabn10]

Thanks Rabbie, ill give that a go.