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:
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
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.