Running totals in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to know if there's a way to get a running total for a field in a
query like ytd total:[item1]+[item2] that will reset to zero in 2006.
 
I would like to know if there's a way to get a running total for a field in a
query like ytd total:[item1]+[item2] that will reset to zero in 2006.
Click to expand...

Yes there is; but since [item1]+[item2] conveys next to zero
information about the structure of your table it's rather hard to
write you a query which will!

Use a criterion on the datefield (which I presume must exist in your
table) selecting all records during the current year:
= DateSerial(Year(Date()), 1, 1)
Click to expand...

will select only dates since January 1 of the year in which the query
is run.

John W. Vinson[MVP]
 
John,
Yes I do have a date field (PayeeDate).
I tried the criterion on the datefield and it does select the records for
the current year however, the ytd totals for the previous year is continuing
to roll over with the new year's total.

I'm trying to get totals for the new year only. I would like for the totals
to reset to zero in 2006.
--
Thanks
D''''Angelia


John Vinson said:
I would like to know if there's a way to get a running total for a field in a
query like ytd total:[item1]+[item2] that will reset to zero in 2006.
Click to expand...

Yes there is; but since [item1]+[item2] conveys next to zero
information about the structure of your table it's rather hard to
write you a query which will!

Use a criterion on the datefield (which I presume must exist in your
table) selecting all records during the current year:
= DateSerial(Year(Date()), 1, 1)
Click to expand...

will select only dates since January 1 of the year in which the query
is run.

John W. Vinson[MVP]
Click to expand...
 
John,
Yes I do have a date field (PayeeDate).
I tried the criterion on the datefield and it does select the records for
the current year however, the ytd totals for the previous year is continuing
to roll over with the new year's total.
Click to expand...

Please post the SQL view of your query.
I'm trying to get totals for the new year only. I would like for the totals
to reset to zero in 2006.
Click to expand...

Since the total should not be stored anywhere, but instead calculated
on demand, I'm not sure that "reset" is a meaningful term here! There
should be nothing in your database to "reset"; your query should
simply calculate the sum of the correct set of records. My guess is
that it isn't doing so, and if you post the SQL we can figure out why
not.

John W. Vinson[MVP]
 
Here is a copy of the sql:
SELECT testQuery2.PayeeNumber, testQuery2.Payee, testQuery2.MaxOfPayeeDate,
testQuery2.CheckNo1, testQuery2.CheckAmt1, testQuery2.CheckNo2,
testQuery2.CheckAmt2, testQuery2.CheckNo3, testQuery2.CheckAmt3,
testQuery2.DbYearCode, testQuery2.PayeeName,
[checkamt1]+[checkamt2]+[checkamt3] AS PyWeeklyTotal, qTEST332.DbYTDTotal
FROM qTEST332 INNER JOIN testQuery2 ON qTEST332.PayeeNumber =
testQuery2.PayeeNumber
GROUP BY testQuery2.PayeeNumber, testQuery2.Payee,
testQuery2.MaxOfPayeeDate, testQuery2.CheckNo1, testQuery2.CheckAmt1,
testQuery2.CheckNo2, testQuery2.CheckAmt2, testQuery2.CheckNo3,
testQuery2.CheckAmt3, testQuery2.DbYearCode, testQuery2.PayeeName,
[checkamt1]+[checkamt2]+[checkamt3], qTEST332.DbYTDTotal;

Thanks
 
Here is a copy of the sql:
SELECT testQuery2.PayeeNumber, testQuery2.Payee, testQuery2.MaxOfPayeeDate,
testQuery2.CheckNo1, testQuery2.CheckAmt1, testQuery2.CheckNo2,
testQuery2.CheckAmt2, testQuery2.CheckNo3, testQuery2.CheckAmt3,
testQuery2.DbYearCode, testQuery2.PayeeName,
[checkamt1]+[checkamt2]+[checkamt3] AS PyWeeklyTotal, qTEST332.DbYTDTotal
FROM qTEST332 INNER JOIN testQuery2 ON qTEST332.PayeeNumber =
testQuery2.PayeeNumber
GROUP BY testQuery2.PayeeNumber, testQuery2.Payee,
testQuery2.MaxOfPayeeDate, testQuery2.CheckNo1, testQuery2.CheckAmt1,
testQuery2.CheckNo2, testQuery2.CheckAmt2, testQuery2.CheckNo3,
testQuery2.CheckAmt3, testQuery2.DbYearCode, testQuery2.PayeeName,
[checkamt1]+[checkamt2]+[checkamt3], qTEST332.DbYTDTotal;

Thanks
Click to expand...

That helps, DAngelina, but I'm still in the dark. It would appear that
this is using qTEST332 and testQuery2 (which I cannot see and don't
know anything about); and it doesn't have any criteria for the date
range.

Remember - YOU can see your database. You know what's in it. I cannot,
and do not. I'd like to be able to help but unless I can see what
you're doing, there's little that I can do.

What's testQuery2? What's qTEST332? Where (if anywhere) did you put
the date range criterion?

John W. Vinson[MVP]
 
I have made up a small Database with 1 table 2 queries and 2 reports. It
demonstrates what you are asking for. If you are interested email me and
I'll be glad to send it too you. It is under 200kb in size.
 
Back
Top