Filter query results 1

[misuser2k7]

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.

 

[Andrzejek]

Please, format your post so it could be 'readable' - use available TGML tags, like [ignore][PRE] ... [/PRE]
[/ignore]
[pre]
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 [/pre]

Use Preview before submitting your post/replies

(You can Edit your own post)


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

qryFirstRecord

SELECT 
  ID, 
  Min(TyNum) AS MinTY, 
  Min(TNum) AS MinT, 
  Min(STNum) AS MinSTN
FROM 
  tblMisuser
GROUP BY 
  ID
HAVING 
  Count(ID)>1

SELECT 
 tblMisuser.ID, 
 tblMisuser.TyNum, 
 tblMisuser.TNum, 
 tblMisuser.STNum, 
 tblMisuser.TName, 
 tblMisuser.TGender, 
 tblMisuser.TAge, 
FROM 
 tblMisuser LEFT JOIN qryFirstRecord ON (tblMisuser.STNum = qryFirstRecord.MinSTN) 
 AND (tblMisuser.TNum =  qryFirstRecord.MinT) AND 
 (tblMisuser.TyNum = qryFirstRecord.MinTY) AND (tblMisuser.ID = qryFirstRecord.ID)
WHERE 
 qryFirstRecord.ID) Is Null

[tt]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	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	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	1	1	Charles	Male	20
187773	2	0	0	Charles	Male	20
187774	1	1	1	Deena	Female	24
187774	2	0	0	Deena	Female	24[/tt]

 

[misuser2k7]

Andy, I am sorry for not formatting the table properly. I'll be honest, I don't know how to use TGML tags.
MajP, thank you for your response.

The problem with this request is that I am doing this for a co-worker who isn’t very clear about what he is looking for. It took some time to understand what he really wants to do.

After a lot of back and forth with the questions and answers, I found out that what he wants the query/queries to do is hide the row with TNum = 0 and STNum = 0 if the count of TyNum exceeds 1 for that particular ID and TyNum group. Conversely, he wants the rows with TNum = 0 and STNum = 0 to be displayed only if the count of TyNum = 1 for that particular ID and TyNum group.

I tried out MajP’s queries in my database. They worked great except for the fact that they include these 3 rows . . . . .
ID TyNum TNum STNum TName TGender TAge
187771 2 0 0 Alex Male 21
187772 2 0 0 Beth Female 22
187774 2 0 0 Deena Female 24

Which should be hidden because the Count of TyNum for these records exceeds 1 for their respective IDs and TyNum group.

But I think I may have found the solution.

I started with a count query which isolates the records which have a count of TyNum = 1 . . . . .
TyNumOnes_Query:
SELECT Table1.ID, Table1.TyNum, Count(Table1.TyNum) AS CountOfTyNum
FROM Table1
GROUP BY Table1.ID, Table1.TyNum
HAVING (((Count(Table1.TyNum))=1));

Used the TyNumOnes_Query in another query to get a full view of all columns . . . . .

SELECT Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge
FROM Table1 INNER JOIN TyNumOnes_Query ON (Table1.TyNum = TyNumOnes_Query.TyNum) AND (Table1.ID = TyNumOnes_Query.ID);

Then created the Union query that generates the required output . . . . .
SELECT Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge
FROM Table1
WHERE (((Table1.TNum)>0) AND ((Table1.STNum)>0));
UNION ALL SELECT Table1.ID, Table1.TyNum, Table1.TNum, Table1.STNum, Table1.TName, Table1.TGender, Table1.TAge
FROM Table1 INNER JOIN TyNumOnes_Query ON (Table1.TyNum = TyNumOnes_Query.TyNum) AND (Table1.ID = TyNumOnes_Query.ID);

Please try it out and let me know if it works or if there is a better/shorter way of doing this.

Thank you all for your help.

 

[dhookom]

I'll be honest, I don't know how to use TGML tags.

TGML is nearly the same as formatting text in any other Window's application. Select the text you want to format and click the appropriate tag from immediately above your text entry box. Consider using the Pre tag and then always Preview your post.

Every major contributor to this forum takes the time to use TGML and then preview before posting. You owe us the courtesy to do the same. Please ask if you have any questions about which TGML tags are appropriate. The Pre and Code tags are the most common.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

I tried out MajP’s queries in my database. They worked great except for the fact that they include these 3 rows . . . . .
ID TyNum TNum STNum TName TGender TAge
187771 2 0 0 Alex Male 21
187772 2 0 0 Beth Female 22
187774 2 0 0 Deena Female 24

Sorry, I misinterpreted. I assumed you wanted to remove the first record of that ID not of that ID and that TyNum. You have to group by ID and TyNum

qryFirstRecord (records to exclude)

SELECT 
  ID, 
  TyNum, 
  Min(TNum) AS MinT, 
  Min(STNum) AS MinSTN
FROM 
  tblMisuser
GROUP BY 
  ID,
  TyNum,
HAVING 
  Count(ID)>1

The rest of the solution should hold. This assumes the first record with an ID, TyNum combination is always a 0,0 record.
If that assumption is not correct and specifically looking for 0,0 even if not the first in an ID, Tynum combination then you can do

qryFirstRecord
SELECT 
 ID, 
 TyNum, 
 First(tblMisuser.TNum) AS FirstTNum,
 First(tblMisuser.STNum) AS FirstSTNum
FROM tblMisuser
GROUP BY 
 ID, 
 TyNum
HAVING First(tblMisuser.TNum)=0 AND First(tblMisuser.STNum=0 AND Count(tblMisuser.ID)>1

Returns

[tt]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
[/tt]

 

[misuser2k7]

MajP,

Sorry for responding late.

Your second solution works great.

Thank you!

 

[misuser2k7]

Duane,

I will try using TGML tags in my next post.

Thank you for your help.