Performence Issue for MS Access Query

[gys67]

I have following query (Table and Column names are changed) .
It takes hours to run. Please help to Improve performence.

SELECT [k].[ID],
[k].[PROGRAM],
[k].[STATE],
[k].[QUARTER],
[k].[NDC],
[k].[NDC_DC],
[k].[Tot_Dis_Reb],
[k].Tot_Dis_U,
[k].Tot_Disp_Re,
[k].Tot_Disp_U,
[k].Tot_A_Reb,
[k].Tot_A_U,
[k].Tot_R_A_Amt,
[k].Tot_R_A_U,
[k].Ex_A,
[k].It_At
FROM K
WHERE k.PROGRAM & LEFT(k.QUARTER, 4) & RIGHT(k.QUARTER, 1) & k.STATE & LEFT(K.NDC, 5) & '-' & RIGHT(LEFT(K.NDC, 9),4) & '-' & RIGHT(K.NDC, 2)
NOT IN (SELECT DISTINCT C_S.PROGRAM & RIGHT(C_S.QUARTER,4) & LEFT(C_S.QUARTER, 1) & C_S.STATE
& C_S.NDC FROM C_S);

Thanks
gys67

 

[Blorf]

Try this. Hopefully I typed everything correct. Instead of using a not in (Sql) I used a join, and a where is null. Might be faster.

ChaZ

SELECT K.ID, K.Program, K.State, K.Quarter, K.NDC, K.NDC_DC, K.Tot_Dis_Reb, K.Tot_Dis_U, K.ToT_Disp_Re, K.Tot_Disp_U, K.Tot_A_Reb, K.Tot_A_U, K.Tot_R_A_Amt, K.Tot_R_A_U, K.Ex_A, K.It_AT, [C_S].[PROGRAM] & Right([C_S].[QUARTER],4) & Left([C_S].[QUARTER],1) & [C_S].[STATE] & [C_S].[NDC] as Filter
FROM K LEFT JOIN C_S ON ([k].[PROGRAM] & Left([k].[QUARTER],4) & Right([k].[QUARTER],1) & [k].[STATE] & Left([K].[NDC],5) & '-' & Right(Left([K].[NDC],9),4) & '-' & Right([K].[NDC],2)) = ([C_S].[PROGRAM] & Right([C_S].[QUARTER],4) & Left([C_S].[QUARTER],1) & [C_S].[STATE] & [C_S].[NDC])
where C_S.program is null;



Ascii dumb question, get a dumb Ansi

 

[gys67]

Hi Blorf :

I really thank you for your support.

I have typed the query as follows

SELECT K.ID,
K.Program,
K.State,
K.Quarter,
K.NDC,
K.NDC_DC,
K.Tot_Dis_Reb,
K.Tot_Dis_U,
K.ToT_Disp_Re,
K.Tot_Disp_U,
K.Tot_A_Reb,
K.Tot_A_U,
K.Tot_R_A_Amt,
K.Tot_R_A_U,
K.Ex_A,
K.It_AT,
[C_S].[PROGRAM] & Right([C_S].[QUARTER],4)
& Left([C_S].[QUARTER],1)
& [C_S].[STATE] & [C_S].[NDC]
as Filter
FROM K LEFT JOIN C_S ON
([k].[PROGRAM] & Left([k].[QUARTER],4) & Right([k].[QUARTER],1)
& [k].[STATE] & Left([K].[NDC],5) & '-' &
Right(Left([K].[NDC],9),4) & '-' &
Right([K].[NDC],2)) =
([C_S].[PROGRAM]
& Right([C_S].[QUARTER],4)
& Left([C_S].[QUARTER],1)
& [C_S].[STATE] & [C_S].[NDC])
where C_S.program is null;


However it gives syntecx error on

FROM K LEFT JOIN C_S ON
([k].[PROGRAM] & Left([k].[QUARTER],4) & Right([k].[QUARTER],1)
& [k].[STATE] & Left([K].[NDC],5) & '-' &
Right(Left([K].[NDC],9),4) & '-' &
Right([K].[NDC],2)) =
([C_S].[PROGRAM]
& Right([C_S].[QUARTER],4)
& Left([C_S].[QUARTER],1)
& [C_S].[STATE] & [C_S].[NDC])
where C_S.program is null;
statement. I cannot figure out as I am not an access programmer. I would really appriciate if anyone can help.

Thanks

 

[grnzbra]

Might it be looking for "LEFT OUTER JOIN"?