Issues w/ multiple tables

[dwessell]

Hi, I'm using Crystal Reports 2k8 and I've tried this in Crystal Reports Basic for VS 2k5. I'm doing a report with three tables, CQ_HEADER, SO_HEADER and SALESPERSON. Both the CQ_HEADER and the SO_HEADER tables link to the SALESPERSON table via a SPN_AUTO_KEY field. However, I always receive duplicates in my result set, due to the joins made, and I don't receive results that are valid in one table, and empty in another (Such that it only counts a CQ, if there is a SO associated with it.

Here's the query that's produced by CR.



SELECT "CQ_HEADER"."CQ_NUMBER", "CQ_HEADER"."ENTRY_DATE",
"CQ_HEADER"."TOTAL_PRICE", "SALESPERSON"."SALESPERSON_NAME",
"SO_HEADER"."ENTRY_DATE", "SO_HEADER"."TOTAL_PRICE" FROM "CQ_HEADER"
"CQ_HEADER" INNER JOIN ("SO_HEADER" "SO_HEADER" INNER JOIN "SALESPERSON"
"SALESPERSON" ON "SO_HEADER"."SPN_AUTO_KEY"="SALESPERSON"."SPN_AUTO_KEY")
ON "CQ_HEADER"."SPN_AUTO_KEY"="SALESPERSON"."SPN_AUTO_KEY" WHERE
("CQ_HEADER"."ENTRY_DATE">={ts '2007-12-01 00:00:00'} AND
"CQ_HEADER"."ENTRY_DATE"<{ts '2007-12-18 00:00:00'}) AND
("SO_HEADER"."ENTRY_DATE">={ts '2007-12-01 00:00:00'} AND
"SO_HEADER"."ENTRY_DATE"<{ts '2007-12-18 00:00:00'}) ORDER BY
"SALESPERSON"."SALESPERSON_NAME" There is no link between the SO_HEADER and
the CQ_HEADER.




Can anyone make a suggestion as to how I could go about structuring this such that it doesn't return duplicate values?

Thanks David

 

[Madawc]

If you want to "receive results that are valid in one table, and empty in another", use left outer. This ought to be an option in your database linking.

As for duplicates, it is in the nature of Crystal that you get as many 'rows' or detail lines as there are valid combinations of tables. You need to group, on some shared value like account number. Insert > Group and choose a field. Note that it has to be some property of an individual 'row', a detail line derived from a single record, or from several individual records from different tables (datasets).

You can then move the record details to group header or footer, and suppress the detail line. (Right-click on the section and choose Format Section.)

If the records are not all identical, you can choose Report > Record Sort Expert to put them into a particular order.

All of this is based on Crystal 10.

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