More help with Parent/Child. Can't get it to work.Help

[novice04]

Ok, I guess this is how you reply.Never done it before.

I have more information for you.

I use cystal 10
Database= ODBC(RDO)
I am using 1 table
My main fields that I can use are:
CustomerID
TransDate
TransType
TransNo
TransUniqueID
ApplyToType
ApplyToNo
ApplyToUniqueID
ReferToTransType
ReferToTransNO
ReferToTransUniqueID
TotalAmount
AppliedToItem
AppliedToAcct
DateDue

Ok What result I want from my report.
I want it grouped by CustomerID

Like this:

Customer|Invoice #|Invoice Amt|Inv.Date|Paid Date|Avg.Days
JoBob's 123456 $100.00 02/01/04 3/1/04 30
Billy's 123457 $200.00 02/2/04 3/2/04 30
--------------------------------------------------------
Total Avg. Days 30

ApplyToNo is the field that is the PARENT
TransNo is the CHILD field

ApplyToNo hold the invoice number and
TransNo is the payment number

I have been using those to fields plus, CustomerID, TotalAmount,TransDate, TransType(This holds values of either P(for payment) or I(for invoice).

The tricky thing for me is that TotalAmount hold the amounts for both the invoices and the payments. And the date field does the same.

Thanks for all your help.

 

[lynchg]

I searched and found your previous post. The way to continue with one question like you have is to reply to the same post (scroll to the bottom when inside your post page), not start a new post like you did.

If you are able to write SQL somewhere either ina database query or within a Crystal command, I think you need to do somthing like this:

Select a.CustomerID, a.TotalAmount,a.TransDate, a.TransType, b.CustomerID, b.TotalAmount, b.TransDate, b.TransType
From yourTable a Inner Join yourTable b On a.ApplyToNo = b.ApplyToNo And a.CustomerID = b.CustomerID
Where a.TransType = 'I' And b.TransType = 'P'

I am guessing on the fields (your posts are very unclear to me at least), but the approach I am suggesting is to link two incidences of the same table (a & b), a will retieve the invoice data and b will retrieve the payment data and you get both values onto one line for the report.

I hope this is helpful. I thought synapsevampire had some good ideas, I don't know if you didn't understand his/her reply or not. It seems like you are trying to accomplish something that is a little complex and if you are inexperienced with programming, reporting, and SQL logic you will probably need some direct one-on-one help that you can't get in this forum.