Selecting details from separate similar files...

[Grouper1138]

I have a report that needs to draw data from a history and current detail tables.

There was a selection formula that specified only details in a certain date range for the one table.

I added the other file in and I'm trying to get the selection criteria fixed to get all the details from both tables, but I'm having trouble with getting too many records back in the selection when I use this...

({table1.tdate} in &begdate to &enddate or
{table2.tdate} in &begdate to &enddate)

Any ideas how to clean this up?

I tried swiching to subreports but that hosed things up worse.

Thanks!

 

[lbass]

What CR version are you using? You could create a Union All query which merges the fields in each table--assuming they are the same in each table, e.g.:

Select
string1, string2, date1
From
TableA
Where
date1 >= begindate and
date1 < enddate
UNION ALL
Select
string1, string2, date1
From
TableB
Where
date1 >= begindate and
date1 < enddate

If you are using CR 9 you would use "Add Command" as your datasource and create the union all there. In 8.0 or 8.5 you could do this in the database->show SQL query area by first developing the report with the current table and record selection criteria so that the SQL auto-generates and then copy the Query, add "union all" and paste it below and change TableA to TableB.

-LB

 

[Grouper1138]

I have CR 9 running against a Great Plains database.

Haven't been able to find where to do the "Add Command".

 

[lbass]

I'm not familiar with Great Plains. "Add Command" is available in the database expert after you select your datasource, as an alternative to selecting tables, views, etc.

-LB

 

[Grouper1138]

I found it.
Now I'm wrestling with the parameters.

Thanks much for the help!