Top Values Not Working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 27 May 2008 20:47 GMT

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;

Reply to this Message

Michel Walsh - 27 May 2008 20:58 GMT

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
[quoted text clipped - 10 lines]
> GROUP BY tbl_QualityLog.[Part #]
> ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

Reply to this Message

Alex - 27 May 2008 21:18 GMT

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
[quoted text clipped - 16 lines]
> > GROUP BY tbl_QualityLog.[Part #]
> > ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

Reply to this Message

Michel Walsh - 27 May 2008 21:31 GMT

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.
>
[quoted text clipped - 20 lines]
>> > GROUP BY tbl_QualityLog.[Part #]
>> > ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

Reply to this Message

Alex - 27 May 2008 21:48 GMT

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
[quoted text clipped - 36 lines]
> >> > GROUP BY tbl_QualityLog.[Part #]
> >> > ORDER BY Count(tbl_QualityLog.[Def Qnty]) DESC;

Reply to this Message