Update Query Taking time

[sarath26uk]

Hi all

I have an order entry screen where customers are identified on basis of their
telephone number.When an order comes in; customer record is pulled out by
using phone number. Within this form there is a subform where their ordered
products are entered such as product no, quantity, price

Now some of the customers get special price on their products for example,
some gets 4.99 while others get 9.99 . I have created a table and query to
store these special prices based on their phone no and product no. so that
special price on a particular product for a one telephone no. for eg

4.99 for item: water for tele: 11122333

5.99 for item: water for tele: 22223333

An update query is also created so that at the order entry screen, when an
order is received you pull up the phone no. then start entering the products,
query will check the product no and see if their is a special price for this
product no. for this customer (tele no). if so update the price to that
special price.

Now when this update query is executed for each item irrespective of having
special price or not, it takes atleast 2 mins to go to the next row to enter
the next item.. this is not a reasonable time so I need some change in
strategy (i guess) , can any one suggest please.

 

[[MVP] S.Clark]

Table Indexes?
Properly joined tables? (i.e. no cross joins)
Joins based on numbers, not text values?
Does the update need to occur to every record, or can you wait until the end
of the entry to do a batch?

Post the SQL if you like.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[sarath26uk via AccessMonster.com]

Thanks for the reply clark,

Im pasting the update query


UPDATE tblAR INNER JOIN ((tblInventory INNER JOIN (tblCustomers INNER JOIN
tblspecialprice ON tblCustomers.Telephone = tblspecialprice.telephone) ON
tblInventory.ProductNo = tblspecialprice.productno) INNER JOIN tblARDetails
ON tblInventory.InventoryID = tblARDetails.dInventoryID) ON (tblCustomers.
CustomersID = tblAR.aCustomersID) AND (tblAR.ArID = tblARDetails.dARID) SET
tblARDetails.ardNetItem = [tblspecialprice].[specialprice]
WHERE (((tblAR.InvNo)=[InvNo]));

each order is recognized by the invoice number.

Well i guess its always best to update the prices as it goes but if thats
nearly impossible then may be before saving that order, may be a button to
update the prices of that order...

Table Indexes?
Properly joined tables? (i.e. no cross joins)
Joins based on numbers, not text values?
Does the update need to occur to every record, or can you wait until the end
of the entry to do a batch?

Post the SQL if you like.

Hi all

[quoted text clipped - 29 lines]

the next item.. this is not a reasonable time so I need some change in
strategy (i guess) , can any one suggest please.

 

[sarath26uk via AccessMonster.com]

I found a fix for this issue, basically instead of updating the prices as it
goes, i decided to place a button and update it towards the end of that order,
so i made a criteria in the update qry based on the telephone # field in the
form. Now it takes less than 2 sec to run the qry thats a huge transition.

Thanks for all the help

Thanks for the reply clark,

Im pasting the update query

UPDATE tblAR INNER JOIN ((tblInventory INNER JOIN (tblCustomers INNER JOIN
tblspecialprice ON tblCustomers.Telephone = tblspecialprice.telephone) ON
tblInventory.ProductNo = tblspecialprice.productno) INNER JOIN tblARDetails
ON tblInventory.InventoryID = tblARDetails.dInventoryID) ON (tblCustomers.
CustomersID = tblAR.aCustomersID) AND (tblAR.ArID = tblARDetails.dARID) SET
tblARDetails.ardNetItem = [tblspecialprice].[specialprice]
WHERE (((tblAR.InvNo)=[InvNo]));

each order is recognized by the invoice number.

Well i guess its always best to update the prices as it goes but if thats
nearly impossible then may be before saving that order, may be a button to
update the prices of that order...

Table Indexes?
Properly joined tables? (i.e. no cross joins)

[quoted text clipped - 9 lines]