Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Should be easy - row to column

    So far I've had a small function for a little task. I receive a lot of CSV files that contain 2 columns, a modelname of ar, and a year for the model.

    What I have my function do, is sum up the cars by unique model, and crete one row with all years in one column.

    I was wondering if there isn't another - simpler - way to do this, as it seems to be quite common.

    The data I receive look like this

    Ford 1987
    Ford 1988
    Ford 2007
    Toyota 2001
    Toyota 2003
    Toyota 2009

    What my VB function returns is something like this.

    Ford, 1987|1988|2007
    Toyota, 2001|2003|2009

    So, is my functon the only way, or is there some simpler way to do it?

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Trin

    A crosstab query will suit your needs. In your table you will need a third column to show the number of cars for each year. If no number is available then when you import to your table have a field model_total with a default value of 1.

    I have attached a sample database based on your data.
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2005
    Posts
    183

    Not qite

    I see where you're going, but its not quite the "goal".

    I've attached an example in excel, that you might see what I'm trying to reach.

    Cheers, Trin
    Attached Files Attached Files
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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