T
tracy
I have created a report that pulls job and invoice data
together so that we can analyze our realization per
customer. I have created a Customer header, then a job
header. My query is based on the job number. So in some
cases, like the one below, I can have one job to the
customer that I have created several invoices, where we
have performed a partial billing. Actual material costs
and labor costs are stored in the jobs table, as totals.
My query will return one line for each invoice tied to the
job number. I want to only total the actual material
costs and labor costs once, but add each individual line
item for the invoice. My totals for the jobs works fine,
but when I create the customer total for all their jobs,
my total is adding the individual labor and material costs
multiple time.
Customer Totals Total All Jobs $573,284.56 Total All
$76,961.05 ($496,323.51) -644.90%
Job # 30538 Total Job $43,458.62 Total
Invoices $63,486.90 Realization $20,028.28
31.55%
Job Detail - Billing Resp. Labor Cost
Material Cost Invoice # Invoice Date
Invoice Amount
Labor Total
$20,595.00 $22,863.62 30538-1
9/10/2003 $4,421.12
TAYL 30538-1 9/10/2003 $14,836.02
TAYL 30538-1 9/10/2003 $0.00
TAYL 30538-1 9/10/2003 $4,249.13
TAYL 30538-1 9/10/2003 $3,300.00
TAYL 30538-1 9/10/2003 $5,576.47
TAYL 30538-1 9/10/2003 $2,030.00
TAYL 30538-1 9/10/2003 $1,050.00
TAYL 30538-1 9/10/2003 $142.59
TAYL 30538-1 9/10/2003 $1,480.00
TAYL 30538-1 9/10/2003 $16,381.57
TAYL 30538-1 9/10/2003 $1,110.00
TAYL 30538-1 9/10/2003 $8,910.00
together so that we can analyze our realization per
customer. I have created a Customer header, then a job
header. My query is based on the job number. So in some
cases, like the one below, I can have one job to the
customer that I have created several invoices, where we
have performed a partial billing. Actual material costs
and labor costs are stored in the jobs table, as totals.
My query will return one line for each invoice tied to the
job number. I want to only total the actual material
costs and labor costs once, but add each individual line
item for the invoice. My totals for the jobs works fine,
but when I create the customer total for all their jobs,
my total is adding the individual labor and material costs
multiple time.
Customer Totals Total All Jobs $573,284.56 Total All
$76,961.05 ($496,323.51) -644.90%
Job # 30538 Total Job $43,458.62 Total
Invoices $63,486.90 Realization $20,028.28
31.55%
Job Detail - Billing Resp. Labor Cost
Material Cost Invoice # Invoice Date
Invoice Amount
Labor Total
$20,595.00 $22,863.62 30538-1
9/10/2003 $4,421.12
TAYL 30538-1 9/10/2003 $14,836.02
TAYL 30538-1 9/10/2003 $0.00
TAYL 30538-1 9/10/2003 $4,249.13
TAYL 30538-1 9/10/2003 $3,300.00
TAYL 30538-1 9/10/2003 $5,576.47
TAYL 30538-1 9/10/2003 $2,030.00
TAYL 30538-1 9/10/2003 $1,050.00
TAYL 30538-1 9/10/2003 $142.59
TAYL 30538-1 9/10/2003 $1,480.00
TAYL 30538-1 9/10/2003 $16,381.57
TAYL 30538-1 9/10/2003 $1,110.00
TAYL 30538-1 9/10/2003 $8,910.00