I am using Access 2003

I want to create a billing statement report from a query that will give me 
all invoices due by a specific client, and I would like to have fields for 
amounts that are 30, 60, 90 days etc....

I am able to do this no problem in Excel, but I would like to generate this 
info from Access, as all of my invoice data is stored there...

Thanks for any help 

On Mar 6, 2:56=A0pm, Jacinda <Jaci...@discussions.microsoft.com> wrote:
> I am using Access 2003
>
> I want to create a billing statement report from a query that will give me=

> all invoices due by a specific client, and I would like to have fields for=

> amounts that are 30, 60, 90 days etc....
>
> I am able to do this no problem in Excel, but I would like to generate thi=
s
> info from Access, as all of my invoice data is stored there...
>
> Thanks for any help

look up   Datediff function

Would be helpful to know what your table structure looks like.

Do you have separte Invoice and Receipts tables?  If so, you might start 
with a query that looks something like:

Query1:
SELECT I.ClientID, I.InvoiceNo, First(I.DueDate) as DueDate, 
First(I.AmountDue) as AmountDue,
              Max(R.DatePaid) as LastPayment, Sum(R.Received) as 
TotalReceipts
FROM tbl_Invoices as I
LEFT JOIN tbl_Receipts as R
ON I.InvoiceNo = R.InvoiceNo
GROUP BY I.ClientID, I.InvoiceNo
HAVING First(I.AmountDue) - Sum(R.Received) > 0

Once you have this query working, you can create a second query to actually 
Group the amounts by 30, 60, 90 days.  To do this, I would probably create 
another table (tbl_Timeperiods) that contains fields for:

StartDiff, EndDiff, and RangeDesc
     0             30           On Time
     31           60                 >30
     61           90                 >60
     90         999                 >90

Then you your next query might look something like:

Select Q.ClientID, T.RangeDesc, Sum(Q.AmountDue - Q.TotalReceipts) as Due
FROM Query1 as Q, tbl_TimePeriods as T
WHERE DateDiff("d", Q.DueDate, NZ(Q.LastPayment, Date())
BETWEEN T.StartDiff AND T.EndDiff
Group By Q.ClientID, T.RangeDesc

This method would give results like:

ClientID   RangeDesc   Due
     1              >30           $25
     1              >60           $75
     1              >90         $100
     2              >30           $25
     2              >90           $75

This query uses the computed date difference between the due date and either 
the last payment date (or the current date if no payments have been made) 
and then compares this to the StartDiff and EndDiff values in the table 
above to determine which range group it falls in.  I prefer this over the 
method below because this gives me flexability if I want to change the range 
values (all I have to do is change a couple of values in a table as opposed 
to having to hard code the ranges in the query below).

You could then take this and create a crosstab query out if it if you need 
all the values for a particular client in a single record.
Another way to approach the second query would be:

SELECT Q.ClientID,
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date()))<=30,[AmountDue]-[TotalReceipts],Null)) 
AS [30 or Less],
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date())) Between 31 And 
60,[AmountDue]-[TotalReceipts],Null)) AS [>30],
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date())) Between 61 And 
90,[AmountDue]-[TotalReceipts],Null)) AS [>60],
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date()))>90,[AmountDue]-[TotalReceipts],Null)) 
AS [>90]
FROM qry_Invoice_Receipts AS Q
GROUP BY Q.ClientID;

HTH
Dale

"Jacinda" <Jacinda@discussions.microsoft.com> wrote in message 
news:2DD9167F-69EB-463B-B0C3-E6C68480050A@microsoft.com...
>I am using Access 2003
>
> I want to create a billing statement report from a query that will give me
> all invoices due by a specific client, and I would like to have fields for
> amounts that are 30, 60, 90 days etc....
>
> I am able to do this no problem in Excel, but I would like to generate 
> this
> info from Access, as all of my invoice data is stored there...
>
> Thanks for any help