How do I write a query which checks if a value from 1 columns is in another column

kknb4591

New member
Local time
Yesterday, 18:52
Joined
Aug 25, 2011
Messages
6
Hi,

I am trying to create a query where I check whether a value from one column is in another and if so bring back no result. E.g:

ID Name1 Name2
1 Bob Bob
1 Bob Bill
1 Bob Dave
1 Bob Keith
2 Dave Bob
2 Dave Sam
2 Dave Bill
2 Dave Keith

For my rule ID1 is fine as Bob is in Name1 and Name2 (therefore this should pass), however ID2 would need to be highlighted as Dave is in Name1 but not in Name2 for that ID.

Any suggestions, should I even be doing this in Access?

Many thanks
 
At the individual row level you could use this:

Code:
SameName: Iif([Name1] = [Name2],1,0)

Should return 1 if the fields match or 0 if they don't.

However, it sounds like you want to check it grouped by the ID field rather than for each row so I'm not sure.
 
CB is on the right path, you just need to aggregate that data using a GROUP BY Query:

PHP:
SELECT YourTableName.ID, YourTableName.Name1, Sum(IIf([Name1]=[Name2],1,0)) AS [Match]
FROM YourTableName
GROUP BY YourTableName.ID, YourTableName.Name1
HAVING (((Sum(IIf([Name1]=[Name2],1,0)))=0));

Essentially, you use his method and sum up all those 1's--after summing, if any ID has a value of 0, then that's an ID you want to display. Be sure to change YourTableName with the name of your table.
 

Users who are viewing this thread

Back
Top Bottom