Access 2003

[Anita]

I have a table of charges, payments and adjustments with invoice
numbers. There are some invoice numbers with multiple records but the
prod code is different. The charge amount is the total invoice charge
but the payments and adj etc are on different lines. I am trying to
group them with a sum on the payments and adjustments but not sum the
charge since it is already the total. Example:

INVOICE PRODCODE CHG AMT PMT ADJ CR ADJ
8935554 11056 96 80.35 20.65 0 0
8935554 11056 96 0 0 0 -5
8935554 11720 51 39.6 11.4 0 0
8935554 147 119.95 32.05 0 -5

The bottom line is the total I am needing. How do I tell it not to
sum the charge amount if the invoice number and the prod code are the
same as the previous line?

 

[Anita]

I have a table of charges, payments and adjustments with invoice
numbers.  There are some invoice numbers with multiple records but the
prod code is different.  The charge amount is the total invoice charge
but the payments and adj etc are on different lines.  I am trying to
group them with a sum on the payments and adjustments but not sum the
charge since it is already the total.  Example:

INVOICE PRODCODE CHG AMT        PMT     ADJ     CR ADJ
8935554 11056       96               80.35      20.65   0       0
8935554 11056       96                 0        0       0       -5
8935554 11720       51                 39.6    11.4    0       0
8935554             147            119.95      32.05   0       -5

The bottom line is the total I am needing.  How do I tell it not to
sum the charge amount if the invoice number and the prod code are the
same as the previous line?

I can see the columns did not line up as I hoped they would.

 

[John W. Vinson]

I have a table of charges, payments and adjustments with invoice
numbers. There are some invoice numbers with multiple records but the
prod code is different. The charge amount is the total invoice charge
but the payments and adj etc are on different lines. I am trying to
group them with a sum on the payments and adjustments but not sum the
charge since it is already the total. Example:

INVOICE PRODCODE CHG AMT PMT ADJ CR ADJ
8935554 11056 96 80.35 20.65 0 0
8935554 11056 96 0 0 0 -5
8935554 11720 51 39.6 11.4 0 0
8935554 147 119.95 32.05 0 -5

The bottom line is the total I am needing. How do I tell it not to
sum the charge amount if the invoice number and the prod code are the
same as the previous line?

How do you define "the previous line"? Unlike spreadsheets, which have a
defined order of rows, tables don't; the records in a table are like potatoes
in a sack: there is no "first row" or "last row".

What - in the data - defines the row with the desired charge amount?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com