If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
GRUOPING + SUMMING AND COUNTING
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. |
#2
|
|||
|
|||
GRUOPING + SUMMING AND COUNTING
Haas wrote:
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. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
GRUOPING + SUMMING AND COUNTING
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 "Haas" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|