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. I'm pretty sure it has to do with my where statement and my syntax for passing that as a string, but I'm confused where to put my "'".
Thank you!
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. I'm pretty sure it has to do with my where statement and my syntax for passing that as a string, but I'm confused where to put my "'".
Thank you!