Create a statement query or report

[Bill Allen]

I have a database with two tables. One is Project and has a ProjectID,
ProjectNo, InvDate and InvAmt. Another table is ROA and has ProjectID, Date
and Amt.

How do I combine these two to make a statement with the data sorted by date?

Thanks,

Bill

 

[Olduke]

I have a database with two tables. One is Project and has a ProjectID,
ProjectNo, InvDate and InvAmt. Another table is ROA and has ProjectID, Date
and Amt.

How do I combine these two to make a statement with the data sorted by date?

Thanks,

Bill

Start with a query. Place all of the required info into the query and add a
calculated field using the DateDiff() function.
If the DateDiff() is 30 days from the billing date, you can issue a 30 day
statement, 60 for a 60 day statement etc.
For this example we'll call your calculated field PastDue30[InvAmount])
In the criteria use DateDiff("d",30,Now())
I would set up a second report to indicate how many accounts are past due
and print that first as a checklist.

 

[Bill Allen]

How do I account for payments on the account?

For example, here are the results of an "Invoice Query by selected project"
Query1 ProjectNo InvDate InvAmt
20714 12/31/2007 $4,032.25
20714 8/11/2007 $1,500.00
20714 11/26/2007 $2,840.06
20714 7/16/2007 $3,137.66
20714 8/17/2007 $3,000.00
20714 10/22/2007 $10,289.01
20714 1/8/2008 $1,418.75
20714 1/4/2008 $58.00


Here are the receipts for the same project:

Query1 ProjectNo Date Amt
20714 8/18/2007 $1,500.00
20714 12/7/2007 $2,840.06
20714 6/4/2007 $2,000.00
20714 7/27/2007 $1,137.66
20714 8/17/2007 $3,000.00
20714 12/7/2007 $9.94
20714 12/28/2007 $1,000.00
20714 1/2/2008 $1,000.00
20714 1/3/2008 $1,000.00
20714 1/4/2008 $1,000.00
20714 1/7/2008 $1,000.00
20714 1/8/2008 $1,000.00
20714 1/9/2008 $1,000.00
20714 1/11/2008 $1,000.00
20714 1/17/2008 $1,000.00


I would like a list with both invoice amounts and receipts sorted by date
with a running balance.

I have a ProjectID key which is a one to many relationship.

Regards,

Bill

I have a database with two tables. One is Project and has a ProjectID,
ProjectNo, InvDate and InvAmt. Another table is ROA and has ProjectID,
Date
and Amt.

How do I combine these two to make a statement with the data sorted by
date?

Thanks,

Bill

Start with a query. Place all of the required info into the query and add
a
calculated field using the DateDiff() function.
If the DateDiff() is 30 days from the billing date, you can issue a 30 day
statement, 60 for a 60 day statement etc.
For this example we'll call your calculated field PastDue30[InvAmount])
In the criteria use DateDiff("d",30,Now())
I would set up a second report to indicate how many accounts are past due
and print that first as a checklist.