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.
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
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
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
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
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
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
RE: Filter out a group, if one particular field is identical
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
RE: Filter out a group, if one particular field is identical
RE: Filter out a group, if one particular field is identical
... 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
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
http://lft.buckeye-express.com/download.cfm/968
If you don't get the same mixed results, I will commit myself
RE: Filter out a group, if one particular field is identical
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
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
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
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