Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: Advance query help

    Hi,
    I am trying to get data from a CACHE database from Access 2002, using ODBC.
    Here is my issue:
    trying to get average_balance for all accounts but average_balance is stored into 12 different fields (avg_bal_amt_1, avg_bal_amt_2,......avg_bal_amt_12).
    Database have another set of 12 fields (avg_bal_mth_1, avg_balmat_2,.....avg_bal_mth_12) which contains string like "200605". If avg_bal_mth_11 contains "200605" than the average_balance for May would be in field avg_bal_amt_5.
    How can I modify the following query to get desired results?

    SELECT
    SH_ACCOUNT.acn,
    SH_ACCOUNT.sh_sfx,
    SH_ACCOUNT.avg_bal_amt_5
    FROM
    SH_ACCOUNT
    WHERE
    SH_ACCT.close_dt IS NULL

    I would appriciate any help,

    Thanks,

    Sam

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is your cache db live? Last time I had to deal with aggregating data from cache, I pulled it out nightly via MSSQL DTS, massaged the data into a workable format and ran the reports off the transformed data.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by sd007
    Hi,
    I am trying to get data from a CACHE database from Access 2002, using ODBC.
    Here is my issue:
    trying to get average_balance for all accounts but average_balance is stored into 12 different fields (avg_bal_amt_1, avg_bal_amt_2,......avg_bal_amt_12).
    Database have another set of 12 fields (avg_bal_mth_1, avg_balmat_2,.....avg_bal_mth_12) which contains string like "200605". If avg_bal_mth_11 contains "200605" than the average_balance for May would be in field avg_bal_amt_5.
    How can I modify the following query to get desired results?

    SELECT
    SH_ACCOUNT.acn,
    SH_ACCOUNT.sh_sfx,
    SH_ACCOUNT.avg_bal_amt_5
    FROM
    SH_ACCOUNT
    WHERE
    SH_ACCT.close_dt IS NULL

    I would appriciate any help,

    Thanks,

    Sam

    Hi Sam,

    If avg_bal_mth_11 contains "200605" than the average_balance for May would be in field avg_bal_amt_5.

    How come the avg for month 11 (ie avg for Nov) contains avg bal amout for May?

    I think you better explain in detail what you're going to do?

  4. #4
    Join Date
    Jun 2006
    Posts
    4

    Re:avg_bal field explanation

    Thanks. Yes you have a valid question that why avg_bal_amt_11 is not for november and that is the challenge I am facing that this database stores avg_bal_amt based upon when the account was opened; if my account was opened in Jan 2006 then my avg_bal_amt will be stored in avg_bal_amt_12. If the account was opened in Feb 2006, it will will stored in avg_bal_amy_01.

    That is the reason we need to look for strin in avg_bal_mth_1 through 12 to find out which avg_bal_amt field to pick. I am sorry to make things more complicated.

    Sam.

  5. #5
    Join Date
    Jun 2006
    Posts
    4
    No. I am running against a shadow server which is in synch with LIVE DB. We don't extract dat from LIVE due to possible performance degrading issue. I am sure MSSQL DTS is better solution for data extraction. I will be looking into this option as we already have a SQL server license.

    Thanks,

    Sam

  6. #6
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by sd007
    Thanks. Yes you have a valid question that why avg_bal_amt_11 is not for november and that is the challenge I am facing that this database stores avg_bal_amt based upon when the account was opened; if my account was opened in Jan 2006 then my avg_bal_amt will be stored in avg_bal_amt_12. If the account was opened in Feb 2006, it will will stored in avg_bal_amy_01.

    That is the reason we need to look for strin in avg_bal_mth_1 through 12 to find out which avg_bal_amt field to pick. I am sorry to make things more complicated.

    Sam.
    i think u need to count on your application which do direct access on the database, make a query on the table something like , select avg_bal_amt from tableA (sorry i forget the name of your table), will will get a string out eg. 200605, then your application is able to break up the string into the year and the month, then use that value to do another query where u place the data.

    u see your query above

    SELECT
    SH_ACCOUNT.acn,
    SH_ACCOUNT.sh_sfx,
    SH_ACCOUNT.avg_bal_amt_5
    FROM
    SH_ACCOUNT
    WHERE
    SH_ACCT.close_dt IS NULL

    there is no way u can ask the database to choose which field to retrieve out, for eg. if u got a string 200601, the select query become


    SELECT
    SH_ACCOUNT.acn,
    SH_ACCOUNT.sh_sfx,
    SH_ACCOUNT.avg_bal_amt_1
    FROM
    SH_ACCOUNT
    WHERE
    SH_ACCT.close_dt IS NULL

    but u can do that in the application, if u have problem forming the SQL statement in the application, do 12 query in the access database, with different name like JanQuery, FebQuery....

    then on your applicatioin when the string is 200601, then your application will run JanQuery, when 200602 it will run FebQuery... etc

    and u can change the name of the field to a common name

    eg. for JanQuery

    SELECT
    SH_ACCOUNT.acn,
    SH_ACCOUNT.sh_sfx,
    SH_ACCOUNT.avg_bal_amt_1 AS avg_bal_amt
    FROM
    SH_ACCOUNT
    WHERE
    SH_ACCT.close_dt IS NULL

    then u garantee to get same name for every query, and u wont have problem on using that value in the future

    Hope this help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •