Merging data from two tables 1

[Designware]

Hi,

I have a reporting issue that I need a solution for. I want to build a report from two tables that have similar transactions. Example: Table A has the following records:
Item Date Qty
2 2/3/11 3
2 3/1/11 6
2 3/4/11 4

And Table B has:
Item Date Qty
2 1/4/11 1
2 3/15/11 5

The detail listing on the final report (sorted by date) should look like:
Item Date Qty
2 1/4/11 1
2 2/3/11 3
2 3/1/11 6
2 3/4/11 4
2 3/15/11 5

All the join types give me too many records and transactions repeated (makes sense).

Can I do report1 on table A, save to a text file, run report2 on table B, append to same text file, then run report3 on that text file?

I see that I can do a subreport, but that appears as if it would give me too many records as well.

How can I accomplish this without user interaction? Are either of the ideas above feasible? Other options?

Thanks for your help!

Dale

 

[lbass]

Assuming you have a CR version 9 or above, you should use the "add command" (database expert->your datasource->add command, and use a union all to merge the fields:

select 'A' as whichtable, tableA.item, tableA.date, tableA.qty
from tableA
where <add any selection criteria here>
union all
select 'B' as whichtable, tableB.item, tableB.date, tableB.qty
from tableB
where <add any selection criteria here>

Use the punctuation/syntax appropriate to your datasource. In the main report, the fields that are in the same ordinal position will now be merged. If you need to distinguish records by table, you can then use {command.whichtable} to identify where the records are coming from. If you need to add any parameters to the command where clause, do it within the command in the box on the right, and then double click to add the parameters to the body of the command.

It is best NOT to link the command to other tables, since the links would occur locally. Instead build any other tables directly into the command.

-LB

 

[Designware]

lbass,

Perfect. I didn't realize I could use SQL directly for the joins. Once I got the syntax correct, the data came through perfectly.

Thanks for the help.

Dale