[AccessD] Query not returning desired results

[Stuart McLachlan]

I find your long space filled table and query names and copious bracketing together with long 
run on lines makes all of your problem SQL hard to analyse.  Suggest you get in the habit of 
using aliases and only bracket where necessary.

I can analyse this much easier:

SELECT q3.Well_In_CO2PAT, 
t3.Well_in_CO2PAT_API,
t3.ProductionMonth,
t3.Other_Pattern, 
t3.Other_Pattern_as_Well,
t3.Other_Pattern_as_Well_API, 
t3.3mo_rbi, 
q3.SumOf3mo_rbi, 
IIf(SumOf3mo_rbi=0,al.Factor_2,3mo_rbi/SumOf3mo_rbi) AS
Factor_2 INTO [tbl 3 Month Factor 2] 

FROM [qry 3 Months Running Injection Data Totals] q3

INNER JOIN [tbl 3 Month Running Inj Data] t3 
   ON q3.Well_in_CO2PAT_API = t3.Well_in_CO2PAT_API
      AND q3.ProductionMonth = t3.ProductionMonth

INNER JOIN Allocations al 
   ON t3.Well_in_CO2PAT_API = al.ChildPID 
      AND t3.Other_Pattern = al.PID 

WHERE t3.Other_Pattern <> "PAT 28-5A" 
    And t3.Other_Pattern <>"PAT 56-6A"
 	AND t3.Other_Pattern_as_Well_API Is Null 
	AND al.Date=#11/1/1948#

GROUP BY q3.Well_In_CO2PAT, t3.Well_in_CO2PAT_API, t3.ProductionMonth,
 t3.Other_Pattern, t3.Other_Pattern_as_Well, t3.Other_Pattern_as_Well_API, t3.3mo_rbi,
q3.SumOf3mo_rbi, IIf(SumOf3mo_rbi=0,al.Factor_2,3mo_rbi/SumOf3mo_rbi;


That said, I can't see anything wrong with that SQL. Can you tell us what data is being 
included or excluded that should not be?



On 10 Dec 2019 at 14:51, Kaup, Chester wrote:

> Here is the whole query. Thanks for looking at this
> 
> SELECT [qry 3 Months Running Injection Data Totals].Well_In_CO2PAT,
> [tbl 3 Month Running Inj Data].Well_in_CO2PAT_API, [tbl 3 Month
> Running Inj Data].ProductionMonth, [tbl 3 Month Running Inj
> Data].Other_Pattern, [tbl 3 Month Running Inj
> Data].Other_Pattern_as_Well, [tbl 3 Month Running Inj
> Data].Other_Pattern_as_Well_API, [tbl 3 Month Running Inj
> Data].[3mo_rbi], [qry 3 Months Running Injection Data
> Totals].SumOf3mo_rbi,
> IIf([SumOf3mo_rbi]=0,Allocations.Factor_2,[3mo_rbi]/[SumOf3mo_rbi]) AS
> Factor_2 INTO [tbl 3 Month Factor 2] FROM ([qry 3 Months Running
> Injection Data Totals] INNER JOIN [tbl 3 Month Running Inj Data] ON
> ([qry 3 Months Running Injection Data Totals].Well_in_CO2PAT_API =
> [tbl 3 Month Running Inj Data].Well_in_CO2PAT_API) AND ([qry 3 Months
> Running Injection Data Totals].ProductionMonth = [tbl 3 Month Running
> Inj Data].ProductionMonth)) INNER JOIN Allocations ON ([tbl 3 Month
> Running Inj Data].Well_in_CO2PAT_API = Allocations.ChildPID) AND ([tbl
> 3 Month Running Inj Data].Other_Pattern = Allocations.PID) WHERE [tbl
> 3 Month Running Inj Data].Other_Pattern<>"PAT 28-5A" And [tbl 3 Month
> Running Inj Data].Other_Pattern<>"PAT 56-6A" AND [tbl 3 Month Running
> Inj Data].Other_Pattern_as_Well_API Is Null AND
> Allocations.Date=#11/1/1948# GROUP BY [qry 3 Months Running Injection
> Data Totals].Well_In_CO2PAT, [tbl 3 Month Running Inj
> Data].Well_in_CO2PAT_API, [tbl 3 Month Running Inj
> Data].ProductionMonth, [tbl 3 Month Running Inj Data].Other_Pattern,
> [tbl 3 Month Running Inj Data].Other_Pattern_as_Well, [tbl 3 Month
> Running Inj Data].Other_Pattern_as_Well_API, [tbl 3 Month Running Inj
> Data].[3mo_rbi], [qry 3 Months Running Injection Data
> Totals].SumOf3mo_rbi,
> IIf([SumOf3mo_rbi]=0,Allocations.Factor_2,[3mo_rbi]/[SumOf3mo_rbi]);
>