This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
asked
MS access code converting to sql
i need to convert the following code to SQL and i am at a loss as i don't know access coding. Option Compare Database Option Explicit Public Function explode() Dim db As Database, rsExplode As Recordset, rsClaims As Recordset, rsRecNum As Recordset Dim i As Long, lngRecNum As Long Set db = CurrentDb Set rsExplode = db.OpenRecordset("dbo_vwClaimsToExplode", dbOpenDynaset, dbSeeChanges) Set rsClaims = db.OpenRecordset("dbo_tmpClaims", dbOpenDynaset, dbSeeChanges) Set rsRecNum = db.OpenRecordset("dbo_tmpClaims_RecNumAssignment", dbOpenDynaset, dbSeeChanges) If rsExplode.RecordCount > 0 Then rsExplode.MoveFirst While Not rsExplode.EOF If rsExplode![Value] = "9" And rsExplode![Qty] > 0 Then For i = 1 To rsExplode![Qty] rsRecNum.AddNew rsRecNum![Operator] = "Explode" rsRecNum![dtmAdded] = Now() rsRecNum.Update lngRecNum = DMax("[Rec Num]", "dbo_tmpClaims_RecNumAssignment", "Operator = 'Explode'") With rsClaims .AddNew ![Dlr Num] = rsExplode![Dlr Num] ![Received Date] = rsExplode![Received Date] ![Clm Date] = rsExplode![Received Date] ![Clm Spec 1] = Left(rsExplode![Reference], 10) ![Clm Spec 2] = rsExplode![Record Number] ![Amt Claimed] = Round(rsExplode![Claimed Amt] / rsExplode![Qty], 2) ![Prod Code 1] = rsExplode![product] ![Ad From] = rsExplode![dtmFrom] ![Ad To] = rsExplode![dtmTo] ![Prod Code 2] = rsExplode![bundleModel] ![Media Desc] = DLookup("[Promo Misc 1]", "dbo_Promos", "Promo = '" & rsExplode![prognum] & "'") ![Promo] = rsExplode![prognum] ![Trans Code] = "P" ![Media] = 9 ![Ratio] = 100 ![Number of Ads] = 1 ![Doc Type] = "L" ![Locked] = 1 ![Audited] = 0 ![Proc Date] = Date ![Control Num] = 0 ![Amt Appr] = 0 ![Amt Paid] = 0 ![Audit 1] = "01" ![Prod Code 1 %] = 0 ![Prod Code 2 %] = 0 ![Prod Code 3 %] = 0 ![Prod Code 4 %] = 0 ![Prod Code 5 %] = 0 ![Prod Code 6 %] = 0 ![Prod Code 7 %] = 0 ![Prod Code 8 %] = 0 ![Prod Code 9 %] = 0 ![Prod Code 10 %] = 0 ![UM] = 0 ![Paid From Pending] = 0 ![Status] = 1 ![Operator] = rsExplode![Operator] ![Rec Num] = lngRecNum .Update End With Next i ElseIf rsExplode![Value] = "16" Or (rsExplode![Value] = "9" And rsExplode![Qty] ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
Whilst I would usually ask you to at least attempt it, I feel that my first step to approach this sort of problem would be to try to convert it into pseudocode, assuming documentation is not available, then go from there to SQL. The task is more onerous because the code is procedural code, not set based, so ideally you want to convert it and also change how it works, to get the same result. So here's a rough translation. I've left some of it intact, but you should be able to get the meaning from what's below. Since you state that your issue is in understanding the Access code, this should hopefully help to give you the means to take it forward. Do let us know how you get on. open connections to "dbo_vwClaimsToExplode" (rsExplode), "dbo_tmpClaims" (rsClaims), "dbo_tmpClaims_RecNumAssignment" (rsRecNum ) For each record in (rsExplode) do the following: BEGIN If rsExplode.[Value] = "9" And rsExplode.[Qty] > 0 Then BEGIN Add rsExplode.[Qty] new records to rsRecNum with the values: rsRecNum![Operator] = "Explode" rsRecNum![dtmAdded] = current datetime. define a value lngRecNum to be the Max of "[Rec Num]" from "dbo_tmpClaims_RecNumAssignment" where "Operator = 'Explode'" Add a new row to rsClaims. Set columns ![Dlr Num] = rsExplode![Dlr Num] ![Received Date] = rsExplode![Received Date] ![Clm Date] = rsExplode![Received Date] ![Clm Spec 1] = the first 10 characters of rsExplode![Reference] ![Clm Spec 2] = rsExplode![Record Number] ![Amt Claimed] = Round(rsExplode![Claimed Amt] / rsExplode![Qty], 2) ![Prod Code 1] = rsExplode![product] ![Ad From] = rsExplode![dtmFrom] ![Ad To] = rsExplode![dtmTo] ![Prod Code 2] = rsExplode![bundleModel] ![Media Desc] = get the value of [Promo Misc 1]" from (join to) "dbo_Promos" where "Promo = '" & rsExplode![prognum] ![Promo] = rsExplode![prognum] ![Trans Code] = "P" ![Media] = 9 ![Ratio] = 100 ![Number of Ads] = 1 ![Doc Type] = "L" ![Locked] = 1 ![Audited] = 0 ![Proc Date] = Date ![Control Num] = 0 ![Amt Appr] = 0 ![Amt Paid] = 0 ![Audit 1] = "01" ![Prod Code 1 %] = 0 ![Prod Code 2 %] = 0 ![Prod Code 3 %] = 0 ![Prod Code 4 %] = 0 ![Prod Code 5 %] = 0 ![Prod Code 6 %] = 0 ![Prod Code 7 %] = 0 ![Prod Code 8 %] = 0 ![Prod Code 9 %] = 0 ![Prod Code 10 %] = 0 ![UM] = 0 ![Paid From Pending] = 0 ![Status] = 1 ![Operator] = rsExplode![Operator] ![Rec Num] = lngRecNum (defined earlier) END ELSE if rsExplode![Value] = "16" Or (rsExplode![Value] = "9" And rsExplode![Qty] ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
I'm getting there will definitely keep you posted and thank you.
No problem - happy to help clarify any individual functions you're stuck on, especially if you can show how far you've got.
You can also try Sql Server Migration Assistant for Access. The download link is:
http://www.microsoft.com/en-in/download/details.aspx?id=28763
declare @recnum as varchar(100), @lngrecnum as varchar(100), @vwclaimscount as int, @count as int select @vwclaimscount = COUNT([dlr num]) from claimstoexplode select @lngrecnum = MAX([rec num] + 1) from tmpClaims_RecNumAssignment print @lngrecnum select @count = claimstoexplode.qty from dbo.claimstoexplode if @vwclaimscount > 0 begin insert into tmpClaims_RecNumAssignment ( operator, dtmadded) values ('explode5', getdate()) insert into tmpclaims ([Dlr Num] ,[Promo] ,[Trans Code] ,[Proc Date] ,[Control Num] ,[Invoice Num] ,[Clm Date] ,[Amt Claimed] ,[Amt Appr] ,[Amt Paid] ,[Ad From] ,[Ad To] ,[Media] ,[Media Desc] ,[Audit 1] ,[Audit 2] ,[Audit 3] ,[Audit 4] ,[Audit 5] ,[Audit 6] ,[Prod Code 1] ,[Prod Code 1 %] ,[Prod Code 1 Amt] ,[Prod Code 1 Adv Amt] ,[Prod Code 2] ,[Prod Code 2 %] ,[Prod Code 2 Amt] ,[Prod Code 2 Adv Amt] ,[Comment] ,[Received Date] ,[Last Modified] ,[Ratio] ,[Number Of Ads] ,[Amt Applied] ,[UM] ,[Audited Amt] ,[Rated Ads] ,[Branch Number] ,[Operator] ,[Bal Pending] ,[Paid From Pending] ,[Store Num] ,[Clm Spec 1] ,[Clm Spec 2] ,[Status] ,[Rec Num] ,[Doc Type] ,locked ) select [dlr num] ,prognum ,'P' ,'' ,'0' ,'' ,[received date] ,[claimed amt]/qty ,'0' ,'0' ,dtmfrom ,dtmto ,'9' ,'' ,'01' ,'' ,'' ,'' ,'' ,'' ,[Product] ,'0' ,'0' ,'0' ,bundlemodel ,'0' ,'0' ,'0' ,'' ,[received date] ,'' ,'100' ,'1' ,'0' ,'0' ,'0' ,'0' ,'0' ,operator ,'0' ,'0' ,'0' ,LEFT(reference,10) ,[record number] ,'1' , @lngrecnum ,'L' ,'1' from claimstoexplode set @vwclaimscount = @vwclaimscount - 1 set @lngrecnum = @lngrecnum + 1 end
it will input into the tmpclaimsrecnum_assignments but i keep getting cannot insert duplicates into tmpclaims. FYI the table is empty. I cleaned out all data to see if that solved the duplicate insert. I have no idea how to do the looping and they wanted this by today but i've been so very busy it isn't going to happen. I downloaded and tried the migration assistant but it does not bring in modules.
It looks like you've simplified the code a little (removing some lookups) and I'm guessing you're getting a PK failure in the tmpclaims table. If you can remove the INSERT into tmpclaims you will see returned to you the work it is trying to do, which may make the problem obvious. You appear to be pulling all records from claimstoexplode and using a single value for @lngrecnum - I'm guessing you need to remove this and perhaps replace it with a join to tmpClaims_RecNumAssignment to give you unique rows in your set. If you can't spot it, can you advise the key fields for tmpclaims?
i have simplified it only for temporary. Just so i could see the progress I wasn't making in this case. As for lngrecnum that is getting the value from the secondary table and that value is showing correctly when i use print to screen.
SET ARITHABORT OFF SET ANSI_WARNINGS OFF declare @recnum as varchar(100), @lngrecnum as varchar(100), @vwclaimscount as int, @count as int select @vwclaimscount = COUNT([dlr num]) from claimstoexplode select @lngrecnum = MAX([rec num] + 1) from tmpClaims_RecNumAssignment print @lngrecnum select @count = claimstoexplode.qty from dbo.claimstoexplode if @vwclaimscount > 0 begin insert into tmpClaims_RecNumAssignment ( operator, dtmadded) values ('explode5', getdate()) insert into tmpclaims1 ([Dlr Num] ,[Promo] ,[Trans Code] ,[Proc Date] ,[Control Num] ,[Invoice Num] ,[Clm Date] ,[Amt Claimed] ,[Amt Appr] ,[Amt Paid] ,[Ad From] ,[Ad To] ,[Media] ,[Media Desc] ,[Audit 1] ,[Audit 2] ,[Audit 3] ,[Audit 4] ,[Audit 5] ,[Audit 6] ,[Prod Code 1] ,[Prod Code 1 %] ,[Prod Code 1 Amt] ,[Prod Code 1 Adv Amt] ,[Prod Code 2] ,[Prod Code 2 %] ,[Prod Code 2 Amt] ,[Prod Code 2 Adv Amt] ,[Comment] ,[Received Date] ,[Last Modified] ,[Ratio] ,[Number Of Ads] ,[Amt Applied] ,[UM] ,[Audited Amt] ,[Rated Ads] ,[Branch Number] ,[Operator] ,[Bal Pending] ,[Paid From Pending] ,[Store Num] ,[Clm Spec 1] ,[Clm Spec 2] ,[Status] ,[Rec Num] ,[Doc Type] ,locked ) select [dlr num] ,prognum ,'X' ,GETDATE() ,'0' ,'' ,[received date] ,round([claimed amt]/qty,2) ,'0' ,'0' ,dtmfrom ,dtmto ,'9' , dbo.Promos.[Promo Misc 1] ,'01' ,'' ,'' ,'' ,'' ,'' ,[Product] ,'0' ,'0' ,'0' ,bundlemodel ,'0' ,'0' ,'0' ,'' ,[received date] ,'' ,'100' ,'1' ,'0' ,'0' ,'0' ,'0' ,'0' ,claimstoexplode.operator ,'0' ,'0' ,'0' ,LEFT(reference,10) ,[record number] ,'1' , @lngrecnum ,'L' ,'1' from claimstoexplode inner join dbo.promos ON dbo.Promos.Promo = dbo.claimstoexplode.prognum set @vwclaimscount = @vwclaimscount - 1 set @lngrecnum = @lngrecnum + 1 end
There is updated code. Now i'm stuck on how i get it to loop through the recnumassigment table and insert the number of each line based on claimstoexplode.qty for each record. Any help would be greatly appreciated.
You could make that set based by joining to the recnumassignment table and then to a numbers table (or select rownumber from sys.columns if you don't have one - there are several ways to generate one) - where numbers.number =< claimstoexplode.qty. This will then generate N lines where claimstoexplode.qty = N. This should eliminate the need to loop on each one.
I am almost there. This code iterates through the claims to explode but it doesn't enter the same row the number in quantity field and it doesn't iterate recnumassignment. SET ARITHABORT OFF SET ANSI_WARNINGS OFF declare @recnum as varchar(100), @lngrecnum as varchar(100), @vwclaimscount as int, @count as int select @vwclaimscount = COUNT([dlr num]) from claimstoexplode --outer loop number select @lngrecnum = MAX([rec num] + 1) from tmpClaims_RecNumAssignment print @lngrecnum select @count = claimstoexplode.qty from dbo.claimstoexplode --inner loop begin while @vwclaimscount > 0 Begin insert into tmpClaims_RecNumAssignment ( operator, dtmadded) values ('explode5', getdate()) insert into tmpclaims1 ([Dlr Num] ,[Promo] ,[Trans Code] ,[Proc Date] ,[Control Num] ,[Invoice Num] ,[Clm Date] ,[Amt Claimed] ,[Amt Appr] ,[Amt Paid] ,[Ad From] ,[Ad To] ,[Media] ,[Media Desc] ,[Audit 1] ,[Audit 2] ,[Audit 3] ,[Audit 4] ,[Audit 5] ,[Audit 6] ,[Prod Code 1] ,[Prod Code 1 %] ,[Prod Code 1 Amt] ,[Prod Code 1 Adv Amt] ,[Prod Code 2] ,[Prod Code 2 %] ,[Prod Code 2 Amt] ,[Prod Code 2 Adv Amt] ,[Comment] ,[Received Date] ,[Last Modified] ,[Ratio] ,[Number Of Ads] ,[Amt Applied] ,[UM] ,[Audited Amt] ,[Rated Ads] ,[Branch Number] ,[Operator] ,[Bal Pending] ,[Paid From Pending] ,[Store Num] ,[Clm Spec 1] ,[Clm Spec 2] ,[Status] ,[Rec Num] ,[Doc Type] ,locked ) select [dlr num] ,prognum ,'X' ,GETDATE() ,'0' ,'' ,[received date] ,round([claimed amt]/qty,2) ,'0' ,'0' ,dtmfrom ,dtmto ,'9' , dbo.Promos.[Promo Misc 1] ,'01' ,'' ,'' ,'' ,'' ,'' ,[Product] ,'0' ,'0' ,'0' ,bundlemodel ,'0' ,'0' ,'0' ,'' ,[received date] ,'' ,'100' ,'1' ,'0' ,'0' ,'0' ,'0' ,'0' ,claimstoexplode.operator ,'0' ,'0' ,'0' ,LEFT(reference,10) ,[record number] ,'1' , @lngrecnum ,'L' ,'1' from claimstoexplode inner join dbo.promos ON dbo.Promos.Promo = dbo.claimstoexplode.prognum set @vwclaimscount = @vwclaimscount - 1 set @lngrecnum = @lngrecnum + 1 set @count = @count - 1 End End
15 People are following this question.
