# How to create a report for "Revenue rec'd by date, per task"?

• 0
• Question
• Updated 4 years ago

Each [Job Number] has:

[Dw 1 Pymt Date], [DW 2 Pymt Date], and so on....

Pymt Dates are calculated from [Start-Hang Date]

I am looking for a table that looks like this:

Pymt Date 02/14 02/28 03/15 03/30

Total of all payments due on that date: \$5000 \$3000 \$1000 \$5000

• 630 Points

Posted 4 years ago

• 0
• 70,444 Points
Try a Summary Report where the rows are grouped by Job # and then Customer Name and the columns are grouped by Pymt Date. I believe that you have your Payment dates being set by formula to be the 15th or the last day of the month, so you will only have 2 "columns" per month.
• 630 Points
I think my problem is that I have multiple payment dates...one for each revenue task.
• 630 Points
Pay Date DW 1 DW 2 PT 1   PT 2 Ext PT
15-Feb \$ Totals
28-Feb
15-Mar
30-Mar
15-Apr
30-Apr
• 70,444 Points
Are you running the report off the revenue tasks table?
• 630 Points
I have a table set up called Receiveables.  All data is contained in that table
• 630 Points
Revenue Projection
JOB NUMBER

Start Hang
01-17-2015
Overview Complete/Job Closed
Drywall Revenue
DW 1 Revenue
\$2,551.70
02-02-2015
DW 1 Pymt Date
02-14-2015
\$2551.70
DW 2 Revenue
\$134.30
02-04-2015
DW 2 Pymt Date
02-14-2015
\$134.30
Paint Revene
PT 1 Revenue
\$2,727.48
02-23-2015
PT 1 Pymt Date
03-01-2015
\$2727.48
PT 2 Revenue
\$481.32
03-23-2015
PT 2 Pymt Date
03-31-2015
\$134.30
EXT PT Revenue
\$160.00
03-02-2015
EXT PT Pymt Date
03-16-2015
\$160.00
Budget Totals
DW Total Revenue
2,686.00
PT Total Revenue
3368.80
JOB Total Revenue
6054.80
• 70,444 Points
This is getting complicated.  if you have a record with multiple fields to hold payment dates, and then want a summary report grouped by payment date, then that is not so practical.  It would be better to have a child table containing all the payments, tagged perhaps by the type of payment, and then you can easily have a summary report.  i think that you need to rethink the design of your tables to have a payments table with just one field for the amount and another single field for the date.
• 630 Points
Thx.  I found a work-around for this.  However, I can't seem to get something else to work.
Here is the situation:  If a job is completed between the 2nd & 15th of the month, payment will be received on the 5th of the month. If a job is completed between the 16th & 1st of the month, payment will be received on the 20th. Please advise how to "write" this.
• 70,444 Points
Can you give examples with some real dates for the 1st, the 2nd and the 16th of the month?