Year-to-date Totals

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

Guest

Hello,
This is my second time posting this request.
I wasn't sure if it went threw.
I have the following fields in a query.
PayeeNum
PayeeDate
ChkNum1
ChkAmt1
ChkNum2
ChkAmt2
WeeklyTotal: chkAmt1 + chkAmt2
I get a weeklytotal working okay.
I would like to get the weeklytotal and ytdtotal (for each year) and have
them reset to zero at new year.

How? Help!

Thanks
D''''Angelia
 
DAngelia said:
Hello,
This is my second time posting this request.
I wasn't sure if it went threw.
I have the following fields in a query.
PayeeNum
PayeeDate
ChkNum1
ChkAmt1
ChkNum2
ChkAmt2
WeeklyTotal: chkAmt1 + chkAmt2
I get a weeklytotal working okay.
I would like to get the weeklytotal and ytdtotal (for each year) and
have them reset to zero at new year.

How? Help!

Thanks
D''''Angelia
Click to expand...

In the criteria field under PayeeDate you could put Between
DateSerial(Year(Date(), 1, 1) And Date(). I think the syntax for the
DateSerial is correct. I use several queries based on Date fields in such a
manner.
 
Joe,
What do I have to do about the YTD Total?
Are you saying that this criteria will reset the date field to the next year?
 
Ok let me see now if I understand what you are asking. You have a query that
you want to get totals from for various time periods time. If that is true
then use the criteria example I posted earlier for the PayeeDate, this will
give only the Chkamt collected in that period of time. Now for the totals
you can set up the query to group by date, payee number and chknum with a
sum on chkamt.
 
Joe,
I want to know how to write the calculation for getting the Weekly and YTD
total that will reset to zero next year.
My query looks like this:
qryPayeeInfoTotals: query name
PayeeNum Group By
PayeeDate Max
ChkNum1 Group By
ChkAmt1 Sum
ChkNum2 Group By
ChkAmt2 Sum
 
DAngelia said:
Joe,
I want to know how to write the calculation for getting the Weekly
and YTD total that will reset to zero next year.
My query looks like this:
qryPayeeInfoTotals: query name
PayeeNum Group By
PayeeDate Max
ChkNum1 Group By
ChkAmt1 Sum
ChkNum2 Group By
ChkAmt2 Sum
Click to expand...


Why the Max in the PayeeDate instead of Group By? It is my understanding
that the sum would be a running total from the first PayDate to the latest
PayeeDate. You would need to group the periods to get a sum for that group.
I'm not sure how to break it down by weeks as I've never done anything
dealing in weeks. However with months I always just grouped on a month and
year type fields in my queries. As for the Yearly it would the
Sum(SumofChkAmtx). As for reseting the the year on say 1/1/06 to zero it
would since it will only use data from 1/1/06 on based on what I gave you
earlier.

I'm also not following the fields you are showing above. I can understand
the PayeeNum and PayeeDate but the multiple ChkNum and ChkAmt I'm not
understanding. For example are you tracking payments made on a give date by
a given payee? If so then what you would have is a table similar to the
following.

PayeeNum
PayeeDate
ChkAmt
ChkNum

Which would then be 1 payment 1 record. I hope that is clear.
 
Joe,
The reason for the 'Max' is that I would like to see the last PayeeDate
(info) for each record.
The reason for ChkNum1, ChkNum2 and ChkAmt1, ChkAmt2. I keep track of how
many checks are written on a weekly basis for my church.

We can write up to two checks per week for a particular category. For
instance if we have a Church-Program on a Sunday morning and evening the same
day and we have to pay two different musician's we have to write two
different checks. I have a comment field that I list the name of whom the
check was made payable to. I didn't want to have to keep a separate
'PayeeName' for each person the check is being written to because we may
sometime only have to pay a particular person once or twice for the year.

This is what I want my report to look like each week:
PayeeName PayeeDate ChkNum1 ChkAmt1 ChkNum2 ChkAmt2 Comment
Musician 9/15/05 121 50.00
Somebody
Pastor 9/15/05 336 100.00 445 200.00
TK & SA
Choir 9/15/05 446 50.00 558
100.00 RF & GAA

I would like to have the weekly total and year-to-date total listed also.

The PayeeDate will always be the 'MaxDate' that's why I used it. The weekly
total will reflect those records related to the 'MaxDate'. The year-to-date
total will be all for the year thus far. I just wanted to make sure that the
Weekly and YTD Totals will reset to zero for the coming year.

I'm sorry for being so long with my explanation. Hope this helps.

Thanks! Thanks! Thanks!
 
DAngelia said:
Joe,
The reason for the 'Max' is that I would like to see the last
PayeeDate (info) for each record.
The reason for ChkNum1, ChkNum2 and ChkAmt1, ChkAmt2. I keep track of
how many checks are written on a weekly basis for my church.

We can write up to two checks per week for a particular category. For
instance if we have a Church-Program on a Sunday morning and evening
the same day and we have to pay two different musician's we have to
write two different checks. I have a comment field that I list the
name of whom the check was made payable to. I didn't want to have to
keep a separate 'PayeeName' for each person the check is being
written to because we may sometime only have to pay a particular
person once or twice for the year.

This is what I want my report to look like each week:
PayeeName PayeeDate ChkNum1 ChkAmt1 ChkNum2 ChkAmt2 Comment
Musician 9/15/05 121 50.00
Somebody
Pastor 9/15/05 336 100.00 445
200.00 TK & SA
Choir 9/15/05 446 50.00 558
100.00 RF & GAA

I would like to have the weekly total and year-to-date total listed
also.

The PayeeDate will always be the 'MaxDate' that's why I used it. The
weekly total will reflect those records related to the 'MaxDate'. The
year-to-date total will be all for the year thus far. I just wanted
to make sure that the Weekly and YTD Totals will reset to zero for
the coming year.

I'm sorry for being so long with my explanation. Hope this helps.

Thanks! Thanks! Thanks!
Click to expand...

Here is an example of one using a table in a database I'm using. Now this is
the SQL from the query. The table is constructed as show below.

LEDGER Table
Transaction
PaymentDate
PaymentAmount

Now the query below would only show me from 1/1/05 to Today and nothing for
2004 at all.

SELECT Month([PaymentDate]) AS Mt, LEDGER.Transaction, LEDGER.PaymentDate,
Sum(LEDGER.PaymentAmount) AS SumOfPaymentAmount
FROM LEDGER
GROUP BY Month([PaymentDate]), LEDGER.Transaction, LEDGER.PaymentDate
HAVING (((LEDGER.PaymentDate) Between DateValue("1/1/" & Year(Date())) And
Date()));

Mt is an Expr field setup to get the month totals and help group in a
report. (again no nothing about weekly) here though you would need to setup
some methods for weekly.

Transaction would be like your PayeeNum
PaymentDate your PayeeDate
PaymentAmount your PayAmt (this would be all your payee amounts.

Now as I said before if you use the BETWEEN DateValue("1/1"&Year(Date()))
And Date())) under the criteriea field it will only work with dates from
1/1/05 to Today. As of Jan 1, 2006 it will only show dates from 1/1/06 to
what ever day after 1/1/06 you run the query. I hope that is clearer.

I sorry I still don't understand the need for the MaxDate as this would give
all data in the date span required.
 
Joe,
I added the criteria you gave for the date field. I tested it and I believe
it will work but I will not really know until the new year.

I use the 'max' because I want the last record entered by date. I tried
using 'last' and it just was not working. It was explained too me why and I
had to go a different route.

Thanks again. I will see what happens in the new year.
--
Thanks
D''''Angelia


Joe Cilinceon said:
DAngelia said:
Joe,
The reason for the 'Max' is that I would like to see the last
PayeeDate (info) for each record.
The reason for ChkNum1, ChkNum2 and ChkAmt1, ChkAmt2. I keep track of
how many checks are written on a weekly basis for my church.

We can write up to two checks per week for a particular category. For
instance if we have a Church-Program on a Sunday morning and evening
the same day and we have to pay two different musician's we have to
write two different checks. I have a comment field that I list the
name of whom the check was made payable to. I didn't want to have to
keep a separate 'PayeeName' for each person the check is being
written to because we may sometime only have to pay a particular
person once or twice for the year.

This is what I want my report to look like each week:
PayeeName PayeeDate ChkNum1 ChkAmt1 ChkNum2 ChkAmt2 Comment
Musician 9/15/05 121 50.00
Somebody
Pastor 9/15/05 336 100.00 445
200.00 TK & SA
Choir 9/15/05 446 50.00 558
100.00 RF & GAA

I would like to have the weekly total and year-to-date total listed
also.

The PayeeDate will always be the 'MaxDate' that's why I used it. The
weekly total will reflect those records related to the 'MaxDate'. The
year-to-date total will be all for the year thus far. I just wanted
to make sure that the Weekly and YTD Totals will reset to zero for
the coming year.

I'm sorry for being so long with my explanation. Hope this helps.

Thanks! Thanks! Thanks!
Click to expand...

Here is an example of one using a table in a database I'm using. Now this is
the SQL from the query. The table is constructed as show below.

LEDGER Table
Transaction
PaymentDate
PaymentAmount

Now the query below would only show me from 1/1/05 to Today and nothing for
2004 at all.

SELECT Month([PaymentDate]) AS Mt, LEDGER.Transaction, LEDGER.PaymentDate,
Sum(LEDGER.PaymentAmount) AS SumOfPaymentAmount
FROM LEDGER
GROUP BY Month([PaymentDate]), LEDGER.Transaction, LEDGER.PaymentDate
HAVING (((LEDGER.PaymentDate) Between DateValue("1/1/" & Year(Date())) And
Date()));

Mt is an Expr field setup to get the month totals and help group in a
report. (again no nothing about weekly) here though you would need to setup
some methods for weekly.

Transaction would be like your PayeeNum
PaymentDate your PayeeDate
PaymentAmount your PayAmt (this would be all your payee amounts.

Now as I said before if you use the BETWEEN DateValue("1/1"&Year(Date()))
And Date())) under the criteriea field it will only work with dates from
1/1/05 to Today. As of Jan 1, 2006 it will only show dates from 1/1/06 to
what ever day after 1/1/06 you run the query. I hope that is clearer.

I sorry I still don't understand the need for the MaxDate as this would give
all data in the date span required.
Click to expand...
 
DAngelia said:
Joe,
I added the criteria you gave for the date field. I tested it and I
believe it will work but I will not really know until the new year.

I use the 'max' because I want the last record entered by date. I
tried using 'last' and it just was not working. It was explained too
me why and I had to go a different route.

Thanks again. I will see what happens in the new year.
Click to expand...


If you can test it using a partial year. It should only show you data that
falls between date1 and date2. Glad to help.
 
Joe,
I am getting the correct records for the date range. I am getting a running
total of the previous year in the YTDField. I only want the current YTDTotal.

Why is that?
 
DAngelia said:
Joe,
I am getting the correct records for the date range. I am getting a
running total of the previous year in the YTDField. I only want the
current YTDTotal.

Why is that?
Click to expand...

You shouldn't be. It should only Sum the Amounts from records that meets the
criteria set.
 

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

Calculations in a query 1
Year to Date subquery 3
Limiting YTD totals to date 4
Add years between two dates 4
Year to Date Query Issue 2
Year-To-Date Query Help Needed 6
Year To Date Record Selection 3
Calculating YTD totals on a Fiscal Year 7

Back
Top