By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,575 Members | 3,016 Online
Bytes IT Community
+ Ask a Question
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
  1. /* CUSTOMERWISE DEBIT/CREDIT AVERAGE BALANCE */
  2. Declare @MaxDay smallint
  3. Declare @Month Char(4)
  4. Declare @Year Char(4)
  5. Declare @sSQL Varchar(1000)
  6. Select @Year = C_YYYY, @Month = C_MM from Saved_Input_Periods
  7. Select @MaxDay = I_Cal_DaysInPer from period_definition Where Year(D_End_date) = @Year And MOnth(D_End_date) = @Month
  8. Select @sSQL = ''
  9. Select @sSQL = 'Select [ACCOUNTNUMBER]=C_GLACCNO,[CUSTOMERNAME]=C_Cus_ShortName,'
  10. Select @sSQL =  @sSQL + '[AVERAGE DR BALANCE]=SUM(M_IDSMonthlyAccDrBal/' + Convert(Varchar(2),@MaxDay ) + '),'
  11. Select @sSQL =  @sSQL + '[AVERAGE CR BALANCE]=SUM(M_IDSMonthlyAccCrBal/' + Convert(Varchar(2),@MaxDay ) + '),'
  12. Select @sSQL =  @sSQL + '[AVERAGE BALANCE]=SUM(M_IDSMonthlyAccumBal/' + Convert(Varchar(2),@MaxDay ) + ') from gl_P200612'
  13. Select @sSQL =  @sSQL + ' WHERE C_CusSubSeg Between 250 and 260'
  14. Select @sSQL =  @sSQL + ' GROUP BY C_GLACCNO,C_Cus_ShortName'
  15. Select @sSQL =  @sSQL + ' ORDER BY C_Cus_ShortName'
  16. Exec (@sSQL)
Feb 12 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I think you need to post some table MetaData for this.
Otherwise it is possible, but very difficult, to decipher what is hapenning.
Feb 12 '07 #2

P: 2
I think you need to post some table MetaData for this.
Otherwise it is possible, but very difficult, to decipher what is hapenning.
I'm sorry but that just flew right over me. MetaData? If you could elaborate, I might be able to comply.
Feb 13 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
Sorry - I normally do post an example with it. I must have been in a rush.
MetaData is the definition of a table.

Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
Feb 13 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.