Top Values Not Working

[Alex]

I have the following query with the Top Values property set to 10. I am not
getting the expected results. The query is resulting in only the top 6
instead of the top 10. I have duplicates, but I don't think that should
matter??? Any ideas why? Thanks.

SELECT tbl_QualityLog.[Part #], Count(tbl_QualityLog.[Def Qnty]) AS
[CountOfDef Qnty]
FROM tbl_QualityLog
WHERE (((tbl_QualityLog.[Date Fnd]) Between
[forms]![frm_Pick_Top_10_Count]![BegDatetxt] And
[forms]![frm_Pick_Top_10_Count]![enddatetxt]))
GROUP BY tbl_QualityLog.[Part #]
ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

 

[Michel Walsh]

If the WHERE clause restricts the set of records to 6 different values of
Part#, your query making groups based on Part# values, that is probably why
you have only 6 records ( 6 groups ) in the result.



Vanderghast, Access MVP

 

[Alex]

If the Top Values property is set to All, I get hundreds of records.

If the WHERE clause restricts the set of records to 6 different values of
Part#, your query making groups based on Part# values, that is probably why
you have only 6 records ( 6 groups ) in the result.



Vanderghast, Access MVP

I have the following query with the Top Values property set to 10. I am
not
getting the expected results. The query is resulting in only the top 6
instead of the top 10. I have duplicates, but I don't think that should
matter??? Any ideas why? Thanks.

SELECT tbl_QualityLog.[Part #], Count(tbl_QualityLog.[Def Qnty]) AS
[CountOfDef Qnty]
FROM tbl_QualityLog
WHERE (((tbl_QualityLog.[Date Fnd]) Between
[forms]![frm_Pick_Top_10_Count]![BegDatetxt] And
[forms]![frm_Pick_Top_10_Count]![enddatetxt]))
GROUP BY tbl_QualityLog.[Part #]
ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

 

[Michel Walsh]

Just to be sure, can you double check that the query you posted *IS* the
right query. In practice, the SQL statement should have the words

TOP 10

and it does not, in what you posted. And when you say you 'select all', do
you have the GROUP BY clause.


I suspect you work with two totally different queries having very similar
names.



If not, can you send me you zipped database with the problem? my mail is
vanderghast at msn, dot com.




Vanderghast, Access MVP

If the Top Values property is set to All, I get hundreds of records.

If the WHERE clause restricts the set of records to 6 different values of
Part#, your query making groups based on Part# values, that is probably
why
you have only 6 records ( 6 groups ) in the result.



Vanderghast, Access MVP

I have the following query with the Top Values property set to 10. I am
not
getting the expected results. The query is resulting in only the top 6
instead of the top 10. I have duplicates, but I don't think that
should
matter??? Any ideas why? Thanks.

SELECT tbl_QualityLog.[Part #], Count(tbl_QualityLog.[Def Qnty]) AS
[CountOfDef Qnty]
FROM tbl_QualityLog
WHERE (((tbl_QualityLog.[Date Fnd]) Between
[forms]![frm_Pick_Top_10_Count]![BegDatetxt] And
[forms]![frm_Pick_Top_10_Count]![enddatetxt]))
GROUP BY tbl_QualityLog.[Part #]
ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

 

[Alex]

I must have set the Top Values property before I copied the SQL. But, it's
definitely set to 10 and it's definitely not working; only giving me the top
6. I'll keep trying. Thanks.

Just to be sure, can you double check that the query you posted *IS* the
right query. In practice, the SQL statement should have the words

TOP 10

and it does not, in what you posted. And when you say you 'select all', do
you have the GROUP BY clause.


I suspect you work with two totally different queries having very similar
names.



If not, can you send me you zipped database with the problem? my mail is
vanderghast at msn, dot com.




Vanderghast, Access MVP

If the Top Values property is set to All, I get hundreds of records.

If the WHERE clause restricts the set of records to 6 different values of
Part#, your query making groups based on Part# values, that is probably
why
you have only 6 records ( 6 groups ) in the result.



Vanderghast, Access MVP


I have the following query with the Top Values property set to 10. I am
not
getting the expected results. The query is resulting in only the top 6
instead of the top 10. I have duplicates, but I don't think that
should
matter??? Any ideas why? Thanks.

SELECT tbl_QualityLog.[Part #], Count(tbl_QualityLog.[Def Qnty]) AS
[CountOfDef Qnty]
FROM tbl_QualityLog
WHERE (((tbl_QualityLog.[Date Fnd]) Between
[forms]![frm_Pick_Top_10_Count]![BegDatetxt] And
[forms]![frm_Pick_Top_10_Count]![enddatetxt]))
GROUP BY tbl_QualityLog.[Part #]
ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;