Acess Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table Which has three fields MotorOneOn/Off, MotorTwoOn/Off and
MotorThreeOn/Off. Each one of these has an option to for On or Off. What I
need to do is to be able to run a query that will give me the results of all
three on or all three off. Not all motors are on or off at the same time. I
have tried to use a union query with no results. Any Ideas or is it so
obvious I can't see it.
 
What is the datatype of the fields? Are they Yes/No? If they are Yes/No
then just put criteria of -1 (minus one) in the same criteria row of the
query design view grid. That will give all ON. Use a 0 (zero) for
OFF.

If the fields are some other kind then the criteria will be different. If
they are set from a form options group then it might use a number field and
criteria of 1 and 2.
 
While it is possible to do what you're describing, the underlying problem is
that you have a well-designed ... spreadsheet! The tools and features of
Access work best on well-normalized relational data, not on spreadsheet-like
repeating columns (Motor1, Motor2, ...).

If you decide to struggle on with the current design, be aware that any
change to the number of Motors will require rewriting your queries,
redesigning your table, "remodeling" your forms and reports, and
re-factoring any code that refers to the repeating columns.

For the nonce, consider using the IIF() function to derive an "overall"
result.

Regards (and good luck!)

Jeff Boyce
Microsoft Office/Access MVP
 
Try using:

SELECT *
FROM Table1
WHERE (MotorOne=True AND MotorTwo=True AND MotorThree=True) OR
(MotorOne=False AND MotorTwo=False AND MotorThree=False);

Lucas
 
You need to use two Boolean ANDs for each set of fields and parenthesise
these operations so they are evaluated independently of a Boolean OR
operation. If the fields are of Boolean (Yes/No") data type then the query
would go like this:

SELECT *
FROM YourTable
WHERE (MotorOneOn/Off AND MotorTwoOn/Off AND MotorThreeOn/Off)
OR (NOT MotorOneOn/Off AND NOT MotorTwoOn/Off AND NOT MotorThreeOn/Off);

If they are text data type:

SELECT *
FROM YourTable
WHERE
(MotorOneOn/Off = "On" AND MotorTwoOn/Off = "On" AND MotorThreeOn/Off =
"On")
OR
(MotorOneOn/Off = "Off" AND MotorTwoOn/Off = "Off" AND MotorThreeOn/Off =
"Off");

However, your table design is not ideal. A better model would be to have
two more tables Motors (with presumably three rows at present) and MotorsOn
which models the many-to-many relationship between the Motors table and the
main table. This would have two foreign key columns referencing the primary
keys of the other tables. This design does not assume a fixed number of
motors so is more flexible.

By joining the main table and the Motors table to the MotorsOn table you'd
only record those motors which are 'on', so if for one row in the main table
only motors one and two are on you'd have two matching rows in MotorsOn. To
return the rows where all motors are on or off you'd then use:

SELECT *
FROM MainTable
WHERE
(SELECT COUNT(*)
FROM MotorsOn
WHERE MotorsOn.MyID = MainTable.MyID
And MotorID IN (1,2,3)) = 3
OR NOT EXISTS
(SELECT *
FROM MotorsOn
WHERE MotorsOn.MyID = MainTable.MyID);

where MyID is the primary key of the main table and the corresponding
foreign key in Motor Status, and MotorID is the primary key of the Motors
table and the corresponding foreign key in Motor Status.

Ken Sheridan
Stafford, England
 
I'm not sure if I understand you right, or not. I'm not understanding what
you say you are unioning together. It sounds like you just need a query with
your one table in it. Place the desired table fields in the query, including
the Yes/No fields. Enter two cirteria rows in the query. In the first row,
put true under each field and in the second row put false. The query will
write a where clause to get the data you describe:
WHERE ((([My Table].MotorOneOn/Off)=True) AND (([My Table].MotorTwoOn/Off)
=True) AND (([My Table].MotorThreeOn/Off)=True)) OR ((([My Table].
MotorOneOn/Off)=False) AND (([My Table].MotorTwoOn/Off)=False) AND (([My
Table].MotorThreeOn/Off)=False));
i.e. all true OR all false.
 
Back
Top