Solved

Problems with an aging report in MS Access

Posted on 2003-11-18
2
831 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

805 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