August 24, 2009 at 1:09 pm
Good day all,
I have an update qry that I would like to pause before moving onto the next record.
Not sure how I would go about coding this and a search has come up blank. I imagine i'd have to use some form of 'do while loop'.
Anyone know the best way of coding this please?
Thanks,
Mitch.....
August 24, 2009 at 1:13 pm
Why?
August 24, 2009 at 2:56 pm
Hi Lynn,
well i'm rewriting a qry from Access to SQL.
The qry matches two fields to another instance of the same table and runs a formula upating one of the fields. The formula relies on the record above it in order to update the enxt record. In Access this runs fine but in SQL it seems to run away with itself so i'm trying to fix it so that it works one record at a time before moving onto the next.
August 24, 2009 at 3:02 pm
Read the first article in my signature block below regarding asking for assistance. Follow the instructions in that article and post the DDL for your table(s), sample data, and expected results. If you follow the instructions in that article, you will get quick responses and tested coded. Looks like a simple solution based on the description, but I could do much more with the info I requested.
August 24, 2009 at 3:03 pm
Take a look at this article. It should help you write this with on "single row updates" going on.
http://qa.sqlservercentral.com/Forums/FindPost449802.aspx
Let's just say - time to kick the training wheels off and put that DB in high gear! Done right, this kind of solution will blow the doors off of the more traditional options.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 3:57 pm
Hi Lynn,
the code I am using in SQL is below:
UPDATE DBO.tblTest
SET PV_OLD = tblTest_1.PV_NEW * POWER((1 +(tblTest.OLDRTE/1200)), tblTest.OLD_NUM),
PV_NEW = Test_1.PV_NEW * POWER((1 +(tblTest.OLDRTE/1200)), tblTest.OLD_NUM)) *
POWER((1+(tblTest.NEWRTE/1200)),tblTest.NEW_NUM)
FROM DBO.tblTest
INNER JOIN DBO.tblTest tblTest_1 ON
(tblTest.TPTKEY = tblTest_1.TPTKEY)
AND (tblTest.OLDDTE = tblTest_1.CHGDTE)
GO
Matt, thanks for the link, shall investigate now.
Mitch....
August 24, 2009 at 4:06 pm
Actually, Mitch, Matt and I are asking for the same thing. Thank you for your existing code, but we still need the DDL for the table(s), sample data, and expected results.
Let us know when you have finished reading that article we recommended.
August 24, 2009 at 5:17 pm
Sure, sorry Lynn, i'm getting poked from all sides here today and i'm not able to get a minutes peace !
August 24, 2009 at 5:26 pm
Mitch2007 (8/24/2009)
Sure, sorry Lynn, i'm getting poked from all sides here today and i'm not able to get a minutes peace !
Been there, done that, got the t-shirt. 😉
August 25, 2009 at 8:35 am
Hello again Lynn,
just want to apologize for wasting your time yesterday, too much
going on for me to focus but once the storm settled I was able to look closer at the Access qry rather then build on what the user explained to me.
So it looks like the update qry actually updates one field then uses that value to update the next field, not taking a value from the previous record as I first thought.
That being the case I think the easiest route is to use a temp table and then use that to complete the final table. Will be easier to complete as we are in a rush and it will still be better then the Access app they are using at the moment!
Once things settle down here for me (recently moved to a new Dept.) I plan on streamlining the new App as I become more familiar with it so that article will help me out big time.
Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply