Prev: access97 comboBox filtering
Next: Compact/Repair and AutoNumber problem
From: bcap on 28 Jan 2010 16:09
Hi,

I have five different tables and I want to get a distinct list by
combining them all. I am using the following UNION statement, BUT
instead of checking the entire field, I'd like to have only it only
check the first 3 characters of each to compare. Is this possible
using UNION or is the another way to do this better, if at all?

SELECT FirstName, LastName, Address, City FROM TBL1
UNION
SELECT FirstName, LastName, Address, City FROM TBL2
UNION
SELECT FirstName, LastName, Address, City FROM TBL3
UNION
SELECT FirstName, LastName, Address, City FROM TBL4
UNION
SELECT FirstName, LastName, Address, City FROM TBL5
UNION
SELECT FirstName, LastName, Address, City FROM TBL6

From: Rich P on 28 Jan 2010 16:34
For Jet Sql, I think you would be better off storing this data in a temp
table like

Select * Into temp from
(Select ...Union All ...) t1

then you can perform an operation like

Select Left(fldx, 3) fldx, Count(*) As cnt from temp
Group By Left(fldx, 3) having count(*) > 1

This would give you a list of records where the are multiple rows that
start with the same first 3 letters



Rich

*** Sent via Developersdex http://www.developersdex.com ***
 | 
Pages: 1
Prev: access97 comboBox filtering
Next: Compact/Repair and AutoNumber problem