How to export csv with variable number of columns?

August 19, 2009 10:57 AM

Using a relational table in a one-to-many relationship in Access, what is the best way to export a csv file with one row per record (from the one half) with a variable number of columns (from the many half)?

Basically, I have a Senator with one or more staffers. I want to export (CSV) so that it looks like:

Senator 1 info | Staffer 1 info
Senator 2 info | Staffer 1 info | Staffer 2 info | Staffer 3 info
Senator 3 info | Staffer 1 info | Staffer 2 info

One row per senator, but an unknown or changing number of columns.

I'm sure there's a way to do this via query. I have to make this as easy as possible (via a button on a form) for a coworker.

Having a hard time finding an answer via Google.

posted by JeremiahBritt to computers & internet (6 comments total)

I think you might want to mess around with the join.

I'm assuming you're doing this in query designer? Right click the little line that ties the two tables together in design view. It'll give you a few options -- I think you want a left join. But change it and try a different join and if it doesn't work, try another.
posted by A Terrible Llama at 12:37 PM on August 19

Actually, no- there's not a way to do this via query, unless you have a fixed number of staffers, and you have an instance of the staffers table left joined to the senators table for each possible staffer.

If you're interested in the method of doing this via code, follow this link to Dev Ashish's Access Web. He's forgotten more about Access than you or I will ever know.
posted by Pragmatica at 12:47 PM on August 19

There's an ugly way to do this without using VBA. You would add a column to the staffers table ("staffer_index"), populate it with NULLs, and write an update query that would group by senator_id where staffer_index is NULL and populate it with 1. Then run the query again with incrementing numbers. This process can be automated with an Access macro, using an extra table to store the current staffer_index.

After that, it's a simple crosstab query to get the result set you want. Use the senator info as the row heading, staffer_index as the column heading, and the staffer info as the value.
posted by zixyer at 1:04 PM on August 19

In the past I did a bunch of nonsense after exporting it to excel:

1. Determine max number of staffers with CountIf
2. Make the necessary additional columns (Staff1 Name, Staff1 email, Staff2 Name, Staff2 Email, etc.).
3. Go hog wild with nested if statements in the resultant fields to populate them if the senator had multiple rows (staffers) and leave blank if not.
4. Dedupe by filtering on unique senator ID.

Since I'm trying to automate this, I might see about creating a Judiciary Staffer table, a Chief of Staff table, etc and then just tying them all together, 1:1 relationships. My plans of 1 staffer table with staff type and 1 senator table appear to be dashed.
posted by JeremiahBritt at 1:38 PM on August 19

If you are going to do the automation behind a button for someone, why not just suck it up and do it in VBA. It is probably easier to learn how to do that than it is to find an awful query that will do what you want.
posted by mmascolino at 2:34 PM on August 19

What you want is called a Pivot Query (not to be confused with Pivot Tables), and surprisingly it looks like Access supports them ( I know it was only added for SQL Server 2005/2008 in the SQL server world)

The following is SQL Server 2005, however the syntax looks pretty similar for Access ( I don't have access to test for you sorry).


create table #t ( senatorID int )
create table #s ( senatorID int, stafferID int, stafferName varchar(10))

insert into #t values (1)
insert into #t values (2)
insert into #t values (3)

insert into #s (senatorID, stafferID , stafferName) VALUES ( 1, 1, 'Jim' )
insert into #s (senatorID, stafferID , stafferName) VALUES ( 1, 2 , 'Sarah')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 2, 1 , 'Joe')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 2, 2 , 'Abby')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 2, 3 , 'Johnny')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 3, 1 ,'Richard')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 3, 2 ,'Theresa')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 3, 3 ,'Ann')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 3, 4 ,'Rupert')
insert into #s (senatorID, stafferID , stafferName) VALUES ( 3, 5, 'Bdissy')

select
SenatorID, [1] as staffer1, [2] as staffer2, [3] as staffer3, [4] as staffer4, [5] as staffer5, [6] as staffer6, [7] as staffer7, [8] as staffer8, [9] as staffer9, [10] as staffer10
from #s
PIVOT
(
min(stafferName)
FOR stafferID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
)
as p

Gives the following output:
SenatorID staffer1 staffer2 staffer3 staffer4 staffer5 staffer6
1 Jim Sarah NULL NULL NULL NULL
2 Joe Abby Johnny NULL NULL NULL
3 Richard Theresa Ann Rupert Bdissy NULL

You'll notice you need to specify the number of columns; In SQL server I'd probably build a dynamic sql query, in Access you'll probably have to do some VBA scripting.

All this does is automates a series of CASE statements using an aggregate for each column ( in this case the MIN () aggregate) that then groups by SenatorID.
posted by spatula at 3:03 PM on August 19