Entering criteria for multiple fields with “Or” condition

[belinda7]

I have a table (Access 2002) with five fields: Product, Year 1, Year 2, Year 3, Year 4.

There are 200 records. Some products have no sales for all four years. Other products may have sales for one or more of those years. I want to design a query to filter out all records with no sales for all the years.

The method I have been using so far, is to add “Is Not Null” for each of those years on a different row in the Query design table.

I now have a much larger table with 15 fields for the years. Is there a way of entering the “Is Not Null” criteria for each of those 15 year fields with the “OR” condition at one location only on the Query design table?

 

[lespaul]

Is there a way of entering the “Is Not Null” criteria for each of those 15 year fields with the “OR” condition at one location only on the Query design table?

Nope. You have to enter the criteria for each field. With a normalized database this would be a much easier query.

Leslie

 

[PHV]

Is there a way of entering the “Is Not Null” criteria for each of those 15 year fields[tt]
Field Expr1: Year1 & Year2 & ... & Year15
Display unticked
Criteria Is Not Null[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

The best solution is to NOT have years as fields. Year values are field values and should not be used as field names.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[belinda7]

Thanks Lespaul for your feedback.
What is a normalised database?

 

[belinda7]

Thanks PHV for your input.
However, this expression concatenates the data from the records. What I am looking for is an expression that refers to all the year fields so that I can apply a common criterion .

 

[lespaul]

For more information read 'The Fundamentals of Relational Database Design'

HTH


Leslie

 

[dhookom]

To normalize your table, you might use a table with fields like:
Product
Year
SalesQty
There would be one record per Product per Year.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PHV]

belinda7, my criteria is true if any year is not null and false if all years are null, so it's like your 15 Or.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SharonFinLV]

I agree about the normalization, but to answer what I think is your question:

If you want to show the records where Year1 is null AND Year2 is null AND Year3 is null (null for all three years, as opposed to any Year is null), then your Is Null criteria should all be on the same Criteria line in the QBE grid.

So your SQL view will show:

SELECT *
FROM tablename
WHERE tablename.year1 Is Null AND tablename.year2 Is Null AND tablename.year3 Is Null;

Hope this helps,
-SharonFinLV