Crosstab Query?

[koolwaters]

Hi!

I have a table that captures financial assistance given to employees in the form of a loan. The table captures the EmployeeID, Department, StartDate, EndDate, Duration, AmountApproved, etc. The amount approved is divided by the number of years and paid at the beginning of each fiscal.

An employee may receive a loan with a start date of 2011/11/23 and an end date of 2015/11/23, which is a four-year loan. If the loan is for $40,000.00, they will receive $10,000.00 each year.

For budgeting, I have to generate a report forecasting the amount of money the department will need in the upcoming fiscal. In this report, I need to see the amount required by the department for each year. So in my report, I have to show each Department and the amount required for each year based on the start and end dates of the loans approved. So if five employees will receive assistance from 2010-2015 and two from 2010-2017, I need to see the total required for each year.

So far, I have created a query, which extracts the Department, Start Date, Duration, Amount Approved and AmountPerAnnum from the table. I have created a crosstab query from that query but the results do not display what I want.

I need to see Department, TotalApplications, SumOfApplications and the Years as Column Headings and the data below.

I hope that I am explicit enough and someone can help me because I have drawn a blank. Any suggestions are appreciated.

Thanks in advance for any help.

 

[alansidman]

Michelle,
I hope this is helpful in building your X tab Query

http://www.datapigtechnologies.com/flashfiles/crosstab.html

Alan

 

[koolwaters]

Hi Alan,

That is something that I had looked at earlier but that did not help (could be me).

What I did was to create an expression to extract the start and end year so the fields I had were:

Department, AmountApproved, Duration, AmountPerAnnum, StartDate, StartYear, EndYear

Using that link, I had added Department as the first field and used the following for each year:

2010: IIf([startyear]=2010,[amountperannum],0)
2011: IIf([startyear]=2011,[amountperannum],0)
2012: IIf([startyear]=2012,[amountperannum],0)
2013: IIf([startyear]=2013,[amountperannum],0)

I then grouped on Department and summed each year column. This did not quite give me what I wanted because if a loan starts in 2010 and is for 5 years and the amount the employee receives is $2000.00 per year, in 2010 the total is $2000.00. In 2011, however, I need to add an additional $2000.00 to whatever loan amounts I have starting that year, etc.

So for each year, I need to see any brought forward loan amounts plus the new ones.

 

[xenou]

I need to see Department, TotalApplications, SumOfApplications and the Years as Column Headings and the data below.

I thought with Xtabs you can only have one column heading? This sounds like a real report would be needed rather than a crosstab. Can you post a sample of actual output desired (But with dummy data)?