Solved

MTD and YTD category report with a budget column

Posted on 2013-11-29
14
580 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Outlook Free & Paid Tools
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

786 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