I'm trying to execute this query to return a value from another table(GradeAQualityAdjustment).
SELECT CheckStubInfo.PatronNo, CheckStubInfo.[BF%], CheckStubInfo.SCC, CheckStubInfo.Bacteria, CheckStubInfo.[Protein%], Round(IIf((350-([CheckStubInfo].[SCC]/1000))>0,(350-([CheckStubInfo].[SCC]/1000))*[Pricing Info].[SCC Premium Price]),2) AS SCCAvg, DLookUp("QualityAdjustment","GradeAQualityAdjustment",[CheckStubInfo].[SCC] Between DLookUp("BegQuality","GradeAQualityAdjustment") And DLookUp("EndQuality","GradeAQualityAdjustment")) AS QualityAdj
FROM [Pricing Info] INNER JOIN CheckStubInfo ON [Pricing Info].Date = CheckStubInfo.Date
WHERE ((([Pricing Info].Date)=[CheckStubInfo].[Date]) AND ((CheckStubInfo.Grade)="A"));
My problem is, that as soon as my query finds the first QualityAdj value, it only returns that value for the records that match. It doesn't look at any other records.
SELECT CheckStubInfo.PatronNo, CheckStubInfo.[BF%], CheckStubInfo.SCC, CheckStubInfo.Bacteria, CheckStubInfo.[Protein%], Round(IIf((350-([CheckStubInfo].[SCC]/1000))>0,(350-([CheckStubInfo].[SCC]/1000))*[Pricing Info].[SCC Premium Price]),2) AS SCCAvg, DLookUp("QualityAdjustment","GradeAQualityAdjustment",[CheckStubInfo].[SCC] Between DLookUp("BegQuality","GradeAQualityAdjustment") And DLookUp("EndQuality","GradeAQualityAdjustment")) AS QualityAdj
FROM [Pricing Info] INNER JOIN CheckStubInfo ON [Pricing Info].Date = CheckStubInfo.Date
WHERE ((([Pricing Info].Date)=[CheckStubInfo].[Date]) AND ((CheckStubInfo.Grade)="A"));
My problem is, that as soon as my query finds the first QualityAdj value, it only returns that value for the records that match. It doesn't look at any other records.