Sum totals

  • Thread starter Thread starter Ian Tranter
  • Start date Start date
I

Ian Tranter

I have problem with a report for time.
My report is linked to q query called MC-Util with the
following fieds.

PLANDATE (Date op is planned to be done)
ACTDAT (Date op is Completed)
MC_WML (op description)
OPTIME (op time in hours)
Completed Hours (hours comp if ACTDAT is not blank)
Month # (Month number)
Over due (Hours over due if ACDAT is blank)
Comp Late. (Hours Completed if ACDAT is Greater
than PLANDATE)

I have the report grouping by month on plan date and
calculating the totals.
My problem is that I want to add/sum all the comp Late
values for a given month.

That is, say 10 hour were planned (PLANDATE) in for
SEPTEMBER & only 3 were completed then 7 were late.
When the 7 were completed (ACTDAT) when were they & how
many of the 7 were in the given month. Some may have been
comp in October & some in November?

Help Please????
 
Ian

How can you differentiate between SEPTEMBER of '02 and SEPTEMBER of '03,
using your [Month #] field? You do know, don't you, that you don't need to
store "calculated" data -- if you have a date field, you can use a query to
determine the month# (see Month() function).
You mention having a query, but you haven't shown us the SQL statement.

More info, please...

Jeff Boyce
<Access MVP>
 
Jeff,
Yea, I have used the month just for info when I was
originally writing the query, any way here is the SQL for
the select query.

SELECT OPDAT.PLANDATE, OPDAT.ACTDAT, OPDAT.MC_WML, Round
((([OUT_WWO]*[RUNT_WML])+[SETUP_WML])/60,2) AS optime, IIf
(Month([OPDAT]![ACTDAT])<=Month([OPDAT]![PLANDATE]),Round
((([OUT_WWO]*[RUNT_WML])+[SETUP_WML])/60,2),0) AS
[Completed Hours], Month([ACTDAT]) AS [Month #], IIf(Nz
([ACTDAT],0)=0,Round((([OUT_WWO]*[RUNT_WML])+
[SETUP_WML])/60,2),0) AS [Over Due], IIf(Month([OPDAT]!
[ACTDAT])>Month([OPDAT]![PLANDATE]),Round((([OUT_WWO]*
[RUNT_WML])+[SETUP_WML])/60,2),0) AS [Comp Late]
FROM OPDAT
WHERE (((OPDAT.PLANDATE) Between [enter sat] And [ent
emd]) AND ((OPDAT.MC_WML)="millc"))
ORDER BY OPDAT.PLANDATE, OPDAT.ACTDAT;

Cheers
Ian,
 
Ian

I would probably approach a similar problem by concatenating queries
building a series of results that lead to solution, rather than try to do it
all in a single query. But that's me -- perhaps another of the 'group
readers can offer a more "big bang" solution for you...

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top