Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Access VBA SQL Looping at the Fieldname level.

    MS Access VBA SQL code issue.

    This SQL code works fine in my MS Access 2007 database:

    SELECT Tampa_2011.[Question32AstonMartin], Tampa_2011.[Question24], Count(Tampa_2011.[RespondentId]) AS CountOfRespondentId
    FROM Tampa_2011
    GROUP BY Tampa_2011.[Question32AstonMartin], Tampa_2011.[Question24]
    HAVING (((Tampa_2011.[Question32AstonMartin])=1));


    The problem is I have many fields in the (1) table {Tampa2011} that begin as Question32++++ where ++++ is a different automobile manufacturer. For example:
    [Question32Acura]
    [Question32Buick]
    [Question32Honda], etc.

    I want to be able to use VBA code to run the above query where I change the name of the car (= “Buick”, etc.).
    Additionally, I want to be able to substitute the Fieldnames with ascending numbers i.e. Question1, Question2, Question3, etc., and then use VBA to execute SQL by programmatically looping through the fieldnames. Anyone analyzing survey databases and doing many SQL crosstabs (Q1 vs. Q2, Q1 vs. Q3, Q2 vs. Q3, etc.) would love this.

    This is as far as I got on the coding with errors:

    Dim Carname As String
    Dim qSQL As String
    Carname = "Buick"
    qSQL = "SELECT Tampa_2011.[Question32" & Carname & "], Tampa_2011.[Question24], " & _
    "Count(Tampa_2011.[RespondentId]) AS CountOfRespondentId " & _
    "FROM Tampa_2011 " & _
    "GROUP BY Tampa_2011.[Question32" & Carname & "], Tampa_2011.[Question24] " & _
    "HAVING (((Tampa_2011.[Question32" & Carname & "])=1));"
    DoCmd****nSQL qSQL

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You appear to be on the right track. The problem is RunSQL can only run action queries; that's a simple select query. Depending on your end goal, you can open a recordset on that SQL or set the SQL of a saved query to it (using a DAO QueryDef) for use as the source of a report or something.

    By the way, your HAVING clause should probably be a WHERE clause (which would be directly after the FROM clause). The WHERE clause is applied before the aggregation, so it would be more efficient. The HAVING would be appropriate if you only wanted the records where the Count() returned more than 100 or something like that, where the criteria had to be applied after the aggregation. You may not see a difference if there aren't that many records in the table.
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    Hay carman

    Idea for you

    SQL ="SELECT Tampa_2011.[Question32+++++], Tampa_2011.[Question24], Count(Tampa_2011.[RespondentId]) AS CountOfRespondentId
    FROM Tampa_2011
    GROUP BY Tampa_2011.[Question32+++++], Tampa_2011.[Question24]
    HAVING (((Tampa_2011.[Question32+++++])=1));"

    SQL = replace(SQL,"+++++",carname)

    It make it Easter to read not reading between the ""
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    eeek
    bad design
    push the car make up a layer and hang the questions off that
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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