Hi There! I am trying to name my query result to specific name for instance if my first name contains "Gary*" say last column Bad/Good name: " Good name" if not say "Bad name". I used below query with Union function but it partially works. Gary comes under both Good name and bad name. Please help to fix
SELECT Customer.CustID, Customer.FirstName, Customer.LastName, Orders.OrderNo, Orders.OrderDate, Orders.Subtotal, "Good name" AS [Good name or Bad name]
FROM Customer INNER JOIN Orders ON Customer.CustID = Orders.CustID
WHERE (((Customer.FirstName) Like "Gary*"))
UNION
SELECT Customer.CustID, Customer.FirstName, Customer.LastName, Orders.OrderNo, Orders.OrderDate, Orders.Subtotal, "Bad name" AS [Good name or Bad name]
FROM Customer INNER JOIN Orders ON Customer.CustID = Orders.CustID
WHERE (((Customer.FirstName) <> "Gary*"));