Access Queries with If (or iif)

[tonyrulesyall]

I am working on an Access query with Access XP.

The table looks like this:

UniqueID Cars Trucks Airplanes
1 1 0 1
2 0 0 0
3 1 1 1
4 0 0 2
5 3 0 0

How do I setup a query so it checks if each UniqueID has a value
greater than one for
"Cars", "Trucks" or "Airplanes". I would the expression to be
checking any value, with something
like an (iif ([cars]>0 or [trucks]>0 or [airplanes] >0, "one category
or more", "doesn't own anything")

Any examples of how to do this would be great. Thank you in advance
for your help.

 

[Brian Smith]

I am working on an Access query with Access XP.

The table looks like this:

UniqueID Cars Trucks Airplanes
1 1 0 1
2 0 0 0
3 1 1 1
4 0 0 2
5 3 0 0

How do I setup a query so it checks if each UniqueID has a value
greater than one for
"Cars", "Trucks" or "Airplanes". I would the expression to be
checking any value, with something
like an (iif ([cars]>0 or [trucks]>0 or [airplanes] >0, "one category
or more", "doesn't own anything")

Any examples of how to do this would be great. Thank you in advance
for your help.

Do you want the value to be greater than 0 or 1? You seem to be
contradicting yourself. I'm assuming you mean 0-- in other words you want
either Cars, Trucks or Airplanes to have a value >0. If so, what you have
written should work.

Brian

 

[Arvin Meyer [MVP]]

I would add another column, called an alias column, to calculate the sum of
the other 3 columns:

TotalVehicles: [Cars]+[Trucks]+[Airplanes]

then add the criteria:

1

in the criteria box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am working on an Access query with Access XP.

The table looks like this:

UniqueID Cars Trucks Airplanes
1 1 0 1
2 0 0 0
3 1 1 1
4 0 0 2
5 3 0 0

How do I setup a query so it checks if each UniqueID has a value
greater than one for
"Cars", "Trucks" or "Airplanes". I would the expression to be
checking any value, with something
like an (iif ([cars]>0 or [trucks]>0 or [airplanes] >0, "one category
or more", "doesn't own anything")

Any examples of how to do this would be great. Thank you in advance
for your help.

 

[Bob Quintal]

(e-mail address removed) wrote in (e-mail address removed):

I am working on an Access query with Access XP.

The table looks like this:

UniqueID Cars Trucks Airplanes
1 1 0 1
2 0 0 0
3 1 1 1
4 0 0 2
5 3 0 0

How do I setup a query so it checks if each UniqueID has a value
greater than one for
"Cars", "Trucks" or "Airplanes". I would the expression to be
checking any value, with something
like an (iif ([cars]>0 or [trucks]>0 or [airplanes] >0, "one category
or more", "doesn't own anything")

Any examples of how to do this would be great. Thank you in advance
for your help.

If you can redesign the database to put your "things Owned" in a
child table with columns
ReferenceId , Category, Itemcount
1, cars, 1
1, Airplanes,1
3, casr, 1
3,trucks,1
3,planes,1

you'll have a much easier time, later, when you need to add boats
and busses to the list.

to get your answer, you simply build a Totals query that sums the
Itemcount on each ReferenceID, and then use that query and your
table to show which rows are missing from the totals.

This process is called normalization, and is very important in
database design theory.