Compare same table for records with different values

[MarrowGenx]

I was seeing if there was an easy way to write a single query to compare records within a table to find ones with different values. As an example, I have a table with two fields (it has more, but this will simplify things): Project Name and Spending Type. Neither is a primary key. To simplify things, I have only two values for Spending Type: Forecast and Previous Month Forecast. I would like to write a simple query that will look at all the Project Numbers with a Spending Type of Forecast and output only those Project Numbers that do not have a corresponding record with the same Project Number and Spending Type of Forecast Previous Month.

I thought something like this would work, but it does not:


SELECT tblSpending.Project_Number, tblSpending_1.Project_Number, tblSpending.Spending_Type, tblSpending_1.Spending_Type
FROM tblSpending LEFT JOIN tblSpending AS tblSpending_1 ON tblSpending.Project_Number = tblSpending_1.Project_Number
WHERE (((tblSpending_1.Project_Number) Is Null) AND ((tblSpending.Spending_Type)="forecast") AND ((tblSpending_1.Spending_Type)="forecast - previous month"));

Any ideas on if this query can be written? I can always break this into 2-3 queries and get my answer, but I was trying to simplify and minimize the number of queries that I have.

Thanks in advance for any suggestions that you may have.

Marrow

 

[MarrowGenx]

I just read about subqueries and I think I am closer to my solution. I need to be able to combine these three queries into one query (if it is possible):

Query #1 not used.

Query #2
SELECT tblSpending.Project_Number
FROM tblSpending
WHERE (((tblSpending.Spending_Type)="forecast - previous month"));

Query #3
SELECT tblSpending.Project_Number
FROM tblSpending
WHERE (((tblSpending.Spending_Type)="forecast - previous month"));

Query #4
SELECT Query2.Project_Number
FROM Query2 LEFT JOIN Query3 ON Query2.Project_Number = Query3.Project_Number
GROUP BY Query2.Project_Number, Query3.Project_Number
HAVING (((Query3.Project_Number) Is Null));



Marrow

 

[PHV]

SELECT F.Project_Number
FROM tblSpending F LEFT JOIN (
SELECT Project_Number FROM tblSpending WHERE Spending_Type='forecast - previous month'
) P ON F.Project_Number=P.Project_Number
WHERE F.Spending_Type='forecast' AND P.Project_Number IS NULL


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MarrowGenx]

Works perfectly. I added a "Group By" piece at the end and got to my answer. Thank you for your quick response, PHV.

Marrow