Query for any field less than zero

[jw45]

I have a query that contains 56 fields: Item, Desc, Wk1, Wk2, Wk3, ... Wk56.

What is the best way to select the Item if any field (Wk1-Wk56) contains a value that is less than zero?

 

[Golom]

Write some code in VBA. Your table is massively un-normalized and the required SQL (which will look something like this)

Select * From myTable

Where  Wk1 < 0
   OR  Wk2 < 0 
   OR  Wk3 < 0
      :
   OR  Wk56 < 0

is going to be long and difficult to maintain.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jw45]

Thanks. I had already started writing the Sql select statement and realized the same thing you said... long and difficult to maintain. Thought someone here may have had a quicker solution.

If you don't mind, help me understand your normalization statement. The table is created from an MRP run and passed to me as a comma delimited text file in the format above. I'm trying to write some exception reports against the information selecting only items that any weeks requirement/balance goes below zero.

How could this be normalized/structured differently?

Thanks,
JW

 

[PHV]

Have a look here:

http://r937.com/relational.html

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

 

[Golom]

If your table was of the form
[tt]
tbkWeeks
WeekNumber WeekValue
1 47
2 -8
3 22
: :
55 -1
56 7
[/tt]
Then the SQL is

Select * From tblWeeks Where WeekValue < 0

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

With your actual table:
WHERE Abs(Wk1)+Abs(Wk2)+...+Abs(Wk56)<>Wk1+Wk2+...+Wk56

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