Solved

Problems with an aging report in MS Access

Posted on 2003-11-18
2
816 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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now