Sorting results Query

S

Steve

Hi
I would like to know how to sort some results of a couple of tables.
Help would be most apreciated as my skill level is growing fast but but it
seems not fast enogh at the moment and Im stuck.
I have two tables one called tblCategory and another called tblResults
I am using the Query Wizard ( fantastic tool )
I need to create a query that will put the top 5 results ( Points ) from
each Catagory in a list to display on a results form.
Sounds easy but well it seems I can get a assending list of categories and
have desending on points but I cant seem to make it only show the top 5
points in each Category.
Any help most apreciated as always
Steve - From Australia a land down under or a land on top if your in outa
space.

tblCategory has field CDescription
tblResults has field RPoints
 
S

Steve

whoops sorry folks I was working away and forgot its xmas day eeeek I must
get a life
 
K

KARL DEWEY

Try this query, substituting your table and field names.
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points) AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points)+1)<=5))
ORDER BY Q.Group, Q.Points DESC;
 
S

Steve

Sorry I cant seem to follow the query to be able to substitute the tables and
fields
does Q = table named tblCategory and Q1 = table tblResults
and I dont have any field named Points but I assume this could be my field
RPoints in the table tblResults and I dont have anything called Product.
Im sure it all works but Im unable to get a working query out of it at all.
I know its my lack of skill but Im still unable to do it but thank you for
trying to help.

My tables and fields are as follows

tblResults has a field called RPoints
tblCategory has a field called CDescription

could you let me know what represents what so I can change it correctly as I
cant seem to make it happen.
Steve - From a land down under

KARL DEWEY said:
Try this query, substituting your table and field names.
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points) AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points)+1)<=5))
ORDER BY Q.Group, Q.Points DESC;

--
KARL DEWEY
Build a little - Test a little


Steve said:
Hi
I would like to know how to sort some results of a couple of tables.
Help would be most apreciated as my skill level is growing fast but but it
seems not fast enogh at the moment and Im stuck.
I have two tables one called tblCategory and another called tblResults
I am using the Query Wizard ( fantastic tool )
I need to create a query that will put the top 5 results ( Points ) from
each Catagory in a list to display on a results form.
Sounds easy but well it seems I can get a assending list of categories and
have desending on points but I cant seem to make it only show the top 5
points in each Category.
Any help most apreciated as always
Steve - From Australia a land down under or a land on top if your in outa
space.

tblCategory has field CDescription
tblResults has field RPoints
Click to expand...
Click to expand...
 
S

Steve

Still stuck
Is anybody there - Please
Steve

Steve said:
Sorry I cant seem to follow the query to be able to substitute the tables and
fields
does Q = table named tblCategory and Q1 = table tblResults
and I dont have any field named Points but I assume this could be my field
RPoints in the table tblResults and I dont have anything called Product.
Im sure it all works but Im unable to get a working query out of it at all.
I know its my lack of skill but Im still unable to do it but thank you for
trying to help.

My tables and fields are as follows

tblResults has a field called RPoints
tblCategory has a field called CDescription

could you let me know what represents what so I can change it correctly as I
cant seem to make it happen.
Steve - From a land down under

KARL DEWEY said:
Try this query, substituting your table and field names.
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points) AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points)+1)<=5))
ORDER BY Q.Group, Q.Points DESC;

--
KARL DEWEY
Build a little - Test a little


Steve said:
Hi
I would like to know how to sort some results of a couple of tables.
Help would be most apreciated as my skill level is growing fast but but it
seems not fast enogh at the moment and Im stuck.
I have two tables one called tblCategory and another called tblResults
I am using the Query Wizard ( fantastic tool )
I need to create a query that will put the top 5 results ( Points ) from
each Catagory in a list to display on a results form.
Sounds easy but well it seems I can get a assending list of categories and
have desending on points but I cant seem to make it only show the top 5
points in each Category.
Any help most apreciated as always
Steve - From Australia a land down under or a land on top if your in outa
space.

tblCategory has field CDescription
tblResults has field RPoints
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Steve,
I tried Karl's example and was able to show the result you wanted.
Here is what I did:
--create a new query using Category and Results in such a way that you end
up with a query with 2 columns
Column1 Column2
CatName RPoints
--this query should show all the RPoints for each CatName
--save this query as qryCatPts

Now use Karl's answer like this:

SELECT Q.CatName, Q.RPoints, (SELECT COUNT(*) FROM qryCatPts As Q1
WHERE Q1.[CatName] = Q.[CatName]
AND Q1.RPoints >= Q.RPoints) AS Rank
FROM qryCatPts AS Q
WHERE ((((SELECT COUNT(*) FROM qryCatPts Q1
WHERE Q1.[CatName] = Q.[CatName]
AND Q1.RPoints >= Q.RPoints)+1)<=5))
ORDER BY Q.CatName, Q.RPoints DESC;

You can see that both Q and Q1 are aliases
instead of Product I used the name of the query -- qryCatPts
instead of Group I used CatName
instead of Points I used RPoints

Jeanette Cunningham


Steve said:
Still stuck
Is anybody there - Please
Steve

Steve said:
Sorry I cant seem to follow the query to be able to substitute the tables
and
fields
does Q = table named tblCategory and Q1 = table tblResults
and I dont have any field named Points but I assume this could be my
field
RPoints in the table tblResults and I dont have anything called Product.
Im sure it all works but Im unable to get a working query out of it at
all.
I know its my lack of skill but Im still unable to do it but thank you
for
trying to help.

My tables and fields are as follows

tblResults has a field called RPoints
tblCategory has a field called CDescription

could you let me know what represents what so I can change it correctly
as I
cant seem to make it happen.
Steve - From a land down under

KARL DEWEY said:
Try this query, substituting your table and field names.
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points) AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points)+1)<=5))
ORDER BY Q.Group, Q.Points DESC;

--
KARL DEWEY
Build a little - Test a little


:

Hi
I would like to know how to sort some results of a couple of tables.
Help would be most apreciated as my skill level is growing fast but
but it
seems not fast enogh at the moment and Im stuck.
I have two tables one called tblCategory and another called
tblResults
I am using the Query Wizard ( fantastic tool )
I need to create a query that will put the top 5 results ( Points )
from
each Catagory in a list to display on a results form.
Sounds easy but well it seems I can get a assending list of
categories and
have desending on points but I cant seem to make it only show the top
5
points in each Category.
Any help most apreciated as always
Steve - From Australia a land down under or a land on top if your in
outa
space.

tblCategory has field CDescription
tblResults has field RPoints
Click to expand...
Click to expand...
Click to expand...
 
S

Steve

Thank you so much for your description of how to do the query.
I was able to complete it and its now working fine.
Thank you also to Karl for his time in writing it for me.
I can follow and understand whats going on with it now.

Steve - From a land down under or ontop if your in outa space
Excellent guys

Jeanette Cunningham said:
Steve,
I tried Karl's example and was able to show the result you wanted.
Here is what I did:
--create a new query using Category and Results in such a way that you end
up with a query with 2 columns
Column1 Column2
CatName RPoints
--this query should show all the RPoints for each CatName
--save this query as qryCatPts

Now use Karl's answer like this:

SELECT Q.CatName, Q.RPoints, (SELECT COUNT(*) FROM qryCatPts As Q1
WHERE Q1.[CatName] = Q.[CatName]
AND Q1.RPoints >= Q.RPoints) AS Rank
FROM qryCatPts AS Q
WHERE ((((SELECT COUNT(*) FROM qryCatPts Q1
WHERE Q1.[CatName] = Q.[CatName]
AND Q1.RPoints >= Q.RPoints)+1)<=5))
ORDER BY Q.CatName, Q.RPoints DESC;

You can see that both Q and Q1 are aliases
instead of Product I used the name of the query -- qryCatPts
instead of Group I used CatName
instead of Points I used RPoints

Jeanette Cunningham


Steve said:
Still stuck
Is anybody there - Please
Steve

Steve said:
Sorry I cant seem to follow the query to be able to substitute the tables
and
fields
does Q = table named tblCategory and Q1 = table tblResults
and I dont have any field named Points but I assume this could be my
field
RPoints in the table tblResults and I dont have anything called Product.
Im sure it all works but Im unable to get a working query out of it at
all.
I know its my lack of skill but Im still unable to do it but thank you
for
trying to help.

My tables and fields are as follows

tblResults has a field called RPoints
tblCategory has a field called CDescription

could you let me know what represents what so I can change it correctly
as I
cant seem to make it happen.
Steve - From a land down under

:

Try this query, substituting your table and field names.
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points) AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points >= Q.Points)+1)<=5))
ORDER BY Q.Group, Q.Points DESC;

--
KARL DEWEY
Build a little - Test a little


:

Hi
I would like to know how to sort some results of a couple of tables.
Help would be most apreciated as my skill level is growing fast but
but it
seems not fast enogh at the moment and Im stuck.
I have two tables one called tblCategory and another called
tblResults
I am using the Query Wizard ( fantastic tool )
I need to create a query that will put the top 5 results ( Points )
from
each Catagory in a list to display on a results form.
Sounds easy but well it seems I can get a assending list of
categories and
have desending on points but I cant seem to make it only show the top
5
points in each Category.
Any help most apreciated as always
Steve - From Australia a land down under or a land on top if your in
outa
space.

tblCategory has field CDescription
tblResults has field RPoints
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rank query results 11
Top 5 by group 0
Query Returning Multiple Results 1
Like / IIF issues (as query criteria) 2
IIf problem with <> 10
Display Query Results on top of a Form 1
Sorting Query Results - Advice Please 5
Sorting 2

Top