Query results limited

E

Eric

I created a table of award nominees last year and based a query on it, sorted
top to bottom according to score. At some point I must have needed to tweak
the query to select only the top three scorers out of the twenty nominees.

I've copied the database to handle this year's competition, and now I need
that query to display the top five scorers instead. However, I have tried
for hours to figure out how it was that I made it display only the top two.
I want to increase that list to the top five.

The interesting thing is, if I copy the query, I still get the top three.
If I build a new query I get all nominees in the results. Can anyone tell me
how to adjust my new query so I can display the top five scorers?
 
E

Eric

I think I see the problem now. Perhaps I should just change that to "SELECT
TOP 5"? I have not idea how I put this in there, I don't recall ever going
into this screen:

SELECT TOP 3 [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
AS Score, [Jury Scores DTA].Nominee, [Jury Scores DTA].[Nominee's
Department], [Jury Scores DTA].Finalist
FROM [Jury Scores DTA]
ORDER BY [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
DESC;
 
K

KARL DEWEY

If any of these - [Jury Scores DTA]![Juror 6 Score] - are null it will not
work.
Use the Nz function -- Nz([Jury Scores DTA]![Juror 6 Score], 0) for
all.
--
KARL DEWEY
Build a little - Test a little


Eric said:
I think I see the problem now. Perhaps I should just change that to "SELECT
TOP 5"? I have not idea how I put this in there, I don't recall ever going
into this screen:

SELECT TOP 3 [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
AS Score, [Jury Scores DTA].Nominee, [Jury Scores DTA].[Nominee's
Department], [Jury Scores DTA].Finalist
FROM [Jury Scores DTA]
ORDER BY [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
DESC;


KARL DEWEY said:
Post the SQL of your query.
Click to expand...
Click to expand...
 
E

Eric

As it happens all scores have been entered, and if someone got a 0, that is
entered too. Do I still need to use the Nz function then? What does Nz
stand for and what is it?

KARL DEWEY said:
If any of these - [Jury Scores DTA]![Juror 6 Score] - are null it will not
work.
Use the Nz function -- Nz([Jury Scores DTA]![Juror 6 Score], 0) for
all.
--
KARL DEWEY
Build a little - Test a little


Eric said:
I think I see the problem now. Perhaps I should just change that to "SELECT
TOP 5"? I have not idea how I put this in there, I don't recall ever going
into this screen:

SELECT TOP 3 [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
AS Score, [Jury Scores DTA].Nominee, [Jury Scores DTA].[Nominee's
Department], [Jury Scores DTA].Finalist
FROM [Jury Scores DTA]
ORDER BY [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
DESC;


KARL DEWEY said:
Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

I created a table of award nominees last year and based a query on it, sorted
top to bottom according to score. At some point I must have needed to tweak
the query to select only the top three scorers out of the twenty nominees.

I've copied the database to handle this year's competition, and now I need
that query to display the top five scorers instead. However, I have tried
for hours to figure out how it was that I made it display only the top two.
I want to increase that list to the top five.

The interesting thing is, if I copy the query, I still get the top three.
If I build a new query I get all nominees in the results. Can anyone tell me
how to adjust my new query so I can display the top five scorers?
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Null Zero. Not need if all have data all the time. Replaces nulls with
zero.

--
KARL DEWEY
Build a little - Test a little


Eric said:
As it happens all scores have been entered, and if someone got a 0, that is
entered too. Do I still need to use the Nz function then? What does Nz
stand for and what is it?

KARL DEWEY said:
If any of these - [Jury Scores DTA]![Juror 6 Score] - are null it will not
work.
Use the Nz function -- Nz([Jury Scores DTA]![Juror 6 Score], 0) for
all.
--
KARL DEWEY
Build a little - Test a little


Eric said:
I think I see the problem now. Perhaps I should just change that to "SELECT
TOP 5"? I have not idea how I put this in there, I don't recall ever going
into this screen:

SELECT TOP 3 [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
AS Score, [Jury Scores DTA].Nominee, [Jury Scores DTA].[Nominee's
Department], [Jury Scores DTA].Finalist
FROM [Jury Scores DTA]
ORDER BY [Jury Scores DTA]![Juror 1 Score]+[Jury Scores DTA]![Juror 2
Score]+[Jury Scores DTA]![Juror 3 Score]+[Jury Scores DTA]![Juror 4
Score]+[Jury Scores DTA]![Juror 5 Score]+[Jury Scores DTA]![Juror 6
Score]+[Jury Scores DTA]![Juror 7 Score]+[Jury Scores DTA]![Juror 8
Score]+[Jury Scores DTA]![Juror 9 Score]+[Jury Scores DTA]![Juror 10
Score]+[Jury Scores DTA]![Juror 11 Score]+[Jury Scores DTA]![Juror 12 Score]
DESC;


:

Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

I created a table of award nominees last year and based a query on it, sorted
top to bottom according to score. At some point I must have needed to tweak
the query to select only the top three scorers out of the twenty nominees.

I've copied the database to handle this year's competition, and now I need
that query to display the top five scorers instead. However, I have tried
for hours to figure out how it was that I made it display only the top two.
I want to increase that list to the top five.

The interesting thing is, if I copy the query, I still get the top three.
If I build a new query I get all nominees in the results. Can anyone tell me
how to adjust my new query so I can display the top five scorers?
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

Removing Certain Records from a Query and Report 1
Display Query Results on top of a Form 1
Top 5 by group 0
List top five results based on age grouping 2
How to display zero value in blank fields of Cross Tab query 0
QBF & Query Malfunction 0
How to Change criteria on a saved query using VBA 0
Top Two Items per customer 7

Top