Multiple Field Criteria

[Guest]

I have several tables with different numbers of fields. An example of the
fields in one of the tables is below.

EmployeeID
Date
Supervisor
StartTime
EndTime
Question1Y
Question1NI
Question1N
Question1NA
Question1Comments

The fields in this table continue with the question # increasing
(Question2Y, Question2NI, etc.) and all tables follow the same field naming
but all have different number of questions.

What I would like to do is to pull any record in which the Question#NI or
Question#N fields are true (they are all Yes/No fields).

I tried doing this with the "True" criteria in each of the fields but it is
not returning the records.

Is this possible?

 

[Jason Lepack]

Don't put both of the criteria on the line then you are comparing with
an AND. Put them on different levels.

SQL for Query:
SELECT *
FROM yourTable
WHERE [Question1 NI] = true or [Question1 N] = true

A better plan would be to normalise your database. Every time you add
a question you have to modify your query and form and everything... I
wouldn't want to write the query you're working on... boourns...

If you wish to pursue this then you can post back, but what I posted
above will help you in your current situation.

 

[Guest]

I tried this in one query, and you're right, it's not fun!

What do you mean by "normalise" the database?

Don't put both of the criteria on the line then you are comparing with
an AND. Put them on different levels.

SQL for Query:
SELECT *
FROM yourTable
WHERE [Question1 NI] = true or [Question1 N] = true

A better plan would be to normalise your database. Every time you add
a question you have to modify your query and form and everything... I
wouldn't want to write the query you're working on... boourns...

If you wish to pursue this then you can post back, but what I posted
above will help you in your current situation.

I have several tables with different numbers of fields. An example of the
fields in one of the tables is below.

EmployeeID
Date
Supervisor
StartTime
EndTime
Question1Y
Question1NI
Question1N
Question1NA
Question1Comments

The fields in this table continue with the question # increasing
(Question2Y, Question2NI, etc.) and all tables follow the same field naming
but all have different number of questions.

What I would like to do is to pull any record in which the Question#NI or
Question#N fields are true (they are all Yes/No fields).

I tried doing this with the "True" criteria in each of the fields but it is
not returning the records.

Is this possible?

 

[Jason Lepack]

My first look is here:
http://en.wikipedia.org/wiki/Database_normalization

Essentially, each table represents one "thing" and tables are related
to each other as "things" are. Take a car sales database for
example.

tbl_car:
car_id
car_make
car_model
car_colour

tbl_customer:
customer_id
customer_name
customer_address

tbl_car_sale:
car_id
customer_id
sale_date
sale_value

So the car table keeps the information about the cars. The customer
table keeps the information about the customers. The car sale table
relates the cars to the customers.

Understand?

PS, who are you creating this database for?

Cheers,
Jason Lepack

I tried this in one query, and you're right, it's not fun!

What do you mean by "normalise" the database?

Don't put both of the criteria on the line then you are comparing with
an AND. Put them on different levels.

SQL for Query:
SELECT *
FROM yourTable
WHERE [Question1 NI] = true or [Question1 N] = true

A better plan would be to normalise your database. Every time you add
a question you have to modify your query and form and everything... I
wouldn't want to write the query you're working on... boourns...

If you wish to pursue this then you can post back, but what I posted
above will help you in your current situation.

 

[Guest]

I work for a distribution center and I am creating this database to track job
observations done on the warehouse employees.

Thanks for the information and the quick responses. I will look at the link
today.

My first look is here:
http://en.wikipedia.org/wiki/Database_normalization

Essentially, each table represents one "thing" and tables are related
to each other as "things" are. Take a car sales database for
example.

tbl_car:
car_id
car_make
car_model
car_colour

tbl_customer:
customer_id
customer_name
customer_address

tbl_car_sale:
car_id
customer_id
sale_date
sale_value

So the car table keeps the information about the cars. The customer
table keeps the information about the customers. The car sale table
relates the cars to the customers.

Understand?

PS, who are you creating this database for?

Cheers,
Jason Lepack

I tried this in one query, and you're right, it's not fun!

What do you mean by "normalise" the database?

Don't put both of the criteria on the line then you are comparing with
an AND. Put them on different levels.

SQL for Query:
SELECT *
FROM yourTable
WHERE [Question1 NI] = true or [Question1 N] = true

A better plan would be to normalise your database. Every time you add
a question you have to modify your query and form and everything... I
wouldn't want to write the query you're working on... boourns...

If you wish to pursue this then you can post back, but what I posted
above will help you in your current situation.

On Mar 20, 4:24 pm, AccessIM <[email protected]>
wrote:
I have several tables with different numbers of fields. An example of the
fields in one of the tables is below.

The fields in this table continue with the question # increasing
(Question2Y, Question2NI, etc.) and all tables follow the same field naming
but all have different number of questions.

What I would like to do is to pull any record in which the Question#NI or
Question#N fields are true (they are all Yes/No fields).

I tried doing this with the "True" criteria in each of the fields but it is
not returning the records.

Is this possible?