Union query

[shangus]

I have a report which is using two tables and I have been trying to use the command object to create a UNION query from the two tables but I always seem to end up with different recordset ie the total doesn't add up. What is odd is when I check the show sql query option I see the union sql statement and the a join sql statement. Also I keep getting this warning prompt that more than one datasource has been used in the report. Is this what is causing it? Has anyone this before. All help is welcome. Thanks

 

[bobpv]

I assume first that you are using 9.0? Try using a UNION ALL rather than a plain UNION. Might help?

To test, run both queries independtely, and manually combine the data sets. If they are correct, try the UNION ALL.

The more than one datasource, I suspect is your connection method. Are you using OLEDB or ODBC (or antoher connection)on BOTH tables?

Letting us know the version and connection will help us to help you.

Bob
Robert Pevey and Associates

http://www.peveyweb.com

 

[synapsevampire]

The more than one datasource indicates that you've done something wrong.

Start a new report, don't add any tables to it, just use the ADD COMMAND to paste in the union query.

You should be fine.

-k

 

[shangus]

Hi thanks for all your replies they were very helpful. What about if I keep the tables but want to count the number of rows of one of the tables, is it easy to do? The thing is I tried that and kept getting a joined count. I need both tables for the report.

 

[Slonoma]

In your UNION all statement, label each table like this:

select field1, field2, field3, 'Table 1'
from table1
UNION ALL
select field 1, field2, field3, 'Table 2'
from table2

This will allow you to differentiate which table each record came from.... then you can do your count based upon that.