Posted by maxzsim via SQLMonster.com on 11/23/05 10:10

Hi ,
here's the query :

"INSERT INTO tb_temp ( Stock_Code, Supp_Code, Customer_code, [Group],
Obsolete ) " _
& "SELECT tb_StockCons.Stock_Code, tb_StockInfo.Supp_Code,
tb_StockCons.Customer_code, tb_StockCons.Group, tb_StockCons.Obsolete " _
& "FROM tb_StockInfo INNER JOIN tb_StockCons ON tb_StockInfo.
Stock_Code = tb_StockCons.Stock_Code AND tb_StockInfo.Cty = tb_StockCons.Cty"
_
& "WHERE (((tb_StockCons.Customer_code)=IIf([Forms]!
[frm_Consignment_Rep_Cust]![txtCustomer] Is Null,[tb_StockCons]!
[Customer_code],[Forms]![frm_Consignment_Rep_Cust]![txtCustomer])) AND
(tb_StockCons.Year between " & CheckDate([Forms]![frm_Report]![FromDate]) & "
AND " & CheckDate([Forms]![frm_Report]![ToDate]) & ") AND((tb_StockCons.
Obsolete)=IIf([Forms]![frm_Consignment_Rep_Cust]![txtObsolete] Is Null,
[tb_StockCons]![Obsolete],[Forms]![frm_Consignment_Rep_Cust]![txtObsolete]))
AND ((tb_StockInfo.Sub_Service_Category)='Product') AND ((tb_StockInfo.
Category)=IIf([Forms]![frm_Consignment_Rep_Cust]![txtCategory] Is Null,
[tb_StockInfo]![Category],[Forms]![frm_Consignment_Rep_Cust]![txtCategory])))
AND ((((tb_StockCons.Quantity" & GetPeriodRep() - 1 & ")<>0) OR (
(tb_StockCons.Amount" & GetPeriodRep() - 1 & ")<>0)));"

where GetPeriodMod & CheckDate are functions in MS Access

appreciate ur advice

tks & rdgs


ZeldorBlat wrote:
>>How can i improve the performance of the query besides converting to SQL
>>Views or SP(this is going to be diffcult as i am getting some filter
>>parameters from the forms in MS Access) ?
>
>You can post your query so we know what you are trying to do.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200511/1

[Back to original message]