Creating queries for invoices more and less than 30 days old

[Guest]

Hei

This is a bit difficult to explain in english ...
In accounting I want to report to my superiors the total sum on invoices
that should have been paid 30 days ago, and invoices that are even older.
Is this understandable?

I have 1 list that contains all the information on the invoices. The
information I need is there. This is called "Invoicelist".

First I need to extract invoices that has not been paid by taking the
invoice amount and substract the payments in a different colomn. I thought I
could use lets say: Colomn3: [colomn1] - [colomn2]. It could be zero in the
two colomns, like 0,00.
Is this ok?

I need to define a date for when the invoice should have been paid. So far I
have done this manually. This date is 1 month after the invoice was issued.
Lets call it "Paydate".
I also then need a date for when the invoice is 30 days overdue. This date
is then 30 days after the Paydate. Lets call it "30 days overdue".

From these two dates I plan to generate a list for belowe 30 days overdue
and above 30 days overdue.
By extrating the invoices that are belowe the paydate and the invoices that
are above the 30 days overdue date, I hope to get a list of invoices that are
belowe 30 days overdue, and should have been paid.
I then plan to get the invoices that are more then 30 days overdue by taking
all invoices that are older then the 30 days overdue date.

If you got this. How do I define this in a query?

 

[Michel Walsh]

Hi,

You can use a crosstab query, where you will pivot on an expression.
Something like:


TRANSFORM COUNT(*) As TheValue
SELECT "Total"
FROM myTable
WHERE Nz(column2 <> column1, true)
GROUP BY 1
PIVOT 30*Int( (Date()-PayDate) / 30 )


You can group by client also:


TRANSFORM COUNT(*) As TheValue
SELECT ClientID
FROM myTable
WHERE Nz(column2 <> column1, true)
GROUP BY ClientID
PIVOT 30*Int( (Date()-PayDate) / 30 )


There is maybe a problem with the design: if a client pay only part of the
bill (because he claims the invoice is wrong) or so, pay in two payments
rather than just one, column2 won't be equal to column1. Worse: imagine the
client pay more! ( eh... because he pays in advance or decide to pay what
was due in the previous invoice, or whatever reason), that would be
registered as not being paid. In theory, a more flexible design would be
to have multiple records, so the client can pay a service in multiple
payments, etc. But that was not your question, just something I though could
have to be considered, eventually.



Hoping it may help,
Vanderghast, Access MVP