Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9459

Re: Accounts Receivable Query

Show all headers | View raw

On 30/05/2014 20:16:48,  wrote:
> I don't know if this is an Access question as much as a general query
> desig n question. I am wondering if I am missing something in my query or
> table design, and would appreciate any constructive suggestions:
> 
> Here are my tables:
> 
> tblInvoices
> InvoiceID PersonID  InvoiceDate InvoiceType InvoiceAmt InvoiceBalance
> 
> tblPayments
> PmtID PersonID PmtDate PmtType PmtAmt UnappliedBalance
> 
> tblPaymentsApplied
> AppliedID   PersonID  PmtAmt InvoiceID PmtID
> 
> Now, I am trying to run a query to show all Accounts Receivable as of Dec
> 31, 2013.
> 
> In order to do that, I need the following:
> 1. All invoices created between Jan 1, 2013 and Dec 31, 2013 which have a
> balance remaning today 2. All invoices created between Jan 1, 2013 and Dec
> 31, 2013 which have a payment allocated on or after Jan 1, 2014
> (in other words, they have been paid as of today, but were outstanding on
> D ec 31)
> I suppose I don't really need #1, because once I look at #2, I can just
> tak e the total invoices minus the total payments and I will know the
> total bal ance, but I think it still has to be a separate query because
> there can be several payments on one invoice, so the original invoice
> column could have duplicates in that particular query.)
> 
> 3. Separately, I want to know about invoices older than Jan 1, 2013, which
> either still have a balance, or had payments made against them in 2013. T
> he reason for this is that we have a number from Dec 2012 of receivables,
> a nd we'd like to know how many of them are still outstanding vs. pair or
> wri tten off.
> 

My Gut feeling is you don't need the InvoiceBalance as that is a calculated
field from InvoiceAmount - Sum(tblPaymentsApplied,PmtAmt). (May I suggest you
call this PaymentAllocated to save confusion with tblPayments.PayAmt). They
will not be the same as a payment could be split over several invoices or
only part pay an invoice. You don't need PersonID in the tblPaymentsApplied
as once it is allocated to an invoice, the person is picked up from that. You
may need to think about an AllocatedDate as you may receive a payment and
allocate part of it to different invoices on different dates. I would have
thought that accounts receivable would be all outstanding invoices from the
year dot till 31st Dec 2013 (less any debts written off) and less any
unallocated payments (UnappliedBalance as you call it)

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Accounts Receivable Query billpage569@gmail.com - 2014-05-30 12:16 -0700
  Re: Accounts Receivable Query "Phil" <phil@stantonfamily.co.uk> - 2014-05-30 21:28 +0100
  Re: Accounts Receivable Query CDMAPoster@fortunejames.com - 2014-06-19 06:54 -0700
    Re: Accounts Receivable Query "Phil" <phil@stantonfamily.co.uk> - 2014-06-19 23:09 +0100
      Re: Accounts Receivable Query CDMAPoster@fortunejames.com - 2014-06-20 11:05 -0700
        Re: Accounts Receivable Query "Phil" <phil@stantonfamily.co.uk> - 2014-06-20 19:39 +0100
          Re: Accounts Receivable Query CDMAPoster@fortunejames.com - 2014-06-20 12:12 -0700
            Re: Accounts Receivable Query CDMAPoster@fortunejames.com - 2014-06-20 12:17 -0700

csiph-web