Aged Analysis Maths

Account receivable statement

I have the three tables below as part of sales invoicing:
TblCustomer invoice
Pk ID
Cbo Customer ID (FK)
Cbo Receipts ID (FK) (Used for referencing advance payments)
Salesman (FK) (Used for commission accounting)
Ship Date
Tbl (Line details (Sub table to tbl customer)

TblReceipts
Pk ID
Cbo Customer ID (FK)
Cbo Invoice Number
Receipts Dates

To match the payments I will pull the correct invoice through the Cbo Invoice Number in receipt table
Now before using the cross tab query to create the aged analysis I want the invoices to netted off with payments at either union query level or at select query or is there a better way to create an aged analysis, for sure four tables are required.
(a)      Customer table
(b)      Customer invoice/Line details tables
(c)      Receipts table

Any idea here will be highly appreciated.

Challenges
(1)      One invoice can be paid by 4 receipts or more (How do we allocate to the invoice concerned????)

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should not store ReceiptsID in your Invoice table, since (as you mention) you could have multiple Receipts. Instead, if you need to track the "type" of payment (like PrePay, Installment, etc) then add that field to your Receipt table and set it when you enter the Payment.

Your Receipts Table should include an Amount field, and you fill in the amount field should reflect the amount paid for that Receipt. So if I have invoice 100-1 for $1000, I might have these receipts:

100-1 $500
100-1 $100
100-1 $200
100-2 $200

The total would be $1000, which would pay out the Invoice.

Also, a Receipt is related to an Invoice (I'd think) so you need to store the PRIMARY KEY value from the Invoice table in the Receipts table as your Foreign Key. Storing the CustomerID in the Receipt table is not necessary, assuming an Invoice is related to your Customer. If you need to store data concerning the entity who made the Payment, then add a field named PaymentBy or something like that, and relate that to the Customer table (or some other table, if appropriate)

Finally, I notice that you have some field prefaced with "cbo", which I assumes means you've set the table level lookup as a Combobox. You would be well advised to remove those table level lookups, and instead create combos on your forms when needed. Table Level lookups are a big no-no, and will come back to bite you when it's time to create reports, queries, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.