Solved

Problems with an aging report in MS Access

Posted on 2003-11-18
2
833 Views
Last Modified: 2008-03-03
Hi, I was wondering if you might be able to help me with a problem I'm having.  I'm trying to write an aging report that draws from two tables in an Oracle database - Invoice_Aging and Cust_Payments.  All invoices are recorded in Invoice_Aging, and all the payments are recorded in Cust_Payments.  How can I write a report in Access that will combine the two tables and figure out the customer's current open balance?

I'm having logistical problems, since payments should be applied to the oldest invoice first, then the next oldest, etc.  But, for example, if a customer was to have an invoice from 120 days ago and one from 90 days ago, then decided to pay it in one lump sum, can I break out the payment to apply partly to the 120 invoice, and the rest to the 90?  How can I write this into a report?

Any help would be greatly appreciated.

Thank you,
Alex
0
Comment
Question by:Hodiggity
2 Comments
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 100 total points
ID: 9771924
Hey Hodiggity!

 This is odd...  If the Oracle Database has these two tables, doesn't the program that uses them HAVE a Balance/Aging Report??
 Doesn't this Oracle application link the payments to specific invoices??

 A balance Aging requires that ALL data for the customer be available to Access to make this determination.
 Considering the balance of power between Oracle and Access you might be better served to see if the Oracle App has this functionality.

 If we are at the point where you know that the report isn't available....

 You will need a routine that measures:
   the total Payable,
   the total Paid
   the difference between the two

   and then work backwards by date until you have collected invoices who's total are less than or equal to the total amount currently due and break those out by periods..

regards
Jack
0
 
LVL 1

Accepted Solution

by:
Jagdish_Bodani earned 400 total points
ID: 9772072
Hi Hodiggity,

I am assuming that there is more than one Question in this question.

(1) You trying to find out how to extract Data fields from two tables:

You can easily extract data fields from both "Invoice_Aging" and "Cust_Payments" tables by creating a Query and joining these two tables by Primary Keys or Foreign keys or any other common key fields.  In other words you must have at least one field in each table which you can use to make joins in your query.

(2) To split payment and apply to old invoice first:

Again create a query using both the tables.  Sort this query by Invoice Date in ascending.  I am assuming there is some kind of "Flag" data field in one of your tables to flag all outstanding or unpaid invoices.  Or use a balance payment fields and use criteria Balance Payment Greater than Zero to find out all outstanding invoices.  I am aslo assuming you have a data field for "Amount of Payment Applied" against particular invoice.  Use this field in your query.

Run the query.  As query is sorting ascending by invoice date, the oldest outstanding invoice will be on top row.  Key amount applied to this old invoice first and then remaing amount to next invoice.  You can key amount right in the column in query results when you run the query.  The amout you applied will get stored in table.

You can also make a user friendly front-end Form with above query as a source.  In this form you can create a one line After_Udate Event for Amount applied field to calculate balance payment still available from current payment which can be applied to next invoice.

It is very hard to give more specific answer as we don't know what data fields are available in two tables.  If you need more specific/detailed answer, please let me know list of all data fields and primary key fields in both the tables.

I tried my best.  Hope it helps.
 
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

792 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