MTD and YTD category report with a budget column

To piggyback on the link below:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28304111.html

I have added a budget table to be used as part of the report.  I need to add the budget column to the report on the database.  I attach a replica of the report and how it should look like.

Thanks
MTD-YTD-testing-HiTechCoach3.accdb
replica.xlsx
KdankwahAsked:
Who is Participating?
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
First the budget tables  need to  be combined into the same table with a field for fiscal year just like you did in the  MTD table.

Are the budge amounts per month (period)  or a total for the year? I would assume by the name is is a budgeted amount for the year. Is that correct?

The exact same method I used for combining  the MTD and YTD will apply to adding the budget amounts.

What part is giving you troubles in learning to do this yourself?
0
 
KdankwahAuthor Commented:
Yes its for the year.  I got into trouble because I did a join.  Now that you mentioned I will combine it into the same table.  That will work then.

Thanks a lot.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Let me know if you have any more questions or get stuck
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
KdankwahAuthor Commented:
Here is what I have so far:

SELECT mtd.[Fiscal Year], mtd.COSTCTR, mtd.CATEGORY, Sum(IIf([Month_id]=[EnterPeriod],[Amount],0)) AS CurMTD, Sum(IIf([Month_id]<=[EnterPeriod],[Amount],0)) AS CurYTD, qryMTDYTDCostCTRCatPrevYear_prevyeardata.MTD AS PrevYrMTD, qryMTDYTDCostCTRCatPrevYear_prevyeardata.YTD AS PrevYrYTD, mtd.segment, mtd.Budget
FROM mtd LEFT JOIN qryMTDYTDCostCTRCatPrevYear_prevyeardata ON (mtd.CATEGORY = qryMTDYTDCostCTRCatPrevYear_prevyeardata.CATEGORY) AND (mtd.COSTCTR = qryMTDYTDCostCTRCatPrevYear_prevyeardata.COSTCTR)
GROUP BY mtd.[Fiscal Year], mtd.COSTCTR, mtd.CATEGORY, qryMTDYTDCostCTRCatPrevYear_prevyeardata.MTD, qryMTDYTDCostCTRCatPrevYear_prevyeardata.YTD, mtd.segment, mtd.Budget
HAVING (((mtd.[Fiscal Year])=[EnterFiscalYear]) AND ((mtd.COSTCTR)="7601"));

The trouble I am having is this: see the highlighted on the attached, the report is kind of repeating itself when I add the budget and the segment.

Thanks
highlighted.xlsx
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Did you combine the two budget tables into a single budget table with a field for fiscal year?
0
 
KdankwahAuthor Commented:
No,
0
 
KdankwahAuthor Commented:
I take it back.  What I did was to add the two budget tables into the MTD with a fiscal year.
The budget file in excel format is attached.

Thanks
highlighted.xlsx
0
 
KdankwahAuthor Commented:
Any luck on this?  Thanks
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
It would help if you posted your current database so I can see what you have done with the tables.
0
 
KdankwahAuthor Commented:
Sorry about that, here is the database with the budget column
MTD-YTD-testing-HiTechCoach3.accdb
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
0
 
KdankwahAuthor Commented:
Thanks so much, will get back to you if anything.
0
 
KdankwahAuthor Commented:
Hitechcoach,

I hate to bring this out of the archives.  My queries produce double entries when I run them, I dont know if its my join or what not.  I will send you the data in due course, probably this evening.  

Thanks
0
 
KdankwahAuthor Commented:
HiTechCoach4

Is there anyway, you can use these fields StartPerioddate and EndPerioddate in the Budget, MTD and Ytd formulas for me.  I decided to use dates instead in the Period parameter.  The Period id does not work with my data.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here