Sub-totalling with Set Skip To

[jmueller]

Hi Everyone,

Got this darn recurring situation that seems to rear it's ugly head every 2 or 3 years and I thought i'd finally take a moment to find out if there is a solution to it or if I once again have to find a work-around...

I have a one-to-many relationship setup between my parent "Invoice" file and the child which holds the detail line items. I'm using fox's report writer and have a "data-grouping" setup on "Region".

What I simply want to do is SUM a field that is in the Parent table (Invoice Amt Subject To Comm) and display it at the end of the data-group. The problem is that this field gets re-summed for every line item in that exists in the child table. FOR EXAMPLE:

PARENT FILE HOLDS:
Invoice Amt Subj To Comm
------- ----------------
0001 $500

Now, if the child file holds 3 items for this invoice, $500 gets multipled by 3.

I think i'll leave it at that since I have to believe that a good number of you know what i'm talking about. I hope at least!

Thanks for your help.

Jack.


For exampleam trying to total up a field in the parent table and display it as

 

[alvechurchdata]

Sounds as though Fox is doing exactly what you are telling it to do. The Invoice Amt Subject To Comm field is in the paret table so it's going to get repeated for every detail line and Fox is going to add all these repeats.

The answer needs to be divided by the number of lines in each invoice. You might be able to do this with Report Variables but it'll be a mess.

You say it's a long term problem so why not bite the bullet and fix it for good? Remove the relationship from the report and write yourself a program to generate a cursor holding exactly what you need to see on the report. If you take two passes through the Invoice table then you should be able to get the right numbers.



Geoff Franklin

http://www.alvechurchdata.co.uk

 

[SGLong]

It also sounds like your data is not normalized. How does the Amount Subject to Commission get into the parent file without somehow being reflected in the child file? Maybe you could extract the same information for the report from the child record thru the same functionality that adds it to the parent record.

Steve

 

[jimstarr]

I agree with Steve. SUM the child records instead of the parent. -Jim

 

[jmueller]

Thanks you guys. You're all right in that the data is not normalized. There is not any fields in the child table that adds up to the "Amt subject to Commission". Geoff, I have tried the counting the # of line items in the child table and dividing by that number but as you said it became a mess. I was just thinking that there might be a quick solution to the problem using report writer but apparently there's not so I will build a cursor as you suggested Geoff.

Again, thanks everyone for taking the time to respond.

Jack.

 

[alvechurchdata]

so I will build a cursor as you suggested Geoff.

Hope it goes well for you. I'm sure you'll find it easier than trying to debug in the Report Writer.

Geoff Franklin

http://www.alvechurchdata.co.uk