SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot toograffic's Avatar
    Join Date
    Mar 2002
    Location
    Sarasota, Florida
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Convert column data into field names?

    I hope I can make this clear enough:
    I have a table (in Access) with data in a field called "QuestionNumbers". This table is built from a survey, with "QuestionNumbers" representing each question in the survey. If the surveyee answers a question, the question number is put into this field, with the answer to that question in another field. If the person answers, say, 5 questions, there are 5 entries in this table under that persons ID number, one for each question. To use this data for my purposes, I want to make a crosstab query and use each distinct number in the "QuestionMumbers" field into a field name, with each row representing a unique id and the questions they answered.

    Here's the problem (I know, finally, right?)- I am using Cold Fusion, and variables can not start with a number, so how do I make each field that is has number for a name into a field that starts with a letter?

    Thanks!
    The two most common elements in the universe are hydrogen
    and stupidity. - Harlan Ellison
    Retirement Communities and Senior Housing Options
    Freecycle.org

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't know if there's a way to assign aliases inside an access crosstab query, but in any case, even if there is, you will get different numbers of columns depending on the data you happen to run against

    while this isn't necessarily fatal, it does present certain challenges to the CF code, namely, looping through the column names to decide how many to print, etc.

    if you're going to have code that's already this complex, why not do the crosstabbing in CF

    i have an article that gives an example: Dynamic multi-level crosstab layouts in ColdFusion

    i apologize for the poor quality of the explanation in that article, i am working on a revision which should make it a lot easier to understand what's going on, however, it may be a while before i get around to it

    in the meantime, it may give you some ideas


    rudy

  3. #3
    SitePoint Zealot toograffic's Avatar
    Join Date
    Mar 2002
    Location
    Sarasota, Florida
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thank You!

    Ahhhhhh! Cold Fusion 5! I'll need to get some documentation explaining all the new features in Cold Fusion 5/MX! I think your article will help quite a bit in how to layout my code to do the cross-tabbing in CF.

    As a web designer for a company that still uses CF4.5, I have learned just enough CF to be dangerous. I am nowhere near qualified to be called a programmer, but basic database work is no problem. It's when I run into these more advanced (to me) challenges that I get into trouble.

    As the project I'm curently working on is a personal venture, the site is being hosted on servers with CF 5/MX, so I can use the nifty new features you mention in your article. I look forward to being able to contribute such articles in the future, as well as to this forum!

    Thanks again!
    The two most common elements in the universe are hydrogen
    and stupidity. - Harlan Ellison
    Retirement Communities and Senior Housing Options
    Freecycle.org

  4. #4
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    incidentally, in CFMX you *can* have structure keys that start with numbers. i don't know about simple variables, and i know its not recommended. i did it by accident recently though, and it worked.

    the common workaround if you go that route, is to prefix the number with an underscore, and then don't ever forget you did it that way.

  5. #5
    SitePoint Zealot toograffic's Avatar
    Join Date
    Mar 2002
    Location
    Sarasota, Florida
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hmmmmmm.....

    The way I first recognized my "number for a field name" quandry was by uploading my pages to the host and having them break! Just as a side note, CF error pages suck! They never seem to tell you what you really need to know. Not as bad as ASP, but bad enough.
    The two most common elements in the universe are hydrogen
    and stupidity. - Harlan Ellison
    Retirement Communities and Senior Housing Options
    Freecycle.org


Bookmarks

Posting Permissions

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