Access query help (1 Viewer)

[bala2020]

Hi All,

Iam writing a query from Windows form application to access db to retrieve records .

The sample table format is shown below

SeatNumber EmpID Month
AXGFG123 298029 3
AXGFG123 298038 3
CALBN154 298085 3
CALBN154 298085 3
CALBN154 298085 3
CALBN154 298085 3
TIT9AA098 301772 3
TIT9AA098 301773 3

If same seat no is allocated to two different employees i need to pull those records alone
output should be
AXGFG123 298029 3
AXGFG123 298038 3
TIT9AA098 301772 3
TIT9AA098 301773 3

So to pull those seats which are allocated to 3 or more people i used the following query
Select seat.SeatNumber FROM Seat INNER JOIN [SELECT seat.EmpID ,seat.SeatNumber FROM Seat WHERE Month =" & month GROUP BY seat.SeatNumber,seat.EmpID HAVING (COUNT(seat.SeatNumber) >3)]. as v1 ON seat.SeatNumber = v1.SeatNumber

However iam unable to get the unique ID ,
my output is as follows which is not needed
CALBN154 298085 3
CALBN154 298085 3
CALBN154 298085 3
CALBN154 298085 3

Can someone please help me as iam stuck here for last 3 days

 

[namliam]

Unfortunately access doesnt allow for the "normal" route most big boy's databases allow, so in access you need to make a 2 step query
1)
Select distinct SeatNumber, EmpID, Month from yourTable

Of if you are intrested in the numbers
Select SeatNumber, EmpID, Month, count(*) as theCount from yourTable group by SeatNumber, EmpID, Month

Save this query as qryStep1

2)
Select SeatNumber, Count(EmpID) CountEmpID
from qryStep1
group by SeatNumber

This should give you the result you are looking for

 

[arnelgp]

you may also try Total query:

SELECT A.SeatNumber, A.EmpID, A.Month
FROM YourTableName AS A INNER JOIN YourTableName AS B ON (A.SeatNumber = B.SeatNumber) AND (A.Month = B.Month)
WHERE (((B.EmpID)<>[A].[EmpID]))
GROUP BY A.SeatNumber, A.EmpID, A.Month;

 

[bala2020]

Wonderful, thank you @ arnelgp, namliam