GRUOPING + SUMMING AND COUNTING

[Haas]

I have a table containing the following fields
Code Car type Ownername Ownership%
002 Toyota Pickup John 50
002 Toyota Pickup Odi 50
005 Mercedez Trailer Aish 100
006 Nissan Patrol Ohi 50
007 Toyota Prado Rengo 250


I need to sort by code and then get the total ownership of each car i.e.
code 002 - 100%and then sorting by the total ownership and then count how
many cars has 100% ownership so back and forth, because i have wrong data in
the ownership field so i want to figure out the total ownership of each car.
Any assitance.

 

[Bob Barrows [MVP]]

I have a table containing the following fields
Code Car type Ownername Ownership%
002 Toyota Pickup John 50
002 Toyota Pickup Odi 50
005 Mercedez Trailer Aish 100
006 Nissan Patrol Ohi 50
007 Toyota Prado Rengo 250


I need to sort by code and then get the total ownership of each car
i.e. code 002 - 100%and then sorting by the total ownership and then
count how many cars has 100% ownership so back and forth, because i
have wrong data in the ownership field so i want to figure out the
total ownership of each car. Any assitance.

Could you show us, in row format as you did for the sample data above, the
output you desire from the query you wish to build? I'm having a little
trouble following your description and a picture would really help.

 

[Michel Walsh]

I am not sure I really understand, but maybe:

SELECT code
FROM yourTable
GROUP BY code
HAVING SUM([ownership%]) = 100


could do. Note that if the percentage are value between 0 and 1 rather than
between 0 and 100, try

HAVING SUM([ownership%]) = 1.00

or


HAVING SUM([ownership%]) BETWEEN 0.9999 AND 1.0001


to allow rounding imprecisions



(can also use:

HAVING ABS( SUM([ownership%]) -1.00 ) < 1E-4

if you prefer)






You can use

HAVING SUM([ownership%]) <> 100


or


HAVING NOT( SUM([ownership%]) BETWEEN 0.9999 AND 1.0001 )

to get the codes not having a total ownership% of 100%.




Vanderghast, Access MVP