Hi,
I'm a regular user of MS Access. However I have a problem which I can't solve in MS Access and have to use SQL and I have limited knowledge of transact-sql.
I do monthly imports in Access of different figures but now I have to make a correction to old values.
I have a main table "prod_AP_Inv_POT" which is actually stored on an SQL server 2009. It's linked into my Access db and thats where I fill it with new data every month.
As I mentioned I need to make an adjustment to one of the fields in my SQL table, about 100k rows or so will be affected.
Savy as I am with MS access I've made an update query (by using design) but it doesn't want to cooperate with me. Basically it doesn't make the change, period. There's nothing wrong with the query it has more to do with the fact that the SQL table i'm doing changes in has 9milion rows.
Anyhow I need to create the exact same operation on the sql server instead but this is where my lack of skills in transact-sql fails me.
Below is my code from MS Access. I was wondering if someone can help me re-write it to a stored procedure in MS SQL?
I know I have to put the "new" code after "Begin" in the SQL procedure, that's kinda it.
Any help is appriciated!
I'm a regular user of MS Access. However I have a problem which I can't solve in MS Access and have to use SQL and I have limited knowledge of transact-sql.
I do monthly imports in Access of different figures but now I have to make a correction to old values.
I have a main table "prod_AP_Inv_POT" which is actually stored on an SQL server 2009. It's linked into my Access db and thats where I fill it with new data every month.
As I mentioned I need to make an adjustment to one of the fields in my SQL table, about 100k rows or so will be affected.
Savy as I am with MS access I've made an update query (by using design) but it doesn't want to cooperate with me. Basically it doesn't make the change, period. There's nothing wrong with the query it has more to do with the fact that the SQL table i'm doing changes in has 9milion rows.
Anyhow I need to create the exact same operation on the sql server instead but this is where my lack of skills in transact-sql fails me.
Below is my code from MS Access. I was wondering if someone can help me re-write it to a stored procedure in MS SQL?
Code:
UPDATE prod_AP_Inv_POT
INNER JOIN conv_ExpectedPaymentDate ON (prod_AP_Inv_POT.VendorCountryType = conv_ExpectedPaymentDate.GeoKey)
AND (prod_AP_Inv_POT.DueDate = conv_ExpectedPaymentDate.DueDate)
SET prod_AP_Inv_POT.ExpectedPaymentDate = [conv_ExpectedPaymentDate].[ExpectedPaymentDate]
WHERE (((prod_AP_Inv_POT.ExpectedPaymentDate) Is Null)
AND ((prod_AP_Inv_POT.Ccode)="US10") AND ((prod_AP_Inv_POT.DT)="A0")
AND ((prod_AP_Inv_POT.ClearingDate) Between #10/1/2011# And #11/30/2011#)
AND ((prod_AP_Inv_POT.Overdue)="Not Paid in Time"));
I know I have to put the "new" code after "Begin" in the SQL procedure, that's kinda it.
Any help is appriciated!