How do I set up monthly and year to date fields in a query

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

Guest

I am trying to do a balance sheet and I am having problems with my query
showing both month to date and year to date totals. I have grouped the Chart
of account # and need to know what else to do with the dates and the $$$$. I
have created my check register in a table and I am pulling the results from
it.
 
I would do this with several queries, and a temp table. The temp table
would hold the structure that I need for output, such as:

ID
Name
MTD
YTD

Have one query populate all if the ID & Names(because some ID will have
YTD's, but no MTD's for the current M), another query to update the MTD,
then a final to update the YTD for each respective ID.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
Hi,



SELECT a.primaryKey,
a.anyOtherField,
(SELECT SUM(b.amount) FROM myTable As b
WHERE MONTH(a.dateTime)=MONTH(b.dateTime) AND a.DateTime>=
b.DateTime),
(SELECT SUM(c.amount) FROM myTable As c
WHERE YEAR(a.dateTime)=YEAR(c.dateTime) AND a.DateTime>=
c.DateTime)

FROM myTable As a
ORDER BY a.DateTime


Note that I assumed the amounts are + or - appropriately, under a single
column, Amount.

The preceding balance is not "reported" through next year (neither through
month, for monthly running sum).



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


You should add a condition on the year for the running sum per month:


(SELECT SUM(b.amount) FROM myTable As b
WHERE MONTH(a.dateTime)=MONTH(b.dateTime)
AND YEAR(a.dateTime)=YEAR(b.dateTime) '<<<---
AND a.DateTime>= b.DateTime),



or the running sum will be by month, but won't restart at 0 from year to
year, unlikely what we want.



Vanderghast, Access MVP
 
Back
Top