calculations of records within detail section

  • Thread starter Thread starter babs
  • Start date Start date
B

babs

Job # System Date Customer Code Customer Name Invoice #
Invoice Amt
80180119 7/31/2009 GARCOCONST GARCO CONSTRUCTION, INC. $14,065.00
80180119 7/31/2009 Monaco Monaco Mechanical 150294
$14,500.00
80180119 8/31/2009 GARCOCONST GARCO CONSTRUCTION, INC.
$7,313.80
80180119 8/31/2009 Monaco Monaco Mechanical 150295
$7,540.00

I have a report and I need to divide from the above data - the 2 record by
the 1 record(invoice amt) really need to get the %
change((14,500/14,065)-1)/100=3.09% - and then the same thing for the next
two records, the date and the job # match for these records so not sure if I
do a dsum???what it should look like and where on the report to put
it????-report footer?? in detail section??

thanks for helping in advance??
Barb
 
Try this --
SELECT babs.Job, babs.System_Date, babs.Customer_Code, babs.Customer_Name,
babs.Invoice_Amt, babs_1.Customer_Code, babs_1.Customer_Name, babs_1.Invoice,
babs_1.Invoice_Amt, (([babs_1].[Invoice_Amt]/[babs].[Invoice_Amt])-1) AS [%
Change]
FROM babs INNER JOIN babs AS babs_1 ON (babs.System_Date =
babs_1.System_Date) AND (babs.Job = babs_1.Job)
WHERE (((babs.Invoice) Is Null) AND ((babs_1.Invoice) Is Not Null));
 
Now I do not think that the invoice will always be null for the other set.
Also I am not clear on how to write SQP - I usually just creat a select query
in design view- can you decipher below if possible on what to put in design
grid.

The calcuation would occur where the JOB # and the DATE are the same - see
previous post for calc.

thanks sooo much for helping ,
Barb

KARL DEWEY said:
Try this --
SELECT babs.Job, babs.System_Date, babs.Customer_Code, babs.Customer_Name,
babs.Invoice_Amt, babs_1.Customer_Code, babs_1.Customer_Name, babs_1.Invoice,
babs_1.Invoice_Amt, (([babs_1].[Invoice_Amt]/[babs].[Invoice_Amt])-1) AS [%
Change]
FROM babs INNER JOIN babs AS babs_1 ON (babs.System_Date =
babs_1.System_Date) AND (babs.Job = babs_1.Job)
WHERE (((babs.Invoice) Is Null) AND ((babs_1.Invoice) Is Not Null));

--
Build a little, test a little.


babs said:
Job # System Date Customer Code Customer Name Invoice #
Invoice Amt
80180119 7/31/2009 GARCOCONST GARCO CONSTRUCTION, INC. $14,065.00
80180119 7/31/2009 Monaco Monaco Mechanical 150294
$14,500.00
80180119 8/31/2009 GARCOCONST GARCO CONSTRUCTION, INC.
$7,313.80
80180119 8/31/2009 Monaco Monaco Mechanical 150295
$7,540.00

I have a report and I need to divide from the above data - the 2 record by
the 1 record(invoice amt) really need to get the %
change((14,500/14,065)-1)/100=3.09% - and then the same thing for the next
two records, the date and the job # match for these records so not sure if I
do a dsum???what it should look like and where on the report to put
it????-report footer?? in detail section??

thanks for helping in advance??
Barb
Click to expand...
Click to expand...
 
Back
Top