×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-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

*Eng-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 out a group, if one particular field is identical

Filter out a group, if one particular field is identical

Filter out a group, if one particular field is identical

(OP)
How do I filter out a group, if one particular field is identical, in all that of group? (Using Access 2002)

My query runs, but when all the group's PasswordNum is empty I DON'T want it to show.

Quary runs and looks like this.
PasswordNum is a number field.

SELECT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName,Members.PasswordUsed
FROM Members
WHERE (((Members.JobDispatchDate) In (SELECT [JobDispatchDate] FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1  And [SSN] = [Members].[SSN] )))
ORDER BY Members.JobDispatchDate, Members.SSN;

Output Looks Like This

JobDispatchDate    SSN  FirstName        LastName      PasswordUsed
4_10              234  ANTHONY          ESPESITO
4_10              234  ANTHONY          ESPESITO
4_10              456  ANTHONY          SMALLWOOD      1234
4_10              456  ANTHONY          SMALLWOOD      1234


I don't want ANTHONY ESPESITO group to show because his Password is empty.
I have spent weeks on this problem. Sent and received countless emails. Still no one can solve it, and many have tried!

THANK YOU! For your time.

RE: Filter out a group, if one particular field is identical

Add to the WHERE clause the following:

WHERE (((Members.JobDispatchDate) In (SELECT [JobDispatchDate] FROM [Members] As Tmp AND Members.PasswordUsed IS NOT NULL

rtmpxr

RE: Filter out a group, if one particular field is identical

(OP)
  I really appreate you attempt and time!
I have struggled with this so long. I wish it was that easy!
 I have not tried your example yet. But I don't see how it would work on a group.
 I think it would just filer out all the records with no password, including Bill Hay's 1 record.

 My example was not good enough, so I will give another.

JobDate    SSN  First    Last      Password
4_10       234   Tom      Espe
4_10       234   Tom      Espe
4_10       456   Tim       Small       123
4_10       456   Tim       Small       123
4_10       345    Bill        Hay         123        
4_10       345    Bill        Hay  
4_10       345    Bill        Hay         123

 I want just Tom Espe's records filterd out, but not  Bill Hay or Tim Small, because ALL of Tom Espe's records contain a blank password..
 You will notice Bill HAY has no password in one of his records. But I want all three of Bill Hay's records to show, including the one with no password. Please also notice the grouping.           

RE: Filter out a group, if one particular field is identical

Ok, I understand. Add the following to the WHERE clause:

and 1<=(select count(*) from members n where members.ssn=n.ssn and n.pass is not null)

I have tested on Firebird(not Access) and it works. Anyway, I think you catch the idea.

Hope that help
rtmpxr

RE: Filter out a group, if one particular field is identical

(OP)
Thank you once again! But I think you are way beyond me!
I boiled what I have down, to make it simpler. This works but includes the unwanted groups.

SELECT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.passwordnum
FROM Members
WHERE Members.JobDispatchDate
 In( SELECT [JobDispatchDate]  
FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1  
And [SSN] = [Members].[SSN])
ORDER BY Members.JobDispatchDate, Members.SSN;

But no mater which way I try to add your patch it won't compile.

But wait a min.I am making progress thanks to you!

no luck I got this to compile

SELECT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.passwordnum
FROM Members
WHERE Members.JobDispatchDate
In( SELECT [JobDispatchDate]
FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1
 And [SSN] = [Members].[SSN]
and 1<=(select count(*) from members
where members.ssn=ssn and passwordnum is not null ))
ORDER BY Members.JobDispatchDate, Members.SSN;

 But the view doesn't change?
Passwordnum is a string so I tried passwordnum > "" and other operators in place of your (passwordnum is not null).
To no availe

RE: Filter out a group, if one particular field is identical

This is the correct form:

SELECT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.passwordnum
FROM Members
WHERE (Members.JobDispatchDate
In( SELECT [JobDispatchDate]
FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1
 And [SSN] = [Members].[SSN]))
and (1<=(SELECT Count(*) from [Members] As Members1
WHERE Members.ssn=Members1.ssn and Members1.passwordnum is not null ))
ORDER BY Members.JobDispatchDate, Members.SSN;

RE: Filter out a group, if one particular field is identical

If still doesn't work, post a part of the original data and the fields name and I'll do the SQL.

RE: Filter out a group, if one particular field is identical

(OP)
You are so cool! Your sql mostly works, it took the view from 238 records down to 99. There are still a few groups where the entire group has the same ssn numbers and blank passwords. Which of course makes no sense to me! Why would it catch most but not all? Perhaps those passwords fields contain a " " instead of null?
 How did you learn so much about sql? I have books that I read on sql, plus I search the net. But I find nothing as complex as you are doing
 I am grateful!

 When you used

“and (1<=(SELECT Count(*) from [Members] As Members1”

 Did that make member1 a temp table? And does that mean I can use a sub query on members1?

 When you used

WHERE Members.ssn=Members1.ssn and Members1.passwordnum is not null  ))

to access the members1 (table or record set), does

 “Members1.passwordnum is not null”

act on the each group individually, to see if the entire group have the same (non)password?

There are other record groups that I don't want to show also. For example when all the [phone] fields, in a group are the same.

 I did not include the [Phone] problem in the first question because I felt that if I could learn how to solve the blank password, I would be able to use the knowledge to solve that the phone problem.

I appreciate your offer, to just do the sql for me, more than you know. But like, I suspect  you are,  that is not my style ? You did not become truly outstanding at sql by letting other people solve your problems for you.

RE: Filter out a group, if one particular field is identical

(OP)
Strange, I changed the "members1.passwordnum is not null" to "members1.passwordnum is null" and got mostly just groups with blank passwordnum fields in them, but there were few groups, where the entire group had a valid passwordnum in them!? I am going to compact and repair the database now. I doubdt it but perhaps that is the problem.

RE: Filter out a group, if one particular field is identical

(OP)
Compact and repair changed nothing.

RE: Filter out a group, if one particular field is identical

There is a BIG difference between NULL and "" (or " ") in a field. NULL means "nothing was posted here ever" where "" means "something was posted here but then erased".  You can find non NULL values using
... where passwordnum is not null
If you view records with blanc in passwordnum then is likely to have there "" or blanc(s).
Members1 is an alias used to differentiate from Members table.
I used each Members.ssn, readed in principal query, to be passed to Members1 table using Members.ssn=Members1.ssn.
For example: if in the principal query I read ssn=234 then pass this information to members1 table (to select JUST THIS members) and from these all with paawordnum not null, and count how many are they.
-if count is 0 then all paswordnum are NULL
-if count is 1 then 1 passswordnum is NOT NULL
-and so on

Regarding the "strange situation", well, is not strange at all. How many NULL paswordnum has TIM ESPE? He has 2 so all TIM ESPE records should appear. How many NULL paswordnum has BILL HAY? He has 1 so ALL these records shold appear (remember the condition 1<=(select count(*)...). How many NULL paswordnum has TIM SMALL? ZERO, so nothing will appear.

RE: Filter out a group, if one particular field is identical

(OP)
This is one of the abnormalities I get with your sql.
Like I said 99 records down from 232 records show, so its much closer. But why does GREGORY SEIBERT show? I then tried this sql

WHERE  Members.ssn = Members1.ssn and Members1.passwordnum > "9"  ))

With this sql, only members groups whith password starting with 9s or mixed 9s and blanks show, which is as it should be. But stuck in there is a members group whose whole group contains a blank password!?

JobDate    SSN         FirstName         LastName           Password Used
4_10    275747793    ANTHONY    SMALLWOOD                    9190511
4_10    275747793    ANTHONY    SMALLWOOD                    9190511
4_10    296649845    GREGORY    SEIBERT                    
4_10    296649845    GREGORY    SEIBERT                    
4_10    416487051     DANIEL       WEEKS                           916087051
4_10    416487051     DANIEL       WEEKS    

OK i Give up! What I am saying sounds crazy. How do I send you a small test database?                   

RE: Filter out a group, if one particular field is identical

(OP)
I guess I should explain what I am trying to do.

We have a job call recording that plays, when a person calls from home (CallerIDPhone number check), or enters their unique password. All this is looked up from a different database.

 All calls are logged to a database. With the JobDispachDate, date, time etc, their ssn callerid etc

 I want to sort thru this large database and view just the a suspicious calls. Theoretically they only need to call once per JobCallDate, but sometimes we call two or three times. I am looking to see if someone else is using their password.

First I filter out people who called only once per jobdispatchdate
Next I group by jobdispatchdate, ssn .
 If the entire groups passwordnum is blank that means they called from home, nothing a suspicious there. If the entire groups Phone (from caller id) is the same, nothing a suspicious there either.
 If ether of the two conditions are not meet I call it a suspicious call.

RE: Filter out a group, if one particular field is identical

On Gregory Saibert problem: when I counted for passwordnum I have do it through ALL database which was a mistake. Gregory Saibert has a not null paswordnum in JobDispatchDate=4_17 therefore it is showed every time. Correctly, the count of passwordnum should be done per JobDispatchDate group! So test this SQL:

SELECT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.Phone, Members.PasswordNum
FROM Members
WHERE (((Members.JobDispatchDate) In (SELECT [JobDispatchDate]
FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1
 And [SSN] = [Members].[SSN])) AND ((1)<=(SELECT Count(*) from [Members] As Members1
WHERE  Members.ssn = Members1.ssn and Members.JobDispatchDate=Members1.JobDispatchDate and Members1.passwordnum is not null )))
ORDER BY Members.JobDispatchDate, Members.SSN;
 

RE: Filter out a group, if one particular field is identical

(OP)
Works like a charm! That filters it down to 73 records
I just can't belive you did it! The problem now is that the sql is so complex, how can I figure it out?
 Like you said Gregory Saibert password was not empty, so how does this sql account for it?

 I was hoping to filter out all the groups where the (C.I.D.)phone fields are the same ,unless of course, the  (C.I.D.)phone fields are all empty. Now I don't know where to begin. (C.I.D. = caller id phone#)

 Thank You
  Thank You
   Thank You

RE: Filter out a group, if one particular field is identical

You can break up the SQL into pieces and run them as individual querys to see what's happening. For example in "SELECT COUNT(*)...." replace Members.SSN with a known one, same for Members.JobDipatchDate and run it.
For Gregory Saibart run this query:

SELECT Members.JobDispatchDate, firstname, lastname,passwordnum, Count(*) AS Calls
FROM Members where lastname='SAIBART'
GROUP BY Members.JobDispatchDate,  lastname,firstname,passwordnum;

You'll see that he has 2 NULL passwords in JobDispatchDate=4_10 and 4_7 so doesn't met the requirements therefore they don't showed up. For the rest of JobDispatchDate groups he has just ONE call (one is not null) each so again it doesn't met the requirements.
About C.I.D I don't know if it is the same with "phone" field in your database. If so, you have two options.
First, use DISTINCT clause like this:

SELECT DISTINCT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.Phone, Members.PasswordNum
FROM Members
WHERE (((Members.JobDispatchDate) In (SELECT [JobDispatchDate]
FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1
 And [SSN] = [Members].[SSN])) AND ((1)<=(SELECT Count(*) from [Members] As Members1
WHERE  Members.ssn = Members1.ssn and Members.JobDispatchDate=Members1.JobDispatchDate and Members1.passwordnum is not null )))
ORDER BY Members.JobDispatchDate, Members.SSN;

The second option do the same job but it shows how many "Calls" were used for each record :

SELECT Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.Phone, Members.PasswordNum, Count(*) AS Calls
FROM Members
WHERE (((Members.JobDispatchDate) In (SELECT [JobDispatchDate]
FROM [Members] As Tmp
GROUP BY [JobDispatchDate],[SSN] HAVING Count(*)>1
 And [SSN] = [Members].[SSN])) AND ((1)<=(SELECT Count(*) from [Members] As Members1
WHERE  Members.ssn = Members1.ssn and Members.JobDispatchDate=Members1.JobDispatchDate and Members1.passwordnum is not null )))
GROUP BY Members.JobDispatchDate, Members.SSN, Members.FirstName, Members.LastName, Members.Phone, Members.PasswordNum
ORDER BY Members.JobDispatchDate, Members.SSN;

 

RE: Filter out a group, if one particular field is identical

(OP)
I had given considerable thought to the problem of the phone field. I started to work on the distint idea but realized that, if a group had all blank (CID)phone fields, they would be suspious calls. Why does this have to be so hard Is that what seperates us hacks from you proffesionals?

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 Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

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


Resources