Grouping problem

[nickgale]

Hi, I have a client who needs a particular report, and I can^t get my head around the grouping...____One table (LINKS) holds activity records including amongst others unique reference (LINK_NO) and outcome code (LNKOUTM_COD), the other (LNKOUTM, which I assume I will have to join to) holds unique reference (LINK_NO, via which I will join) and again outcome code (LNKOUTM_COD). This database structure has been designed to allow users to record multiple outcomes (not my design, so I can^t alter it).____The problem is that I need to report on numbers of each outcome code, whether they be in the main table, or the additional table, and also group by each outcome...____Would appreciate some help on this.____Thanks,__Nick

[kfleming]

What you need to create is a multiple scan report.__To do this you can pick any file as a the master file and set the scope of this master to just one record.__Then created a calculated field called ONE with the expression 1.__Select this field as the linking field and then pick one of your real tables as the related file. Set the relation type as Scan and for the related index click the Flexlink box. For the Flexlink expression, you again use 1.__Then do the same for the second table. Start with the ONE field and again build a flexlink scan.____You can then use a calculated field __IIF(SCAN(LINKS),LINKS->LNKOUTM_COD,LNKOUTM->LNKOUTM_COD)____You can then group by this field.____Kathleen__R&R Support

[nickgale]

[updated:LAST EDITED ON Jun-30-03 AT 12:26 PM (EST)]Sorry Kathleen, I didn^t say, I^m using SQL version 9.0.007 over Oracle, does this alter your solution?__I^ve never heard of a master FILE before, scope is something I^ve never come across, and I^ve never seen a relation type ^scan^ either. I thought I was a relatively experienced user - it would appear not?

[kfleming]

All of my comments assumed that you were using the Xbase version of R&R..__For SQL you could create a User SQL report and then UNION the tables together to make a single result set that would contain all of the records from the first table along with all of the records from the second table.__The statement that you would use is:____DEFINE REPORTVIEW ALLRECS (LINK_NO,LINKCODE) as__SELECT LINK_NO,LNKOUTM_COD from LINKS__UNION ALL__SELECT LINK_NO,LNKOUTM_COD from LNKOUTM__ORDER BY LINKCODE____In your report you would see a table called ALLRECS with 2 fields and all of the records from each table sorted by the code. You could then create a group field and do your totals.____Kathleen__R&R Support

[nickgale]

Thanks Kathleen,____Your suggestion gave me the syntax that I^ve actually ended up using to create a view within the database, that way the users will find it easier to report on too (without the need for additional RnR knowledge).____Thanks,__Nick