>In many cases, I maintain 2 identical tables, one with active records and the other for historical records. Most of the time, the app is working only with the active records, which is a much smaller table. This speeds up the applications dramatically.
>The problem is that sometimes I need to get records from both of the identical tables. For example: I have active and historical invoices and I'd like to know the total number of invoices for each customer. If I execute the following SQL-Select, I will get one record for each customer except one, which will have two records (one from each table).
>Does anyone know a single SQL-Select statement that will produce one record for each customer?
>
>select Customer, count(InvNum) as Invoices;
> from ActiveInvoices;
> group by Customer;
>union select Customer, count(InvNum) as Invoices;
> from HistInvoices;
> group by Customer;
> order by Customer

If the counts are not the same from active to historical, then you'll get multiple records per customer.
You'll probably have to make a 2nd select to get the data you need.