How to do a UNION on a single table?

 SELECT Customers.FirstName, Customers.Surname,  Customers.DOB, Customers.CustomerAddress FROM Customers WHERE Customers.CustomerAddress LIKE '%'+ 'Main' + '%'   UNION SELECT Customers.FirstName, Customers.Surname,  Customers.DOB, Customers.CustomerAddress FROM Customers WHERE Customers.CustomerAddress LIKE '%'+ 'Gladys'+ '%'

In a union, the two or more queries should always have the same number of fields in the SELECT statement. The WHERE clause seemed to be the problem in your union query.

You need both of the returned sets to have the same format if you want to be able to merge the two sets. The first set only returns a customer address, for example:

123 Main St

But the set returned from the second query returns three columns: first name, last name, dob. For example:

John, Doe, 1970-12-31

So get both queries to return the same set of columns so that they can be merged. For example, include all 4 columns:

Jane, Smith, 1975-11-22, 123 Main St

and

John, Doe, 1970-12-31,  89 Elm St

With the same number of columns, in the same order, with the same types, then the two sets can be merged with the UNION.

Jane, Smith, 1975-11-22, 123 Main StJohn, Doe, 1970-12-31,  89 Elm St

Also be sure to read up and distinguish between UNION and UNION ALL.

I think the problem itself was not exactly the where clause, but the fact that you tried to do a UNION on the results of two queries where you are demanding different sets of data. I am probably wrong but as I understand it, what UNION does is kind of pasting two tables vertically so they have to have the same shape if you will to work properly.

The first part of your query returns just the CustomerAddress of the customers matching your criteria:

SELECT Customers.CustomerAddressFROM CustomersWHERE (  Customers.CustomerAddress LIKE '%'+ 'Peters' + '%'or Customers.CustomerAddress LIKE '%'+ 'Crows'+ '%') 

Whereas, this query:

SELECT Customers.FirstName, Customers.Surname,       Customers.CustomerAddress, Customers.DOBFROM Customers

returns four different columns and has no restriction, which would not prevent the union from happening given that your first query had 4 columns as well, but would not show what you were looking for