Access SQL filtering data from multiple columns

[Beatrix]

Hi Everyone,

I have got an Access SQL query with more than 24 columns. There are 24 columns to display the data between 2014 Jan and 2015 Dec and those columns have numbers and text. I need to filter the rows which says "Information Requested" in any of the 24 columns.

I was wondering is there any way in Access SQL to filter the data without repeating those 24 fields ?

Cheers
B.

[jonh]

Not sure what you mean by repeating fields.


Queries are based on tables.
Filter the underlying table data then the structure of the query should be irrelevant.


(Assuming your query is a crosstab based on a table structure that makes sense.)

[Beatrix]

Hi Jonh,

Thanks very much for your reply. I copied a sample table below to explain what I mean. I need to select all rows from the table where it reads "Information Req'd"

Do I have to use the WHERE clause for each single month like;


WEHERE [2014 Dec Check]="Information Req'd" OR
WEHERE [2014 Nov Check]="Information Req'd" OR
WEHERE [2014 Oct Check]="Information Req'd" OR.....

There will be 24 months need to be included so I was thinking there might be a smarter way to achieve this?



2014 Jun
Check

2014 Jul Check

2014 Aug Check

2014 Sep Check

2014 Oct Check

2014 Nov Check

2014 Dec Check

2015 Dec Check



0

1

0

0

4

12

7

Information Req'd



0

1

0

1

1

0

6

0



7

0

Information
Req'd

11

3

4

1

Information
Req'd



Information Req'd

0

5

2

2

0

2

Information
Req'd



0

1

0

0

0

0

0

1



0

1

0

1

1

1

0

Information
Req'd



0

Information
Req'd

Information
Req'd

0

0

0

0

Information
Req'd



0

1

0

0

0

0

0

Information
Req'd





Thanks again.
B.

[jonh]

You would need to repeat the fields.

The 'smarter' way would be to change the table structure so that it's easier to query.

[Beatrix]

Hi Jonh

I totally agree with you on changing the table structure but I am not allowed that's the funny bit. I really don't understand why people like to make things complicated..

thanks again.
B.

[Beatrix]

Hi Jonh,

I need your help if you don't mind please?? In below code How can I use Where CLause to extract "Info Required" only?

I've thought using WHERE ([2014 Jan Check])="Info Required" would work but because it's an AS in IF Statement I don't know how I should use it in WHERE Clause :(


SELECT [CR],
INV.SD,
INV.UD,

IIF(ISNULL(MAX([Jan-14])), IIF(#01/01/2014# BETWEEN DATEADD("m", -13, SinceDate) AND NZ(UntilDate, #01/01/2100#), "Info Required", " N/A"), MAX([Jan-14])) AS [2014 Jan Check],
IIF(ISNULL(MAX([Feb-14])), IIF(#02/01/2014# BETWEEN DATEADD("m", -13, SinceDate) AND NZ(UntilDate, #01/01/2100#), "Info Required", " N/A"), MAX([Feb-14])) AS [2014 Feb Check],

MAX([Jan-14]) AS [2014 JAN],
MAX([Feb-14]) AS [2014 FEB],

FROM
x

GROUP BY [CR], INV.SD, INV.UD;

[Beatrix]

Sorry Jonh!

Just figured it out..I need to use this query as a sub query to use AS in Where Clause..