Filter out a group, if one particular field is identical

[rderkis]

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.

 

[rtmpxr]

Add to the WHERE clause the following:

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

rtmpxr

 

[rderkis]

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.

 

[rtmpxr]

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

 

[rderkis]

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

 

[rtmpxr]

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;

 

[rtmpxr]

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

 

[rderkis]

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.

 

[rderkis]

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.

 

[rderkis]

Compact and repair changed nothing.

 

[rtmpxr]

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.

 

[rderkis]

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?

 

[rderkis]

Here is the brief cut down database. The password is 777

http://lft.buckeye-express.com/download.cfm/968

If you don't get the same mixed results, I will commit myself

 

[rderkis]

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.

 

[rtmpxr]

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;

 

[rderkis]

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

 

[rtmpxr]

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;

 

[rderkis]

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?