Loan Production Report

[vickan240sx]

Hi,



I'm currently trying to figure out how to do this in a report.

Current Month: September 2012
Loans Processed
	New	Renewal	Total
Commercial Loans	1	2	3
Consumer Loans	2	1	3
Total	3	3	6
3 Month Summary	Sep-12	Aug-12	Jul-12
Requests Received:	3	3	2
Redraw Requests:	1	0	0
Cancelled:	-1	0	0
Total Completed:	3	2	3
Difference:	0	1	-1

The current month will be based on a drop down menu that edits a query with which the report will be pulling from.

The 3 months summary will be constantly changing months/years based on the current month/year that is chosen.

In the database, I have a date field for each record to show when the loan request was received and another date field for when the loan was processed. These two dates would be determine what records would appear in the report.

The report that I currently have tried to build has a lot of unbound fields for all of the number values as well as the month/year values.

I'm really in need of finding out what type of formula I would need to input into the unbound fields to only sum up the specific month's data.

Thank you for your help

[June7]

What you are trying to do is set up a report that pivots and summarizes data and you want the report to be dynamic (i.e. automatically adjust for the advancing time period). Not so easy to do with pivoted data but there is more than one way to skin this cat. Just have to be creative and think outside the Access box. Maybe this will get you started.

Month 1 is the month input by user on form (September is 9)
Month 2 is month 1 plus 1 (10 for October)
Month 3 is month 1 plus 2 (11 for November)

Then textboxes in footer section might be like:

=Count(IIf(Month([Received])=Month1,[ID])
=Count(IIf(Month([Received])=Month1+1,[ID])
=Count(IIf(Month([Received])=Month1+2,[ID])

=Count(IIf(Month([Received])=Month1,[Redraw])
=Count(IIf(Month([Received])=Month1+1,[Redraw])
=Count(IIf(Month([Received])=Month1+2,[Redraw])

Beyond this, I would have to know more about your data structure.

[vickan240sx]

Hi,

That's exactly what I'm trying to do. I was able to get month 1 to show up correctly. I'm working on the month 2, so your information will definitely be useful. I think you also just solved my next problem which is the unbound fields I have in my detail section are being duplicated multiple times based on the number of records being pulled by the query. I'll try putting all the unbound fields in the report footer so they only appear once.

[vickan240sx]

What you are trying to do is set up a report that pivots and summarizes data and you want the report to be dynamic (i.e. automatically adjust for the advancing time period). Not so easy to do with pivoted data but there is more than one way to skin this cat. Just have to be creative and think outside the Access box. Maybe this will get you started.

Month 1 is the month input by user on form (September is 9)
Month 2 is month 1 plus 1 (10 for October)
Month 3 is month 1 plus 2 (11 for November)

Then textboxes in footer section might be like:

=Count(IIf(Month([Received])=Month1,[ID])
=Count(IIf(Month([Received])=Month1+1,[ID])
=Count(IIf(Month([Received])=Month1+2,[ID])

=Count(IIf(Month([Received])=Month1,[Redraw])
=Count(IIf(Month([Received])=Month1+1,[Redraw])
=Count(IIf(Month([Received])=Month1+2,[Redraw])

Beyond this, I would have to know more about your data structure.

Hi,

In your formula, what does [Received] represent?

[June7]

I don't know your actual field names so I picked something generic as example for a date field. Use your actual field names.