misuser2k7
MIS
I need to filter records in a table in a paticular way
The table looks like this . . . . .
ID TyNum TNum STNum TName TGender TAge
187771 1 0 0 Alex Male 21
187771 1 1 1 Alex Male 21
187771 1 1 2 Alex Male 21
187771 1 1 3 Alex Male 21
187771 2 0 0 Alex Male 21
187771 2 1 1 Alex Male 21
187771 2 1 2 Alex Male 21
187771 2 1 3 Alex Male 21
187771 3 0 0 Alex Male 21
187772 1 0 0 Beth Female 22
187772 1 1 1 Beth Female 22
187772 1 1 2 Beth Female 22
187772 2 0 0 Beth Female 22
187772 2 1 1 Beth Female 22
187772 2 1 2 Beth Female 22
187773 1 0 0 Charles Male 20
187773 1 1 1 Charles Male 20
187773 2 0 0 Charles Male 20
187774 1 0 0 Deena Female 24
187774 1 1 1 Deena Female 24
187774 2 0 0 Deena Female 24
187774 2 1 1 Deena Female 24
187774 3 0 0 Deena Female 24
187775 1 0 0 Eric Male 26
187775 1 1 1 Eric Male 26
Table has a compound primary key (ID, TyNum, TNum, STNum)
It needs to hide/filter out the records as indicated . . . . .
ID TyNum TNum STNum TName TGender TAge
187771 1 0 0 Alex Male 21 << Hide/filter out because there are other records with Same TyNum
187771 1 1 1 Alex Male 21 << Display because record/s of same TyNum
187771 1 1 2 Alex Male 21 << Display because record/s of same TyNum
187771 1 1 3 Alex Male 21 << Display because record/s of same TyNum
187771 2 0 0 Alex Male 21 << Hide/filter out because there are other records with Same TyNum
187771 2 1 1 Alex Male 21 << Display because record/s of same TyNum
187771 2 1 2 Alex Male 21 << Display because record/s of same TyNum
187771 2 1 3 Alex Male 21 << Display because record/s of same TyNum
187771 3 0 0 Alex Male 21 << Display because this is the only record for this TyNum
187772 1 0 0 Beth Female 22 << Hide/filter out because there are other records with Same TyNum
187772 1 1 1 Beth Female 22 << Display because record/s of same TyNum
187772 1 1 2 Beth Female 22 << Display because record/s of same TyNum
187772 2 0 0 Beth Female 22 << Hide/filter out because there are other records with Same TyNum
187772 2 1 1 Beth Female 22 << Display because record/s of same TyNum
187772 2 1 2 Beth Female 22 << Display because record/s of same TyNum
187773 1 0 0 Charles Male 20 << Hide/filter out because there are other records with Same TyNum
187773 1 1 1 Charles Male 20 << Display because record/s of same TyNum
187773 2 0 0 Charles Male 20 << Display because this is the only record for this TyNum
187774 1 0 0 Deena Female 24 << Hide/filter out because there are other records with Same TyNum
187774 1 1 1 Deena Female 24 << Display because record/s of same TyNum
187774 2 0 0 Deena Female 24 << Hide/filter out because there are other records with Same TyNum
187774 2 1 1 Deena Female 24 << Display because record/s of same TyNum
187774 3 0 0 Deena Female 24 << Display because this is the only record for this TyNum
187775 1 0 0 Eric Male 26 << Hide/filter out because there are other records with Same TyNum
187775 1 1 1 Eric Male 26 << Display because record/s of same TyNum
So the filtered result should look like this . . . . .
ID TyNum TNum STNum TName TGender TAge
187771 1 1 1 Alex Male 21
187771 1 1 2 Alex Male 21
187771 1 1 3 Alex Male 21
187771 2 1 1 Alex Male 21
187771 2 1 2 Alex Male 21
187771 2 1 3 Alex Male 21
187771 3 0 0 Alex Male 21
187772 1 1 1 Beth Female 22
187772 1 1 2 Beth Female 22
187772 2 1 1 Beth Female 22
187772 2 1 2 Beth Female 22
187773 1 1 1 Charles Male 20
187773 2 0 0 Charles Male 20
187774 1 1 1 Deena Female 24
187774 2 1 1 Deena Female 24
187774 3 0 0 Deena Female 24
187775 1 1 1 Eric Male 26
I tried to do this with a left join query on these two Queries . . . . .
Query4:
SELECT Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge
FROM Table1
GROUP BY Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge;
Query5:
SELECT Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge
FROM Table1
GROUP BY Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge
HAVING (((Table1.TNum)>0) AND ((Table1.STNum)>0));
Left Join query
SELECT Query4.ID, Query4.TyNum, Query4.TNum, Query4.STNum, Query4.TName, Query4.TGender, Query4.TAge
FROM Query4 LEFT JOIN Query5 ON (Query4.STNum = Query5.STNum) AND (Query4.TNum = Query5.TNum) AND (Query4.TyNum = Query5.TyNum) AND (Query4.[ID] = Query5.[ID])
GROUP BY Query4.ID, Query4.TyNum, Query4.TNum, Query4.STNum, Query4.TName, Query4.TGender, Query4.TAge, Query5.ID
HAVING (((Count([Query4].[ID]+[Query4].[TyNum]+[Query4].[TNum]+[Query4].[STNum]))<2) AND ((Query5.ID) Is Null));
But this is the result I get . . . . .
ID TyNum TNum STNum TName TGender TAge
187771 1 0 0 Alex Male 21
187771 2 0 0 Alex Male 21
187771 3 0 0 Alex Male 21
187772 1 0 0 Beth Female 22
187772 2 0 0 Beth Female 22
187773 1 0 0 Charles Male 20
187773 2 0 0 Charles Male 20
187774 1 0 0 Deena Female 24
187774 2 0 0 Deena Female 24
187774 3 0 0 Deena Female 24
187775 1 0 0 Eric Male 26
Is there a way I can get the intended result by tweaking these queries or by some other way?
Thank you.