Parameter Query Problem (Null or Not Null)

[Antavas]

I have a query I'm trying to put together. I have some initial information
but then I have 11 columns of items we evaluate for each record. If an item
pertains to a record we put an X in the field but if it doesn't we leave it
blank. So each record might have any number of X's or even no X's

My problem is that I'm trying to query the records that we've put X's on but
it isn't working. I've tried putting "Is Null" under all 11 columns but all
that does is remove the 15 records that have X's. I've tried "Is Not Null"
under all 11 columns but then I get no records back at all.

How do I get my query to return the 15 records that have X'd items?

 

[John W. Vinson]

I have a query I'm trying to put together. I have some initial information
but then I have 11 columns of items we evaluate for each record. If an item
pertains to a record we put an X in the field but if it doesn't we leave it
blank. So each record might have any number of X's or even no X's

My problem is that I'm trying to query the records that we've put X's on but
it isn't working. I've tried putting "Is Null" under all 11 columns but all
that does is remove the 15 records that have X's. I've tried "Is Not Null"
under all 11 columns but then I get no records back at all.

How do I get my query to return the 15 records that have X'd items?

Use a criterion of IS NOT NULL or =X under each field... but on *different
lines* of the query grid to use OR logic.

You should really reconsider your table desgn, though. You seem to have a many
to many relationship between records and "items"; the correct way to model
this is with a table of records, a table of Items (11 rows, at least at
present) and a third table related one-to-many to both of these tables.

 

[Jose Lopes]

Thx for your answer John. I knew someone was going to mention that my DB is
not well designed. But that’s just the way the data is generated. I have a
different table every day. You said I should import the data to a big table…
allow me to give you a quick glance of my tables.

Table 1: [20080901]

Product wharehouse1 wharehouse2
12345678 20 20
01234567 5 10
98765432 3 3

Table 2: [20080902]

Product wharehouse1 wharehouse2
12345678 10 10
01234567 5 5
98765432 1 1

At the moment I need to chose the relevant table to query. What is your
advice?

 

[Jose Lopes]

sorry wrong thread...