Not Exists

[tlindeman]

Could you please help. I am trying to build a statement where the data is not in one table. I keep getting a "data mismatch in query expression". All I am trying to do is get the data from table dbo_tmphistorical where the store number does not exist in dbo_Xpientstores. I pasted the SQL

SELECT dbo_tmpPMixhistorical.OriginalPLU, Sum(dbo_tmpPMixhistorical.AdjItemCount) AS SumOfAdjItemCount, Sum(dbo_tmpPMixhistorical.AdjItemTotal) AS SumOfAdjItemTotal, dbo_tmpPMixhistorical.Description, dbo_tmpPMixhistorical.WeekEndingDate
FROM dbo_tmpPMixhistorical, dbo_XpientStores
WHERE (((Exists (Select * from dbo_XpientStores where dbo_tmpPMixhistorical.storenumber = dbo_XpientStores.storenum))=False) AND ((dbo_tmpPMixhistorical.PLUMst)=1) AND ((dbo_tmpPMixhistorical.WeekEndingDate)=#6/17/2012#) AND ((dbo_tmpPMixhistorical.country)="USA"))
GROUP BY dbo_tmpPMixhistorical.OriginalPLU, dbo_tmpPMixhistorical.Description, dbo_tmpPMixhistorical.WeekEndingDate
ORDER BY dbo_tmpPMixhistorical.OriginalPLU;

 

[John Big Booty]

The error you are getting would tend to indicate that you are trying to compare a Text field with a Numeric field. Check that the fields in the two tables both have the same Data Type.

You might also find this link of interest.