[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

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
0
Kdankwah
Asked:
Kdankwah
  • 9
  • 5
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now