Top 10 For Each 2

[sfalin]

I have a table of records housing items processed by various persons. I need to get a random quality sampling of data for each person in a given time period. The kicker is I need it to pull 10 records for each person. The query is pulling as desired except I can't figure out how to tell the query to pull 10 records for each person. The query is set up as follows.

SELECT TOP 10 DocTable.EndEID, DocTable.CaseID, DocTable.Account, DocTable.Amount, DocTable.LogEID, DocTable.EndDate, DocTable.[1stEID], DocTable.[2ndEID], DocTable.[3rdEID], DocTable.Status
FROM DocTable
WHERE (((DocTable.EndDate) Between Date()-7 And Date()))
ORDER BY Rnd([Account]);

This pulls the top 10 of all records no matter the [EndEID]. Any help would be appreciated.

 

[rajeshkanna]

select x.*
from DocTable as x
where <Count> > (select count(*) from DocTable as y where x.<PrimaryKey> > y.<PrimaryKey> and x.Account = y.Account)


Anything between <> is place holders

 

[sfalin]

My coding skills are lacking. I have just begun SQL coding. I can't tell from your code how it could limit the results to 10 items for each and keep the results random. I need it spelled out.

Thanx.

 

[SockMonkey]

I am encountering a very similar issue and the code you have placed does not make sense Rajesh.

I have a primary table with a primary key MktgNo
and I have a related table with multiple records for each MktgNo. Each record is dated.

I am trying to create a query that pulls the main record and only the most recent single record from related table.

The TOP statement only seems to work in relation to all records and not per set of Unique ids.

Table1:
MktgNo (PrimaryKey)
Property Name
Tenant Name


Table2:
MktgNo
ActivityDate
Activity
Comments

Table1: 1 --> oo Table2

For each record in Table 1 I want to show only the most recent record in Table 2

I believe the use of a subquery is the way to go but I have not had luck with any attempts.

If I come up with anything I will post it here.

Robert

 

[rajeshkanna]

Hi sfalin,

You can place the required number of records in the place of <Count>,

If you need to get 10 records then replace <Count> with 10.
thats all.

The query just arrange and list first said count(10) records for each Account.

x and y are just for table's alias names.

thankz
rajeshkanna

 

[SockMonkey]

Rajesh

Thanks for the update. I have the following SQL statement:

SELECT x.*
FROM tblExclusiveMktgHistory AS x
WHERE (((1)>(SELECT Count(*) FROM tblExclusiveMktgHistory AS y WHERE x.RecordID > y.RecordID AND x.ExclMktgNo = y.ExclMktgNo)))
ORDER BY x.ExclMktgNo DESC , x.ActivityDate DESC;

which almost works except for the fact that it is taking the first record as opposed to the most recent record

I tried adding the following into the subquery:
ORDER BY y.ActivityDate DESC
however this generates an error that the specified expression &quot;y.ActivityDate&quot; is not part of the aggregate function.

However I just reversed the sign in the subquery part to
WHERE x.RecordID < y.RecordID and it seems to have fixed the problem.

Hope this helps you sfalin.

Robert

 

[sfalin]

Well, it got too complicated for me. Sorry guys. BUT!! But, I did find exactly what I needed at MS Support (

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q109319).

Have to create a grouping report with a counter macro. Much easier and works perfectly since I needed a report anyway. Thanks for all your time.