1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Rep Power

    Question combining many rows into one

    Please help me figure out a solution to this problem.

    I have a table in Microsoft Access that tracks the amount of money received from various companies. In some cases there is more than one amount received from the same company. Here is some sample data from the table:

    CompanyID, Date, Amount, Code

    5, 12/18/2000, $250.00, B M
    5, 7/3/2001, $350.00, SP
    8, , $100.00, M
    9, , $150.00, M
    9, , $250.00, gen

    I need to create a new table so that all data associated with the same CompanyID appears in the same row. See example below:

    CompanyID, Date, Amount, Code, Date2, Amount2, Code2

    5, 12/18/2000, $250.00,B M, 7/3/2001, $350.00, SP
    9, , $150.00, M, , $250.00, gen

    I know this is inefficient in terms of database design. However the reason I need this new table is to export the data into excel for further manipulation.

    Any ideas on how I can accomplish this task? Any help would be appreciated. Thanks. -Josh
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Central Florida, USA
    Rep Power
    Sorry for the briefness of this reply, but I am short on time these days:

    You want a query, not a new table. Access uses the word "query" to mean what we often call a "view" in the world of SQL.

    Specifically, you want a Crosstab query, which can reorganize columns into rows. Access happens to have a wizard for creating new queries, and "Crosstab Query Wizard" is one of the choices. Play with this for awhile, and if it doesn't make sense, bring your findings back here.

    Once you have created this query, then the query can be imported into Excel via ODBC (just as if it were a table), or you can even just copy and paste the ouptut into Excel.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo