×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Filter query results

Filter query results

Filter query results

(OP)

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.

RE: Filter query results

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

Use Preview before submitting your post/replies

(You can Edit your own post)


---- Andy

There is a great need for a sarcasm font.

RE: Filter query results

CODE -->

qryFirstRecord

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


CODE -->

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 

CODE -->

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 

RE: Filter query results

(OP)

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.

RE: Filter query results

Quote (misuser2k7)

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

RE: Filter query results

Quote:

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

CODE -->

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

CODE -->

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

CODE -->

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
 

RE: Filter query results

(OP)

MajP,

Sorry for responding late.

Your second solution works great.

Thank you!

RE: Filter query results

(OP)

Duane,

I will try using TGML tags in my next post.

Thank you for your help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close