Don’t overlook the UNION keyword in SQL as a way to simplify selections from multiple tables. For instance, to select the customer with the highest sales from three tables with basically the same layout, use the UNION keyword to allow your VB code to open only one resultset with the answer:
Private Function MaxCustSales() As Long Dim sSql as string sSql = "select max(cust_sales) max_sales from " & _ "sales.dbo.sales_east " & "UNION " & _ "select max(cust_sales) max_sales from " & _ "sales.dbo.sales_west " & _"UNION " & _ "select max(cust_sales) max_sales from " & _ "sales.dbo.sales_intl " & _ "ORDER BY max_sales DESC"
Do this to open the resultset:
If NOT IsNull(!max_sales) Then MaxCustSales = !max_sales End IfEnd Function