CrossTab Sum and divide by 60

G

gumby

TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) AS [SumOfDown Time
(mm)]
SELECT MonitorDownTime.[Assigned Monitor], Sum(MonitorDownTime.[Down
Time (mm)]) AS [Total Of Down Time (mm)]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

I would like to have my two Sum fields be divided by 60. These fields
are a totla number of minutes, but I would like to display them in the
query as hours.

How would I go about doing that?

Thanks,
David
 
A

Allen Browne

Can't you just divide by 60?

TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [SumOfDown Time]
SELECT MonitorDownTime.[Assigned Monitor],
Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [Total Of Down Time]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");
 
G

gumby

Yes, I got it. Thanks. Can I limit the decimals in the query to 2?

Allen said:
Can't you just divide by 60?

TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [SumOfDown Time]
SELECT MonitorDownTime.[Assigned Monitor],
Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [Total Of Down Time]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

gumby said:
TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) AS [SumOfDown Time
(mm)]
SELECT MonitorDownTime.[Assigned Monitor], Sum(MonitorDownTime.[Down
Time (mm)]) AS [Total Of Down Time (mm)]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

I would like to have my two Sum fields be divided by 60. These fields
are a totla number of minutes, but I would like to display them in the
query as hours.

How would I go about doing that?

Thanks,
David
Click to expand...
Click to expand...
 
A

Allen Browne

In query design view, right-click the field, and choose Properties.
Set:
Format Fixed
Decimal Places 2

Realistically, queries are just for retrieving data. Better to set the
properties of the text box on your form/report.

If you actually wanted to round the values to 2 decimal places:
TRANSFORM Round(Sum(MonitorDownTime.[Down Time (mm)])/60,2)
AS [SumOfDown Time]
SELECT ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

gumby said:
Yes, I got it. Thanks. Can I limit the decimals in the query to 2?

Allen said:
Can't you just divide by 60?

TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [SumOfDown Time]
SELECT MonitorDownTime.[Assigned Monitor],
Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [Total Of Down Time]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

gumby said:
TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) AS [SumOfDown Time
(mm)]
SELECT MonitorDownTime.[Assigned Monitor], Sum(MonitorDownTime.[Down
Time (mm)]) AS [Total Of Down Time (mm)]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

I would like to have my two Sum fields be divided by 60. These fields
are a totla number of minutes, but I would like to display them in the
query as hours.

How would I go about doing that?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
G

gumby

Good Point & thank you sir.

Allen said:
In query design view, right-click the field, and choose Properties.
Set:
Format Fixed
Decimal Places 2

Realistically, queries are just for retrieving data. Better to set the
properties of the text box on your form/report.

If you actually wanted to round the values to 2 decimal places:
TRANSFORM Round(Sum(MonitorDownTime.[Down Time (mm)])/60,2)
AS [SumOfDown Time]
SELECT ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

gumby said:
Yes, I got it. Thanks. Can I limit the decimals in the query to 2?

Allen said:
Can't you just divide by 60?

TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [SumOfDown Time]
SELECT MonitorDownTime.[Assigned Monitor],
Sum(MonitorDownTime.[Down Time (mm)]) / 60
AS [Total Of Down Time]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

TRANSFORM Sum(MonitorDownTime.[Down Time (mm)]) AS [SumOfDown Time
(mm)]
SELECT MonitorDownTime.[Assigned Monitor], Sum(MonitorDownTime.[Down
Time (mm)]) AS [Total Of Down Time (mm)]
FROM MonitorDownTime
GROUP BY MonitorDownTime.[Assigned Monitor]
PIVOT Format([Date],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec");

I would like to have my two Sum fields be divided by 60. These fields
are a totla number of minutes, but I would like to display them in the
query as hours.

How would I go about doing that?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Top