Oranges, Apples and Bananas...Selective Query!

[Peter]

Hi all again!..i am struggling wit the following issue… ïŠ


In a table I have the following fields that are part of a query…


Products Green Yellow Large Medium

Bananas 1 1 1 1
Apples 2 3 2 2
Oranges 0 1 2 4


How do I make the query NOT count “Apples†IF they are “Yellow?

Thanks again!

 

[vanderghast]

SELECT Green+large+Medium
FROM table
WHERE products = "apples"



Note that this is a very poor table design. Not only it does not say how
many apples are and green and large at the same time, but also, the
properties should be vertical, not horizontal.


Vanderghast, Access MVP

 

[golfinray]

In the criteria under the "yellow" field put Not like "apples"

 

[Peter]

Thanks Milton and Vanderghast! I will change the table design
accordingly..once again..thanks!

 

[Peter]

Hm..i get this meesage when i put No like "apples" in the yellow field-

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

 

[KARL DEWEY]

You have a real problem with your table structure.
You have 2 green and 3 yellow apples but there only 4 in the barrel (2 large
plus 2 medium).

You have only 1 yellow orange but there is 6 in the box (2 large plus 4
medium). What color are the rest of them?

 

[vanderghast]

You should try the design:


Product Amount Color Size ' Fields


with one record per (product, color, size):

Apple 2 Green Small
Apple 1 Green Medium
Apple 1 Yellow Small
Apple 3 Yellow Medium


as example. Then, counting the apples that are not yellow would be:


SELECT SUM(amount)
FROM table
WHERE product="apple"
AND color <> "yellow"



Vanderghast, Access MVP

 

[Peter]

Hm..well...i see now that i did not explain me correct..you are right. The
large and medium should be blue and red//meaning: I looked in the tables and
it is set up the following way..forget about medium and large

Record 1. Date: June 8 2009. Apples: Green1 Yellow2 Blue3 Red4
Record 2. Date: June 8 2009. Oranges:Green0 Yellow6 Blue1 Red0

The query is set up to give me the result per date (between date) but i dont
want to count if the Oranges Blue1.....

Hmm.... am i having a genral meltdown in my thingking..

 

[KARL DEWEY]

Make your table as Vanderghast suggest.

Hm..well...i see now that i did not explain me correct..you are right. The
large and medium should be blue and red//meaning: I looked in the tables and
it is set up the following way..forget about medium and large

Record 1. Date: June 8 2009. Apples: Green1 Yellow2 Blue3 Red4
Record 2. Date: June 8 2009. Oranges:Green0 Yellow6 Blue1 Red0

The query is set up to give me the result per date (between date) but i dont
want to count if the Oranges Blue1.....

Hmm.... am i having a genral meltdown in my thingking..

 

[Peter]

Ok..will do. Thanks for the guidance...i really need to rebuild a
lot..well..step by step..thanks again!