How do I link 2 invoice tables together so that invoices don't repeat?

[ewarr]

I am using 2 tables for this report linking the IC# fields. I need to see all invoice activity from table A and all invoice activity from table B but only show unique invoice #'s. So it's possible to have only 1 invoice from table A and many invoices from table B for the same IC#. Here's an example of what I'm getting now from Crystal:

IC# table A table B item table A $ table B $
invoice# invoice#

123 8475 4475-6 800 1,000 3,000
123 8475 1125-8 800 1,000 6,500
123 8475 8527-3 800 1,000 4,488

How do I only show 1 invoice (8475) with 1 dollar amount (1,000) but also show all 3 table B invoices with all 3 dollar amounts?
Thanks!

 

[CrystalizeCanada]

Hi ewarr,

If you want to show 4 rows - So something like this:

8475 1000 (from Table A)
8475 3000 (from Table B)
8475 6500 (from Table B)
8475 4488 (from Table B)

Then you may need to create a vertical join called a Union Join. This in done in an SQL command in Crystal by copying and pasting the seperate Select statements and putting the word UNION between them. This may be what you need if you need to then sum up the $ column for Table A and B amounts.

OR...
Perhaps just creating a group (Insert,Group) on the Invoice Number would work then simply move the table A$ to the header so that looks like it shows only once.

Gordon BOCP
Crystalize

 

[IdoMillet]

The UNION approach is one option.

Another option is to use only table A in the main report and use table B in a linked subreport.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Madawc]

Or group. Put the Table A value in the group header and the table B values in the detail lines.

If you want to show Table A values without a Table B, use a Left Outer join.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ewarr]

Table A dollars in my example totals $3000 (incorrect, there is only 1 invoice for $1000...other 2 are duplicates) so if I grouped by invoice number, the total would be $3000 for table A when it should be $1000. Here's what I need to see:

IC# table A table B item table A $ table B $
invoice# invoice#

123 8475 4475-6 800 1,000 3,000
1125-8 800 6,500
8527-3 800 4,488

Total 1,000 13,988

Can anyone help? Thank you!

 

[IdoMillet]

Use a Running Total and set the accumulation frequency to once for each Group Level 1 (assuming that Invoice # is Group Level 1).

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com