Get distribution of data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query showing e.g. 800 data values in the range from 0 to 50. I
would now like to see the distribution - i.e. how many percent 0, how many
percent 1, 2, 3...etc.

Is there an easy way of doing that?

I can run through all my data and count for each value - but I dont want
that if I could just make a query.
 
SELECT YourField, Count(YourField) as FreqCount
FROM YourTable
GROUP BY YourField

If you are doing this in the query grid.
-- Add YourField to the grid TWO times
-- Select View: Totals from the menu bar
-- Change Group By to Count under the second copy of Your Field
 
Hi, Diane,

SELECT DataValueField, Count(DataValueField) As CountOfDataValueField FROM
tblYourTableName GROUP BY DataValueField;

That should work,

Sam
 
Oops, that won't work. I forgot you were looking for percentage, not count.

What you need is TWO queries. The first one is:

SELECT Count(DataVaueField) As CountOfDataValueField FROM tblYourTableName;

Save that query, maybe call it qrySubDataValue. Now make a new query, as
follows:

SELECT DataValueField, Format(Count(DataValueField)/CountOfDataValueField,
"Percent") AS Percentage
FROM tblYourTableName, qrySubDataValue
GROUP BY DataValueField;

I think that should work now. Sorry, again.

Sam
Hi, Diane,

SELECT DataValueField, Count(DataValueField) As CountOfDataValueField FROM
tblYourTableName GROUP BY DataValueField;

That should work,

Sam
I have a query showing e.g. 800 data values in the range from 0 to 50. I
would now like to see the distribution - i.e. how many percent 0, how many
Click to expand...
[quoted text clipped - 4 lines]
I can run through all my data and count for each value - but I dont want
that if I could just make a query.
Click to expand...
Click to expand...
 
Hi,


Won't you need a join, or a where clause?



Alternatively, probably slower,

SELECT Fieldname, COUNT(*) /(SELECT COUNT(*) FROM myTable as b WHERE
b.fieldName=a.fieldName)
FORM myTable As a


With your solution, probably faster, but with an inner join, it can be:

SELECT a.DataValueField, Count(*) / CountOfDataValueField
FROM tblYourTableName As a INNER JOIN qrySubDataValue As b
ON a.DataValueField= b.DataValueField
GROUP BY a.DataValueField



Hoping it may help,
Vanderghast, Access MVP



OfficeDev18 via AccessMonster.com said:
Oops, that won't work. I forgot you were looking for percentage, not
count.

What you need is TWO queries. The first one is:

SELECT Count(DataVaueField) As CountOfDataValueField FROM
tblYourTableName;

Save that query, maybe call it qrySubDataValue. Now make a new query, as
follows:

SELECT DataValueField, Format(Count(DataValueField)/CountOfDataValueField,
"Percent") AS Percentage
FROM tblYourTableName, qrySubDataValue
GROUP BY DataValueField;

I think that should work now. Sorry, again.

Sam
Hi, Diane,

SELECT DataValueField, Count(DataValueField) As CountOfDataValueField FROM
tblYourTableName GROUP BY DataValueField;

That should work,

Sam
I have a query showing e.g. 800 data values in the range from 0 to 50. I
would now like to see the distribution - i.e. how many percent 0, how
many
Click to expand...
[quoted text clipped - 4 lines]
I can run through all my data and count for each value - but I dont want
that if I could just make a query.
Click to expand...
Click to expand...
Click to expand...
 
Thanks All - but I forgot to say that my values are decimal numbers....

i.e. it could be
3.72
11.89
44.67
12.41
etc.

How do I make the distribution - now showing numbers in the ranges
0-1
1-2
2-3
3-4
....
49-50
 
Diane,

Are you sure you want this in Access? This sounds like a problem best solved
in Excel. To get there, just run your query(ies) in Datasheet view and click
on Tools-->Analyze in Excel. Once in Excel, you can get Excel to display a
chart that you can play with and get any kind of distribution you want.

Sam

Michel said:
Hi,

Won't you need a join, or a where clause?

Alternatively, probably slower,

SELECT Fieldname, COUNT(*) /(SELECT COUNT(*) FROM myTable as b WHERE
b.fieldName=a.fieldName)
FORM myTable As a

With your solution, probably faster, but with an inner join, it can be:

SELECT a.DataValueField, Count(*) / CountOfDataValueField
FROM tblYourTableName As a INNER JOIN qrySubDataValue As b
ON a.DataValueField= b.DataValueField
GROUP BY a.DataValueField

Hoping it may help,
Vanderghast, Access MVP
Oops, that won't work. I forgot you were looking for percentage, not
count.
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 
Force the numbers into a range by rounding them, truncating them, or
rounding them up. Truncation is simplest

Truncation
SELECT Int(YourField) as Range, Count(YourField) as FreqCount
FROM YourTable
GROUP BY Int(YourField)
=0 and <1 are in Group 0
=1 and <2 are in Group 1
Click to expand...

Round up (only works with positive numbers)
SELECT -Int(-YourField) as Range,
Count(YourField) as FreqCount
FROM YourTable
GROUP BY -Int(-YourField)

= 0 is in group 0
 
Back
Top