Subtotal with conditions on

[fredong]

I have 3 tables (Invoice, customer, NationalAcct)that are linked together.Some customers are NationalAcct with parent & child relationship and some do not have it. Basically, every Invoice has a customer# that ties to the Customer table and the Customer table will have NationalAcct# if is a National Account, if not it will be Null and the NationalAcct table will determine if this customer# is a Parent or a child.
I am trying to subtotal the SalesAmt field with the National Accts(parent cnad child) and non National Acct with Group by the Customer# and not showing the NationalAcct child Customer# but just the NationalAcct parent Customer#. Please advise.Thanks.

invoice.cust# --- customer.customer#,customer.NationalAcct#-----NationalAcct.NationalAcct#

 

[synapsevampire]

Try posting technical information instead of a description of the requirement:

Crystal version
Database/connectivity used
Example data
Expected output

This:

"and the Customer table will have NationalAcct# if is a National Account, if not it will be Null and the NationalAcct table will determine if this customer# is a Parent or a child."

makes no sense to me, if it's null, how can the nationalacct determine if the customer is a parent or a child if the foreign key field is null?

Perhaps you mean that there are two measn by which the data is stored, in which case you might need to add in the customer table a second time and use a formula to determine which field is used.

Hard for me to determine from your description.

-k

 

[fredong]

I am using Crystal v.8.0 woth ODBC connection.I should not use parent and child it should bePrimary acct or a subisdery acct. if the Customer is not a National Acct it will have a Null in Customer.NationalAcct#. If is a National Acct it will have NationalAcct# in the Customer.NationalAcct#. and joined it to the NationalAcct.NationalAcct#. From the NationalAcct table it will determine if is a Primary acct or a subisdery acct. I would like my output to be like the table2 the table1 details section

Table1
-------
Customer# Amt NationalAcct# Description
--------- ----- ------------- ------------
AAA-1 100 1111 Primary
AAA-2 2000 1111 Subsidery
AAA-3 40000 1111
BBC 100 Null
BBC 150 Null
CCB 50 NULL
BBB-1 200 1222 Primary
BBB-2 2000 1222 Subsidery
BBB-3 40000 1222 Subsidery

My desire ouput is in Table2 in Group By customer#

Table2
-------
Customer# Subtotal NationalAcct# Description
--------- ----- ------------- ------------

AAA-1 42100 1111 Primary
BBC 250 NULL
CCB 50 NULL
BBB-1 42200 1222 Primary