A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

GRUOPING + SUMMING AND COUNTING



 
 
Thread Tools Display Modes
  #1  
Old July 9th, 2008, 12:44 PM posted to microsoft.public.access.queries
Haas
external usenet poster
 
Posts: 14
Default 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  
Old July 9th, 2008, 01:05 PM posted to microsoft.public.access.queries
Bob Barrows [MVP]
external usenet poster
 
Posts: 441
Default 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  
Old July 9th, 2008, 02:28 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.