[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

Problems with an aging report in MS Access

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
Hodiggity
Asked:
Hodiggity
2 Solutions
 
jadedataMS Access Systems CreatorCommented:
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
 
Jagdish_BodaniCommented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now