Prior Fiscal YTD numbers

  • Thread starter Thread starter Trisha
  • Start date Start date
T

Trisha

I have a report which contains data from many different
tables. I'm stuck on a portion where I have to show Prior
year-to-date numbers and YTD numbers. My table has all of
the months separate, so for example, for this year I would
have to add months 7-12 and 1-4. IS there a way to
automate this in a query so that whenever it is opened it
will check this month and then add up all of the prior
months starting July, since that is the start of our
fiscal year. If anyone can help me I would really
appreciate it.

Thanks,
Trisha
 
Trisha,

Here's an untested accounting example.

Table: tblBalances
Fields: Period
Account
Amount

Query:
SELECT tblBalances.Account, Sum(IIf([Period]>=DateValue
([Start Date]),[Amount],0)) AS [Current Year], Sum(IIf
([Period]<DateValue([Start Date]),[Amount],0)) AS [Prior
Year]
FROM tblBalances
WHERE (((tblBalances.Period)>=DateSerial(Year(DateValue
([Start Date]))-1,Month(DateValue([Start Date])),Day
(DateValue([Start Date]))) And (tblBalances.Period)<
(DateValue([End Date])+1)))
GROUP BY tblBalances.Account;

Mark
 
Back
Top