Prev: hi jerry
Next: table based i one field splitted in 2 fields
From: SBecker on 29 Apr 2010 09:35
I have a huge database that I need to add fields and build a couple of
formulas for those fields. Right now I have 3 months worth of data that I
need to be able to report trending on. Within those 3 months, I have loan
data breaking out how many loans have closed for a multitude of reasons. I
need to trend how many each month close for those reasons and what they are
as a percentage of the overall total of loans for that month.

Any suggestions?
From: ghetto_banjo on 29 Apr 2010 09:56
To see how many closed in each month with each reason, you could just
do a query that summarizes the data. I don't know what your table/
field names are, so here is an example:

SELECT Month([CloseDate]) As CloseMonth, CloseReason, Count(LoanID) AS
LoanCount FROM tblLoans GROUP BY Month([CloseDate]), Reason;


You could also build a report that has Groupings on the Month/Reason
and do Counts/Sums on that report.
From: John W. Vinson on 29 Apr 2010 12:13
On Thu, 29 Apr 2010 06:35:01 -0700, SBecker
<SBecker(a)discussions.microsoft.com> wrote:

>I have a huge database that I need to add fields and build a couple of
>formulas for those fields. Right now I have 3 months worth of data that I
>need to be able to report trending on. Within those 3 months, I have loan
>data breaking out how many loans have closed for a multitude of reasons. I
>need to trend how many each month close for those reasons and what they are
>as a percentage of the overall total of loans for that month.
>
>Any suggestions?

You certainly do NOT need to or want to add fields to your tables for
calculations. That's not how tables work!

Tables are for storing static data. Calculations, trends, summaries, and so on
are not done in Tables, but instead in Queries, which pull data from the
tables and allow you to select which fields or records, do calculations, sort,
and so on.

I don't know your business needs or the structure of the tables, so I can't
give specific advice, but I would expect some Totals queries grouping by an
expression extracting the month from the date of the loan, with a Report or a
Graph based on that query. For some resources to learn how to do this see

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

You're of course welcome to post back with more specific questions.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: hi jerry
Next: table based i one field splitted in 2 fields