Solved

MTD and YTD category report with a budget column

Posted on 2013-11-29
14
551 Views
Last Modified: 2014-02-05
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
Comment
Question by:Kdankwah
  • 9
  • 5
14 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
Comment Utility
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
 

Author Comment

by:Kdankwah
Comment Utility
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
 
LVL 21
Comment Utility
Let me know if you have any more questions or get stuck
0
 

Author Comment

by:Kdankwah
Comment Utility
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
 
LVL 21
Comment Utility
Did you combine the two budget tables into a single budget table with a field for fiscal year?
0
 

Author Comment

by:Kdankwah
Comment Utility
No,
0
 

Author Comment

by:Kdankwah
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Kdankwah
Comment Utility
Any luck on this?  Thanks
0
 
LVL 21
Comment Utility
It would help if you posted your current database so I can see what you have done with the tables.
0
 

Author Comment

by:Kdankwah
Comment Utility
Sorry about that, here is the database with the budget column
MTD-YTD-testing-HiTechCoach3.accdb
0
 
LVL 21
Comment Utility
0
 

Author Comment

by:Kdankwah
Comment Utility
Thanks so much, will get back to you if anything.
0
 

Author Comment

by:Kdankwah
Comment Utility
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
 

Author Comment

by:Kdankwah
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now