Grouping Value List

[Robbydobbs]

I have a payment database that has 2 fields

1-(Value List)Payment Type

2-(Number Field)Payment

The value list and the Number Field repeats 8 times so that you can choose the payment type and payment 8 different times. For example if a customer purchases an item and chooses to pay a partial payment with Visa for $15 and comes back again with Master Card paying $25 and comes back again with Visa paying 25 and back again with Master Card for 50. You now have 4 payments: 2- Visa, 2- Mastercard total payments is 115; but Visa's total was 40 and Master Card's total was 75. Is There a way to group the Visa and Master Card purchases together and come back with a total for each.

[Ugo DI LUCA]

Hi Dobber,

I know this will not answer your particular question but you would need to avoid repeating fields.

My Payment File is linked to a Payment Line Item.

All Payments (deposits on customer order, part-payment, credit,...) are entered from the Customer Order or Invoice File.

This way, not only you can group payment type for a particular invoice, but you can easily group any payment type for any client, any customer order, any date and period of time, any, any, any, any....using appropriate selfjoins.

My accountant loves it. Really.

Now, I'll take a look at the repeating problem to be more helpful.

[Fitch]

If you're not going to do it Ugo's way (I agree, a separate Payment Line Item file is better), then you'd have to make a calculated repeating field for Visa:

If [Payment Type = "Visa", Payment, 0]

... and make another one for Master Card.

And I suppose at least one more for other types:

If [not Payment Type = "Visa" and not Payment Type = "Master Card", Payment, 0]

Then make a Sum calc for each of those, and a grand total that adds all the sums.