Hello Sir,
I am having problem in formulating a SQL query . Please help in
finding out the solution:
I need a SQL query in whichi want to select N number of employee from
table Training_emp such that
----> There are at max 2 emp from any department

MyTableStructure is:
Training_emp:
Emp_code
Topic
Session
Month
Inn_add
Attend
Date

Emp_master:
Emp_code
DeptName

now what i want is..to select say 20 employees from Training_emp
table....
suct that no more that two employeees from same departnment are
selected
My Back end is MS access

Hello,

Jet sql (Access Jet) does not have the capability to perform such a
query in one shot -- that would be transact sql (for sql server).
However, there is a workaround. Quick note: Access is a file based
miniature RDBMS (as opposed to sql server which is server based). As
such - it can only perform 10-30% of the operations of a server based
RDBMS right out of the box. The power of Access lay in the workarounds.
The workarounds could be a little bit tedious but at least they are
available. So here is your workaround:

Sub getTopTwoEmpsFromEachDept()

Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, str2 As String

DoCmd.SetWarnings False
Set DB = CurrentDB
Set RS = DB.OpenRecordset("Select DeptName From Emp_Master Group By
DeptName Order By DeptName")
Do While Not RS.EOF
str1 = RS(0)
DoCmd.RunSql "Insert Into tmp1 Select Top 2 Emp_Code, DeptName From
Emp_Master Where DeptName = '" & str1 & "' Order By Emp_Code"
RS.MoveNext
Loop
DoCmd.SetWarnings True

End Sub

This solution would require that you create a temporary table which I
called tmp1 which will contain the Emp_Code field and the DeptName
field. You loop through your master table and pick up each distinct
DeptName and then select the top two Emp_Codes from each Dept and store
these values in tmp1.

Here is the Tsql (transact Sql) version of this workaround

select * from Emp_Master t1 Where Exists
(Select * from (Select Top 2 Emp_Code, DeptName From Emp_Master t2 Where
t2.DeptName = t1.DeptName Order By DeptName) t3 Where t3.Emp_Code =
t1.Emp_Code

This query assumes that Emp_Code is a unique field. This query is less
busy than the workaround above, but I have not been able to run this
type of query in Access. Thus, the workaround.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich P wrote:
I was wondering why one would need the top 2 of every department,
however it is not mine to reason why. If one wants to open a query from
the db window or from a form to display the records then he could use
your methods.

If it is a report, then I would most likely use MoveLayout,
PrintSection, NextRecord. In A97's help it defined it much better than
A2003. Here's the truth table. from A97

MoveLayout NextRecord PrintSection Description
True True True (Default) Move to the next print location, get the next
record, and print the data.
True False True Move to the next print location, don't advance to the
next record, but print the data. This combination is typically used when
the data in a section requires more space than the layout allows and you
want to print the remaining data in the space that would otherwise be
occupied by the next section.
False True False Skip a record without leaving a blank space on the page.
True True False Skip a record and leave a blank space on the page.
True False False Leave a blank space without skipping a record.
False True True Print the current record on top of the last record as an
overlay.
False False True Not allowed.
False False False Not allowed.

So using a counter that's reset on department grouping, one could print
if the counter is less than 3 (that value 3 could be a variable of
number of emps to print passed to the report so it's not hardcoded),
movelayout is true if less than 3, printsection is true if less than 3,
otherwise movelayout is false is greater 2, and nextrecord is true no
matter what, and printsection false if greater 2. That way I can adjust
by placing a variable of however many emps I want and not hardcode the
number 2. That would be much easier than designing a query to do the
work. But my method is for a report and who knows why the op wants to
do what he/she is doing.

On Mar 29, 4:27*am, richa <richa.19.j... (AT) gmail (DOT) com> wrote:
Do you need to select the employees randomly, so that different people
come up each time? Or can you let this query select the same two
people from each department every time you run the it?