Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problems with an aging report in MS Access

Posted on 2003-11-18
2
Medium Priority
?
865 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 300 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 1200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

609 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