If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formulas - help!
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? |
#2
|
|||
|
|||
Formulas - help!
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. |
#3
|
|||
|
|||
Formulas - help!
On Thu, 29 Apr 2010 06:35:01 -0700, SBecker
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/acc...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] |
Thread Tools | |
Display Modes | |
|
|