Linked Tables & Duplicate Record Print Out

[ProgressiveJL]

I've place the fields in the details section but since the tables are joined, field data from Table1 displays data from Table2 regardless... and vice versa.

What I want to happen is to list all records in Table1 and Table2 that meet my parameter values. What's happening now is that records from both tables are printing out twice. ie. data is printing out for the Table1 occurence and then again for Table2 occurence. I want the records to print out once per occurence.

Hope that makes sense.
Thanks again for the input!

 

[satinsilhouette]

Not exactly, but do you have an inner join, left outer join??? Are you joining the same table to itself?

Table 1 Table 2
id tbl2id

Join id to id on the two tables. If they are giving back the same exact data, that is what doesn't make sense unless you are joinging back a table to itself.
Can you clarify please?

 

[synapsevampire]

In general, poist technical information rather thyan descriptions:

Crystal verison
Database/connectivity used
Example data
Expected output

What you are speaking of is record inflation.

Were you to post basic technical inforamtion, you might find that a Command object using a UNION ALL suits your requirements, but then it's hard to envision what the required output is when you make vague references to what is being output and in what format.

-k

 

[ProgressiveJL]

Ok, so my tables are not linked but I've used the select expert to select records that match parameter {?account} from my two tables.

I've then dragged fields from both tables into the details section. The problem is that say there are 5 records in table one which match my parameter and 3 in the other table. All 5 records will print and 3 + 2 duplicates from table2 will print as well.

The result that I want is the 5 records from table1 and 3 records from table2.

Hope that clears it up better.. Thanks!

 

[ProgressiveJL]

Crystal version = XI
Database/connectivity used = local xBase

Example data:

Table1
account, jdate, jtime, jdesc

Table2
account, jdate, jtime, jdesc

Expected output:
From inputted account # parameter, display all records from Table1 & Table2 - fields jdate, jtime, jdesc, jdate, jtime, jdesc

 

[synapsevampire]

Crystal can get confused by unlinked tables, as one should expect.

If you use ODBC then the Add Command should be available to you.

Using a UNION ALL as in:

select 'tbl1' MySOurce,
account, jdate, jtime, jdesc
from table1
UNION ALL
select 'tbl2' MySOurce,
account, jdate, jtime, jdesc
from table2

Now you have all rows and they are uniquely labeled by their source. Just apply your filtering against the entire recordset in the Report->Selection Formula-Record and you should get what you want.

-k

 

[ProgressiveJL]

What if I don't use ODBC but xBase... there's not option for me to Add Command.. =(

 

[synapsevampire]

That's correct, so change or you can hack your way through it using Crystal and a subreport.

Note that I ask for example data and you list tables and fields...

Anyway, it sounds like you're joining on the account, so create a group by the account (assuming that you have multiples per), display one tables rows in the details, then place a subreport that links via the account in the group footer and show all of the rows from the other table in it.

-k

 

[ProgressiveJL]

You know.. I think I got your subreport solution to work! Just gotta tweak it a bit..

Thanks Synapse!!