Count & Filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this in table:


Joe xyz
Fred xyz
Lou xyz
Fred xyz
Lou xyz
Lou xyz

(the xyz is just random data...there are other data columns..and a primary
key field too)

What I need is to return just the top occuring [name] field records. In
this case the Lou records because there are three.

Or return the top 2 occuring name field records. In this case it would be
the Lou and Fred records.

I will set the top X occuring as a fixed value - probably 10 in the real
table. (I don't need to make it variable by the user - but I will probably
vary it occasionally.)

Have surfed around thinking this to be a common need - but am still not
quite clear on the approach to take...thanks in advance to input....
 
In plain English, that's "I would like to be able to specify the number
of Top Values in a top values query at runtime"? if you want to do it
in the QBE grid, it's easy. You just enter the number of records you
want returned in the box. If you want to do it in code, it's a PITA.
You have to split the query SQL at the number of top values and then
put it back together with your own number....

Yeah, the QBE grid is a pretty cool thing...
 
It sounds to me you want a query with an SQL String like:

SELECT *
FROM Table1 As Main
WHERE Main.FirstName In
( SELECT TOP 1 Sub.FirstName
FROM Table1 As Sub
GROUP BY Sub.FirstName
ORDER BY Count(Sub.FirstName) DESC
);


If you want a variable TOP ???, then I guess you can use the QueryDef object
to modify the TOP predicate clause of the SQL String in code.
 
thanks to you both.....

(The Top Values in design view does not work because it does not return the
most recurring records that are intermixed - it simply will return the first
x records....)

Van, does this code go into the zoom of one new calculated column in design
view....or is this a pure sql driven query overall.... I have about 6 other
field columns of data ( the xyz stuff ) that I need returning besides the
name field.

Also I see you have: "WHERE Main.FirstName In"
and am wondering what the "In" relates to...?

same question for the : "DESC" at the end ....

obviously need to brush up on sql syntax....but thanks for assist..
--
NTC


Van T. Dinh said:
It sounds to me you want a query with an SQL String like:

SELECT *
FROM Table1 As Main
WHERE Main.FirstName In
( SELECT TOP 1 Sub.FirstName
FROM Table1 As Sub
GROUP BY Sub.FirstName
ORDER BY Count(Sub.FirstName) DESC
);


If you want a variable TOP ???, then I guess you can use the QueryDef object
to modify the TOP predicate clause of the SQL String in code.

--
HTH
Van T. Dinh
MVP (Access)



NetworkTrade said:
I have this in table:


Joe xyz
Fred xyz
Lou xyz
Fred xyz
Lou xyz
Lou xyz

(the xyz is just random data...there are other data columns..and a primary
key field too)

What I need is to return just the top occuring [name] field records. In
this case the Lou records because there are three.

Or return the top 2 occuring name field records. In this case it would be
the Lou and Fred records.

I will set the top X occuring as a fixed value - probably 10 in the real
table. (I don't need to make it variable by the user - but I will
probably
vary it occasionally.)

Have surfed around thinking this to be a common need - but am still not
quite clear on the approach to take...thanks in advance to input....
Click to expand...
Click to expand...
 
hey - just to let you know that it works great - much thanx...still a little
foggy on the logic of the syntax but that is my own lack of experience.
thanks again....
--
NTC


Van T. Dinh said:
It sounds to me you want a query with an SQL String like:

SELECT *
FROM Table1 As Main
WHERE Main.FirstName In
( SELECT TOP 1 Sub.FirstName
FROM Table1 As Sub
GROUP BY Sub.FirstName
ORDER BY Count(Sub.FirstName) DESC
);


If you want a variable TOP ???, then I guess you can use the QueryDef object
to modify the TOP predicate clause of the SQL String in code.

--
HTH
Van T. Dinh
MVP (Access)



NetworkTrade said:
I have this in table:


Joe xyz
Fred xyz
Lou xyz
Fred xyz
Lou xyz
Lou xyz

(the xyz is just random data...there are other data columns..and a primary
key field too)

What I need is to return just the top occuring [name] field records. In
this case the Lou records because there are three.

Or return the top 2 occuring name field records. In this case it would be
the Lou and Fred records.

I will set the top X occuring as a fixed value - probably 10 in the real
table. (I don't need to make it variable by the user - but I will
probably
vary it occasionally.)

Have surfed around thinking this to be a common need - but am still not
quite clear on the approach to take...thanks in advance to input....
Click to expand...
Click to expand...
 
All of the SQL elements used are explained in details in the JET SQL
Reference section of Access Help ... If you are not familiar with (JET) SQL,
browse through this section and probably a good book on JET SQL or general
SQL.
 
Back
Top