Remove some lines from query (1 Viewer)

elly.khanlar

Registered User.
Local time
Today, 11:39
Joined
Oct 3, 2019
Messages
23
Hi everyone, I have a make a union query which I am trying to union two tables together.
In one of the tables, I don't want two things to be included in two fields in Table A that contain certain criteria.

Here is my SQL which is not working and I get this error" Undefined function "Where" in expression.

here is my

SELECT field 1, field 2, field 3, field 4

From table A
WHERE(table A.FIELD 2])<>"Syrups")

OR

WHERE((table A.field 4])<>1)


UNION ALL SELECT *
FROM [table b)
 

June7

AWF VIP
Local time
Today, 07:39
Joined
Mar 9, 2014
Messages
3,129
Remove all parentheses as they are not needed in this case. First version has odd number of parens - they must be in pairs. Names have space so need []. You have unpaired [ and ]. Advise not to use space nor punctuation/special characters in naming convention. This effort would be greatly simplified if you followed that guidance.

SELECT [field 1], [field 2], [field 3], [field 4] FROM [table A] WHERE [FIELD 2]<>"Syrups"
UNION ALL SELECT * FROM [table b];

Does tableB have only 4 fields all in same order as TableA?
 

elly.khanlar

Registered User.
Local time
Today, 11:39
Joined
Oct 3, 2019
Messages
23
yes, table b has exactly 4 fields. how about same conditions?

basically, i don't want to include rows that has syrup in field 2 or field 4 has number 1 .
 

June7

AWF VIP
Local time
Today, 07:39
Joined
Mar 9, 2014
Messages
3,129
Apply WHERE clause in both SELECT lines if you want to restrict records from both tables.
 

arnelgp

error reading drive A:
Local time
Today, 23:39
Joined
May 7, 2009
Messages
9,623
firstly on your second union query, Specify the fieldnames as you specify on the first table even if you have same number of fields.
you'll get unexpected result when the order of fields on the second table is not as on your first table:

SELECT [field 1], [field 2], [field 3], [field 4]
From [table A]
WHERE(([table A].[FIELD 2])<>"Syrups")
OR
WHERE(([table A].[field 4])<>1)
UNION ALL
SELECT [field 1], [field 2], [field 3], [field 4]
FROM [table b]
 

June7

AWF VIP
Local time
Today, 07:39
Joined
Mar 9, 2014
Messages
3,129
Except don't repeat keyword WHERE.

However, I doubt that OR with <> will produce desired output. But try it.

Consider:

SELECT [field 1], [field 2], [field 3], [field 4] FROM [table A]
WHERE [FIELD 2]<>"Syrups" AND [Field 4] IN (SELECT [Field 4] FROM [table A] WHERE [Field 4]<>1)
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom