Sorting Records

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

Guest

I have a table with several hundred records. I have a numerical field with
different values that I want to sort. I want to run a query that shows only
ranges not all of the records. For example, how many records with a value
less than ten, how many with values 10-20, and how many with values over 20.

Any help is greatly appreciated.
 
The post header says SORTING but you talk about COUNTING. Below is a union
query that might work for you. Edit the table and field names.

SELECT Count([YourTable].[YourField]) AS [Range less than 10], NULL AS
[Range 10 - 20], NULL AS [Range Greater than 20]
FROM [YourTable]
WHERE ((([YourTable].[YourField]) < 10))
UNION ALL SELECT NULL, Count([YourTable].[YourField] AS [Range 10 - 20], NULL
FROM [YourTable]
WHERE ((([YourTable].[YourField]) Between 10 And 20))
UNION ALL SELECT NULL, NULL, Count([YourTable].[YourField] AS [Range
Greater than 20]
FROM [YourTable]
WHERE ((([YourTable].[YourField]) > 20));
 
Perhaps the following will work for you


SELECT Abs(Sum([Numberfield] < 10)) as LessThan10
, Abs(Sum([Numberfield] >=10 and [NumberField] <=20)) as [10To20]
, Abs(Sum([Numberfield] >20 )) as Over20
FROM YourTableName


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

Karl,
if you're still online I would still be interested to know the answer to
that question about UNION ALL.

Thanks to both of you.

John Spencer said:
Perhaps the following will work for you


SELECT Abs(Sum([Numberfield] < 10)) as LessThan10
, Abs(Sum([Numberfield] >=10 and [NumberField] <=20)) as [10To20]
, Abs(Sum([Numberfield] >20 )) as Over20
FROM YourTableName


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

matt22 said:
I have a table with several hundred records. I have a numerical field with
different values that I want to sort. I want to run a query that shows
only
ranges not all of the records. For example, how many records with a value
less than ten, how many with values 10-20, and how many with values over
20.

Any help is greatly appreciated.
Click to expand...
Click to expand...
 
This seems to be what I am looking for but I keep getting a "Data type
mismatch in criteria expression" message when I run the query. I've tired to
debug the query and here is what I found out:

I can run each individual select statement and I get the results I am
looking for for that individual statement. I can also run any combination of
two select statements and get the correct results back. However, when I run
all three select statements I get the error above.

Is there a problem when using more than two select statements and Union All?

Also, is there a way to have the results display all on the same row. When I
run two select statements there are three columns. One with data in it and
two columns with null value.

Thanks.

KARL DEWEY said:
The post header says SORTING but you talk about COUNTING. Below is a union
query that might work for you. Edit the table and field names.

SELECT Count([YourTable].[YourField]) AS [Range less than 10], NULL AS
[Range 10 - 20], NULL AS [Range Greater than 20]
FROM [YourTable]
WHERE ((([YourTable].[YourField]) < 10))
UNION ALL SELECT NULL, Count([YourTable].[YourField] AS [Range 10 - 20], NULL
FROM [YourTable]
WHERE ((([YourTable].[YourField]) Between 10 And 20))
UNION ALL SELECT NULL, NULL, Count([YourTable].[YourField] AS [Range
Greater than 20]
FROM [YourTable]
WHERE ((([YourTable].[YourField]) > 20));

--
KARL DEWEY
Build a little - Test a little


matt22 said:
I have a table with several hundred records. I have a numerical field with
different values that I want to sort. I want to run a query that shows only
ranges not all of the records. For example, how many records with a value
less than ten, how many with values 10-20, and how many with values over 20.

Any help is greatly appreciated.
Click to expand...
Click to expand...
 
Is there a problem when using more than two select statements and Union All?
Union queries always have two or more select statements.
Please post your SQL statement.
You could follow it up with a totals query.

John Spencer's answer is the cleanest but I do not understand how it works.
To me the total (Sum) of numbers 1 through 9 equals 45.

--
KARL DEWEY
Build a little - Test a little


matt22 said:
This seems to be what I am looking for but I keep getting a "Data type
mismatch in criteria expression" message when I run the query. I've tired to
debug the query and here is what I found out:

I can run each individual select statement and I get the results I am
looking for for that individual statement. I can also run any combination of
two select statements and get the correct results back. However, when I run
all three select statements I get the error above.

Is there a problem when using more than two select statements and Union All?

Also, is there a way to have the results display all on the same row. When I
run two select statements there are three columns. One with data in it and
two columns with null value.

Thanks.

KARL DEWEY said:
The post header says SORTING but you talk about COUNTING. Below is a union
query that might work for you. Edit the table and field names.

SELECT Count([YourTable].[YourField]) AS [Range less than 10], NULL AS
[Range 10 - 20], NULL AS [Range Greater than 20]
FROM [YourTable]
WHERE ((([YourTable].[YourField]) < 10))
UNION ALL SELECT NULL, Count([YourTable].[YourField] AS [Range 10 - 20], NULL
FROM [YourTable]
WHERE ((([YourTable].[YourField]) Between 10 And 20))
UNION ALL SELECT NULL, NULL, Count([YourTable].[YourField] AS [Range
Greater than 20]
FROM [YourTable]
WHERE ((([YourTable].[YourField]) > 20));

--
KARL DEWEY
Build a little - Test a little


matt22 said:
I have a table with several hundred records. I have a numerical field with
different values that I want to sort. I want to run a query that shows only
ranges not all of the records. For example, how many records with a value
less than ten, how many with values 10-20, and how many with values over 20.

Any help is greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top