435,575 Members | 3,016 Online
Need help? Post your question and get tips & solutions from a community of 435,575 IT Pros & Developers. It's quick & easy.

# Need help porting an SQL Query to MS Access

 P: 2 I'm trying to convert a query that runs in Query Analyser (SQL Server 2000) to work in MS Access. This will then help me modify the query further and use it to create reports and such. This query is not something I'm used to seeing in SQL, but the function is to automatically establish what month we are in and how many days there are supposed to be in this month. Then divide the total accumulated values (which are provided in a table) and divide them by that number to get accurate average values. I'm pasting the query below. Look forward to your responses. Expand|Select|Wrap|Line Numbers /* CUSTOMERWISE DEBIT/CREDIT AVERAGE BALANCE */ Declare @MaxDay smallint Declare @Month Char(4) Declare @Year Char(4) Declare @sSQL Varchar(1000) Select @Year = C_YYYY, @Month = C_MM from Saved_Input_Periods Select @MaxDay = I_Cal_DaysInPer from period_definition Where Year(D_End_date) = @Year And MOnth(D_End_date) = @Month Select @sSQL = '' Select @sSQL = 'Select [ACCOUNTNUMBER]=C_GLACCNO,[CUSTOMERNAME]=C_Cus_ShortName,' Select @sSQL =  @sSQL + '[AVERAGE DR BALANCE]=SUM(M_IDSMonthlyAccDrBal/' + Convert(Varchar(2),@MaxDay ) + '),' Select @sSQL =  @sSQL + '[AVERAGE CR BALANCE]=SUM(M_IDSMonthlyAccCrBal/' + Convert(Varchar(2),@MaxDay ) + '),' Select @sSQL =  @sSQL + '[AVERAGE BALANCE]=SUM(M_IDSMonthlyAccumBal/' + Convert(Varchar(2),@MaxDay ) + ') from gl_P200612' Select @sSQL =  @sSQL + ' WHERE C_CusSubSeg Between 250 and 260' Select @sSQL =  @sSQL + ' GROUP BY C_GLACCNO,C_Cus_ShortName' Select @sSQL =  @sSQL + ' ORDER BY C_Cus_ShortName' Exec (@sSQL) Feb 12 '07 #1