M
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dirk Goldgar
Note: it's best to include the details of your question in the body of
the message. Don't just put a question in the subject and leave the
body blank. Usually you'll need to provide more information than will
fit in the subject line, anyway, and also not all of a long subject line
may be seen.
You don't really give enough information for a solid answer -- such as
"top 5 based on what evaluation?" -- but here's SQL of a sample query
that does the sort of thing I think you're asking.
SELECT MyTable.*
FROM MyTable
WHERE MyTable.ID In
(
SELECT TOP 5 T.ID from MyTable As T
WHERE T.Category = MyTable.Category
ORDER BY Revenue DESC
)
ORDER BY MyTable.Category;
the message. Don't just put a question in the subject and leave the
body blank. Usually you'll need to provide more information than will
fit in the subject line, anyway, and also not all of a long subject line
may be seen.
You don't really give enough information for a solid answer -- such as
"top 5 based on what evaluation?" -- but here's SQL of a sample query
that does the sort of thing I think you're asking.
SELECT MyTable.*
FROM MyTable
WHERE MyTable.ID In
(
SELECT TOP 5 T.ID from MyTable As T
WHERE T.Category = MyTable.Category
ORDER BY Revenue DESC
)
ORDER BY MyTable.Category;
M
MFH
Thank you for your help. I will remember to put the details in next time.
I just want to take the top 5 values for each record in my query.
For example: if you have a classrom of 30 students and each has taken 20
tests throughout the year. How do you display the top 5 test scores for each
student?
I tried modifying your code below and I have a data mismatch error. I have a
feeling this is not the right code for what I want to do...
Modified code...
SELECT [RG03a CORPS PN Failure Totals b].[Part Number], [RG03a CORPS PN
Failure Totals b].[Total]
FROM [RG03a CORPS PN Failure Totals b]
WHERE [RG03a CORPS PN Failure Totals b].[Total]
IN
(
SELECT TOP 5 [RG03a CORPS PN Failure Totals c].[Total]
FROM [RG03a CORPS PN Failure Totals b]
AS [RG03a CORPS PN Failure Totals c]
WHERE [RG03a CORPS PN Failure Totals c].[Part Number] = [RG03a
CORPS PN Failure Totals b].[Part Number]
ORDER BY [Total]
)
ORDER BY [RG03a CORPS PN Failure Totals b].[Part Number];
I just want to take the top 5 values for each record in my query.
For example: if you have a classrom of 30 students and each has taken 20
tests throughout the year. How do you display the top 5 test scores for each
student?
I tried modifying your code below and I have a data mismatch error. I have a
feeling this is not the right code for what I want to do...
Modified code...
SELECT [RG03a CORPS PN Failure Totals b].[Part Number], [RG03a CORPS PN
Failure Totals b].[Total]
FROM [RG03a CORPS PN Failure Totals b]
WHERE [RG03a CORPS PN Failure Totals b].[Total]
IN
(
SELECT TOP 5 [RG03a CORPS PN Failure Totals c].[Total]
FROM [RG03a CORPS PN Failure Totals b]
AS [RG03a CORPS PN Failure Totals c]
WHERE [RG03a CORPS PN Failure Totals c].[Part Number] = [RG03a
CORPS PN Failure Totals b].[Part Number]
ORDER BY [Total]
)
ORDER BY [RG03a CORPS PN Failure Totals b].[Part Number];
D
Dirk Goldgar
MFH said:Thank you for your help. I will remember to put the details in next
time.
I just want to take the top 5 values for each record in my query.
For example: if you have a classrom of 30 students and each has taken
20 tests throughout the year. How do you display the top 5 test
scores for each student?
I tried modifying your code below and I have a data mismatch error. I
have a feeling this is not the right code for what I want to do...
Modified code...
SELECT [RG03a CORPS PN Failure Totals b].[Part Number], [RG03a CORPS
PN Failure Totals b].[Total]
FROM [RG03a CORPS PN Failure Totals b]
WHERE [RG03a CORPS PN Failure Totals b].[Total]
IN
(
SELECT TOP 5 [RG03a CORPS PN Failure Totals c].[Total]
FROM [RG03a CORPS PN Failure Totals b]
AS [RG03a CORPS PN Failure Totals c]
WHERE [RG03a CORPS PN Failure Totals c].[Part Number] =
[RG03a CORPS PN Failure Totals b].[Part Number]
ORDER BY [Total]
)
ORDER BY [RG03a CORPS PN Failure Totals b].[Part Number];
Aside from the fact that this line:
ORDER BY [Total]
should have the DESC keyword applied, like this:
ORDER BY [Total] DESC
if you want the five records with the maximum [Total], I don't
immediately see anything wrong with that query. And I don't see
anything there that could give a "data mismatch error". Do you get that
error when you paste the SQL into a query window and run it? Or could
it be that you are using VB code to run this query, and maybe have an
error in that code?
M
MFH
Actually, I want to do this with a query and not from a table. I do not have
any ID associated with this query. The query is based on other queries the
top five values I want to display for each category are based upon totals
from previous queries. Do you have any other suggestions?
any ID associated with this query. The query is based on other queries the
top five values I want to display for each category are based upon totals
from previous queries. Do you have any other suggestions?
Dirk Goldgar said:MFH said:Thank you for your help. I will remember to put the details in next
time.
I just want to take the top 5 values for each record in my query.
For example: if you have a classrom of 30 students and each has taken
20 tests throughout the year. How do you display the top 5 test
scores for each student?
I tried modifying your code below and I have a data mismatch error. I
have a feeling this is not the right code for what I want to do...
Modified code...
SELECT [RG03a CORPS PN Failure Totals b].[Part Number], [RG03a CORPS
PN Failure Totals b].[Total]
FROM [RG03a CORPS PN Failure Totals b]
WHERE [RG03a CORPS PN Failure Totals b].[Total]
IN
(
SELECT TOP 5 [RG03a CORPS PN Failure Totals c].[Total]
FROM [RG03a CORPS PN Failure Totals b]
AS [RG03a CORPS PN Failure Totals c]
WHERE [RG03a CORPS PN Failure Totals c].[Part Number] =
[RG03a CORPS PN Failure Totals b].[Part Number]
ORDER BY [Total]
)
ORDER BY [RG03a CORPS PN Failure Totals b].[Part Number];
Aside from the fact that this line:
ORDER BY [Total]
should have the DESC keyword applied, like this:
ORDER BY [Total] DESC
if you want the five records with the maximum [Total], I don't
immediately see anything wrong with that query. And I don't see
anything there that could give a "data mismatch error". Do you get that
error when you paste the SQL into a query window and run it? Or could
it be that you are using VB code to run this query, and maybe have an
error in that code?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
D
Dirk Goldgar
MFH said:Actually, I want to do this with a query and not from a table. I do
not have any ID associated with this query. The query is based on
other queries the top five values I want to display for each category
are based upon totals from previous queries. Do you have any other
suggestions?
Whether you select from a table or a query is irrelevant, except for the
impact it has on the efficiency of the query. So if, as I guessed
before, "RG03a CORPS PN Failure Totals b" is the name of a query and
not a table, it should have no effect on the structure of the query
you're trying to build. As I said, the query you posted doesn't look to
me as though it should raise an error, so I want to try to figure out
what caused the error you report. That's why I asked the questions I
did, but you haven't answered them: Do you get the error when you paste
the SQL into a query window and run it? Or are you using VB code to run
this query, in which case it would be a good idea to post the code.
M
MFH
It is an SQL code
Dirk Goldgar said:Whether you select from a table or a query is irrelevant, except for the
impact it has on the efficiency of the query. So if, as I guessed
before, "RG03a CORPS PN Failure Totals b" is the name of a query and
not a table, it should have no effect on the structure of the query
you're trying to build. As I said, the query you posted doesn't look to
me as though it should raise an error, so I want to try to figure out
what caused the error you report. That's why I asked the questions I
did, but you haven't answered them: Do you get the error when you paste
the SQL into a query window and run it? Or are you using VB code to run
this query, in which case it would be a good idea to post the code.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
D
Dirk Goldgar
MFH said:It is an SQL code
So, you're saying you pasted that SQL directly into the SQL view of a
new query, and when you changed to datasheet view, you got a "data
mismatch" error? What was the exact error number and message, please?
M
MFH
Yes, that is correct. The error message reads "Data type mismatch in criteria
expression"
expression"
D
Dirk Goldgar
MFH said:Yes, that is correct. The error message reads "Data type mismatch in
criteria expression"
Are you sure all the queries on which this query is based run okay?
I've got a feeling this is going to be difficult to debug without the
queries and tables to work with. If you'd like to send me a cut-down
copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in
size (preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message. If that address isn't visible
to you, you can get it from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried
in spam and viruses.
M
MFH
Thank you Dirk. But I have found the solution. I changed the format in the
column properties to a number format. I guess Access did not like that there
was not a format specified for this column.
column properties to a number format. I guess Access did not like that there
was not a format specified for this column.
D
Dirk Goldgar
MFH said:Thank you Dirk. But I have found the solution. I changed the format
in the column properties to a number format. I guess Access did not
like that there was not a format specified for this column.
Interesting. Congratulations on figuring it out. What format did you
change from/to, and what was the data type of the field in question?
Was it a calculated field? Did you change the format in the table
design, or in one of the queries?
M
MFH
It was a standard number format with 0 decimal places. There was no previous
format. It was not a calculated field but it was based on previous
calculations in previous queries. I just changed the format in the top 5
query and left everything else alone.
format. It was not a calculated field but it was based on previous
calculations in previous queries. I just changed the format in the top 5
query and left everything else alone.
D
Dirk Goldgar
MFH said:It was a standard number format with 0 decimal places. There was no
previous format. It was not a calculated field but it was based on
previous calculations in previous queries. I just changed the format
in the top 5 query and left everything else alone.
Sounds like somewhere along the way, Access guessed incorrectly at the
data type of the result of some expression, but your applying the format
gave it enough information to fix it. All's well that ends well.