CrossTab Sum and divide by 60

  • Thread starter Thread starter gumby
  • Start date Start date
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
 
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");
 
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...
 
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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UNION and Crosstab query 5
Crosstab returns blank fields 6
Crosstab Returns Empty Fields 2
Crosstab, Quarter Sum isn't working 6
Crosstab qry not adding up 1
Showing Crosstab Totals Column in Chart 1
Group by Range in Crosstab Query 4
Sorting Crosstab Query 2

Back
Top