[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MTD and YTD category report with a budget column

Posted on 2013-11-29
14
Medium Priority
?
642 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 2000 total points
ID: 39686140
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
ID: 39686146
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
ID: 39686148
Let me know if you have any more questions or get stuck
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

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

Author Comment

by:Kdankwah
ID: 39688747
No,
0
 

Author Comment

by:Kdankwah
ID: 39688750
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
 

Author Comment

by:Kdankwah
ID: 39691481
Any luck on this?  Thanks
0
 
LVL 21
ID: 39691765
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
ID: 39692416
Sorry about that, here is the database with the budget column
MTD-YTD-testing-HiTechCoach3.accdb
0
 
LVL 21
ID: 39694555
0
 

Author Comment

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

Author Comment

by:Kdankwah
ID: 39832971
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
ID: 39837884
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

867 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