How do I collect data based on the same data in one field but differ data in another

wheddingsjr

Registered User.
Local time
Today, 18:45
Joined
Jan 20, 2017
Messages
11
Hi all

I created a query in access that retrieving data that has the same data in one field but I also want to eliminate data where the data is the same in another field. I have added two attachments and I will explain them both.

Excel Example: This is the data in the table that I am using. As you can see I wanted all data where there are more than 1 MEMNO which I need. What I doint need are those lines where the COMBO1 fields are also the same. I want to keep those that have the same MEMNO but different COMBO1 numbers.

Access Example: In order to get this data from access. I used the WHERE statement in the attached script (minus the yellow highlited portion). I attempted to copy the original where statement using AND and replacing MEMNO with COMBO1 and replaced the HAVING portion of the WHERE statement with NOT HAVING and it did work. Any suggestions would be greatly apprecioated.

I hope all this makes sense..
 

Attachments

in the query, join T1 to T2 on the field you DO want the same T1.F1 and T2.F1
then for the field you do NOT, bring down that field, F2
then set the criteria: not =

field: T1.F2
criteria: <> T2.F2
 
Thanks Ranman

But I am a bit confused. I only have one table.
 
ohhh I see where you might have misunderstood. The "RESULT" column in my example doesnt actually exist. I added that so that everyone could see what my desired outcome is.
 
wait..Is the other table that you are referring to the original RESULTS table?
 
Hi Ranman

it took a minute for me to understand what you meant but I did it and it worked perfectly. Thank you VERY much
 

Users who are viewing this thread

Back
Top Bottom