Filter Query by multiple fields, one value

[eepok]

Quick questions and I'm sure this is easy enough to warrant a quick response:

I have a query.
I have 12 different fields that may or may not have the value "Sabbatical".
I want to view only the records that have the value "Sabbatical" in AT LEAST one of those 12 fields.

Thanks in advance.

 

[Colin@Toyota]

SELECT tblYourTable.*
WHERE tblYourTable.Field1 = "Sabbatical" OR tblYourTable.Field2 = "Sabbatical" OR tbleYourTable.Field3 = "Sabbatical" etc.

I think this should work...

 

[eepok]

SELECT tblYourTable.*
WHERE tblYourTable.Field1 = "Sabbatical" OR tblYourTable.Field2 = "Sabbatical" OR tbleYourTable.Field3 = "Sabbatical" etc.

I think this should work...

It sure looks like it should work! But I don't code. Would you happen to know how to do exactly that, but via the query window?

 

[aikea]

Go to Query page -> create new query -> SQL -> type in that sql statement

 

[swift]

Hi,

I have a similar problem in that i have ten fields in a table containing dates. I have a parameter in my query asking for 'Enter date in format DD/MM/YYYY', when this is populated with a certain date I need it to search all ten fields and return only those records which date match. At the moment something is going very wrong - probably my coding - its my first foray into SQL so please be gentle!!!
Any help would be great!!
Thanks,

john

SELECT [MAIN TABLE], WHERE [Enter date in format DD/MM/YYYY] AS Expr1, [MAIN TABLE].[CONC-TESTDATE1]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE2]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE3]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE4]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE5]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE6]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE7]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE8]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE9]="Expr1" Or [MAIN TABLE].[CONC-TESTDATE10] AS "Expr1";

 

[pbaldy]

The presence of so many date fields indicates the possibility of a design problem, so you might want to search here on normalization. Fixing that will prevent a lot of similar problems to this one. In any case, the structure of your SQL is off. Generally:

SELECT Field1, Field2, ...
FROM TableName
WHERE Field1 = 123

in your case, something like

SELECT Field1, Field2, ...
FROM [MAIN TABLE]
WHERE [CONC-TESTDATE1] = [Enter date in format DD/MM/YYYY] OR [CONC-TESTDATE2] = [Enter date in format DD/MM/YYYY] OR ...

As long as the text within the brackets is spelled exactly the same, it should only ask for the date once.

In addition to the potential design problem, you should avoid the use of spaces or symbols in your names.

 

[swift]

Yeah, I must admit the DB is not what you would call 'normal' by any stretch of the imagination....I was very pleased with it when it was built but having to iron out the bugs is proving troublesome, to say the least.

The problem I have now is that the DB is in constant use daily and its very difficult to get time to access and make changes etc....its looking likely that I will go for DB number 2, build it offline,so to speak, then transport the data across that way....

Anyway, will try your advice on the query, cheers for that pbaldy