Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
COURSE of the MONTH
15 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MTD and YTD category report with a budget column
Posted on 2013-11-29
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
5
14 Comments
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?
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?
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.
Thanks a lot.
Here is what I have so far:
SELECT mtd.[Fiscal Year], mtd.COSTCTR, mtd.CATEGORY, Sum(IIf([Month_id]=[EnterP
FROM mtd LEFT JOIN qryMTDYTDCostCTRCatPrevYea
GROUP BY mtd.[Fiscal Year], mtd.COSTCTR, mtd.CATEGORY, qryMTDYTDCostCTRCatPrevYea
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
SELECT mtd.[Fiscal Year], mtd.COSTCTR, mtd.CATEGORY, Sum(IIf([Month_id]=[EnterP
FROM mtd LEFT JOIN qryMTDYTDCostCTRCatPrevYea
GROUP BY mtd.[Fiscal Year], mtd.COSTCTR, mtd.CATEGORY, qryMTDYTDCostCTRCatPrevYea
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
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
The budget file in excel format is attached.
Thanks
highlighted.xlsx
Sorry about that, here is the database with the budget column
MTD-YTD-testing-HiTechCoach3.accdb
MTD-YTD-testing-HiTechCoach3.accdb
Try the Attached:
MTD-YTD-testing-HiTechCoach4.accdb
MTD-YTD-testing-HiTechCoach4.accdb
Featured Post
Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to create a slide that will launch other presentations in Microsoft PowerPoint.
In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide:
…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Suggested Courses
Course of the Month15 days, 15 hours left to enroll
743 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.