Calculations on selected records from one field

[Guest]

I have a table with the following logical structure:


Name...Number eaten of apples......Number of eaten oranges.....All fruits

Mike...............3..............................
...........4...........................7
John...............5..............................
...........3...........................8
Peter..............2..............................
...........2...........................4
Phil.................1............................
.............4...........................5

Question 1:
Can I calculate in a query sum totals for apples (or oranges or all fruits)
eaten by Mike and Peter together (in one record) and ignore John and Phil?

For example:
Name...Number eaten of apples......Number of eaten oranges.....All fruits

Mike+John.............8..............................
...........7...........................15


Question 2:
How can I get sum totals for all excluding just Peter? Or Peter and Phil?

For example

All except
Peter..........9........................11........................20

Your help will be very much appreciated!

Aleksandr

 

[Guest]

The first problem is that your table structure is wrong. It should be
something like:

ID txtName txtFruit nEaten
1 Mike apples 3
2 Mike oranges 4
3 John apples 5
4 John oranges 3

The ID field should be an autonumber data type and the primary key. There
should not be an "all fruits" entry as this data can be derived from doing
the math on the other entries.

As far as limiting it to a certain person, you put something like ="Mike" in
the criteria for the txtName field.

To get the data to show up across such as
Name...Number eaten of apples......Number of eaten oranges.....All fruits

You need a crosstab query if you have the proper table structure as I've
listed above.

 

[John Spencer]

Same answer as yesterday

SELECT
, Sum(Apples) as AppleCount
, Sum(Oranges) as OrangeCount
, Sum(Apples) + Sum(Oranges) as FruitCount
WHERE [Name] Not In ("John","Phil")

If you don't understand how to do that from the SQL statement, post back for
instructions on how to build the query using the query design view (grid
view).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John,

Many thanks for your help, it works fine.

Aleksandr

Same answer as yesterday

SELECT
, Sum(Apples) as AppleCount
, Sum(Oranges) as OrangeCount
, Sum(Apples) + Sum(Oranges) as FruitCount
WHERE [Name] Not In ("John","Phil")

If you don't understand how to do that from the SQL statement, post back for
instructions on how to build the query using the query design view (grid
view).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with the following logical structure:


Name...Number eaten of apples......Number of eaten oranges.....All fruits

Mike...............3..............................
..........4...........................7
John...............5..............................
..........3...........................8
Peter..............2..............................
..........2...........................4
Phil.................1............................
............4...........................5

Question 1:
Can I calculate in a query sum totals for apples (or oranges or all
fruits)
eaten by Mike and Peter together (in one record) and ignore John and Phil?

For example:
Name...Number eaten of apples......Number of eaten oranges.....All fruits

Mike+John.............8..............................
..........7...........................15


Question 2:
How can I get sum totals for all excluding just Peter? Or Peter and Phil?

For example

All except
Peter..........9........................11........................20

Your help will be very much appreciated!

Aleksandr