SQL <> statement

[goterps1]

Now I'm trying to pull all records from Fund 49 that do not match with Fund 83 based on SSN. Below is the SQL that I'am trying in I'm not retrieving any records. I know I should be retrieving records.


SELECT DISTINCT [Fund 49].SSN, [Fund 49].FUND, [Fund 49].ACCOUNT, [Fund 49].OPENCLOSE
FROM [Fund 49] INNER JOIN [Fund 83] ON [Fund 49].SSN = [Fund 83].SSN
WHERE ((([Fund 49].SSN)<>([Fund 83].[SSN])));

 

[PWise]

what you are saying is show me the records from fund49

where fund49.ssn=fund43.ssn and fund49.ssn<>fund43.ssn

now if fund49.ssn=fund43.ssn how can fund49.ssn<>fund43.ssn?

that is why it is not retuning records

 

[PWise]

sorry thatshould be

fund83 not fund43

 

[goterps1]

I see where the confusion is with the FROM clause. I thought I had to join the two tables based on the SSN number.

My select statement did work when I'm trying to find matching SSN ex. [Fund 49].SSN)=([Fund 83].[SSN]. So what I'm attempting to do now is find those SSN in Fund 49 that do not match any SSN in Fund 83.

 

[JoeAtWork]

If I understand you correctly, I think you want:

SELECT DISTINCT [Fund 49].SSN, [Fund 49].FUND, [Fund 49].ACCOUNT, [Fund 49].OPENCLOSE
FROM [Fund 49]
WHERE [Fund 49].SSN NOT IN (SELECT [Fund 83].SSN FROM [Fund 83])

 

[PHV]

SELECT DISTINCT [Fund 49].SSN, [Fund 49].FUND, [Fund 49].ACCOUNT, [Fund 49].OPENCLOSE
FROM [Fund 49] LEFT JOIN [Fund 83] ON [Fund 49].SSN = [Fund 83].SSN
WHERE [Fund 83].SSN Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[randy700]

Why go through all this trouble? Access provides you with a Find Unmatched Query Wizard to provide exactly what you are looking for.


Randy