[Newbie] Export query results as multiple csv based on column values

S

sugargenius

I've been tasked with updating an Access macro that exports company
actual costs into csv. The macro does some importing then runs a query
to create the output table. Finally, the output table is exported via
TransferText. The output table looks like

CHARGE_NUM BUDGET_ELEMENT HOURS DIRECT OVERHEAD
1001-001 43 100 5000 1500
1001-001 45 100 4500 100
1002-001 43 100 5000 1500

The first 4 characters of CHARGE_NUM are the project id. Right now the
macro exports one big file for all projects. I need it to create
seperate export files for each project.

If this were in foxpro, I might do something like:

LOCAL lcProjId
SELECT DISTINCT SUBSTR(CHARGE_NUM,1,4) AS PROJ_ID FROM OUTPUT INTO
CURSOR _csrProjID

SCAN
lcProjId = _csrProjID.PROJ_ID
SELECT * FROM OUTPUT WHERE SUBSTR(CHARGE_NUM,1,4) = lcProjId
IF RECCOUNT()>0
COPY TO lcProjId + "_ACTUALS.CSV" TYPE DELI
ENDIF
ENDSCAN

How would I accomplish this with Access?

Thanks,
Woody
 
A

Albert D. Kallal

We will assume that the ms-access table is output

Dim rst As DAO.Recordset
Dim strSql As String

Set rst = CurrentDb.OpenRecordset(
"select distinct left(fax,4) as ProjID from OUTPUT")

Do While rst.EOF = False
strSql = "select * into [Text;FMT=Delimited;HDR=Yes;Database=C:\]." & _
rst!projid & "_ACTUALS.CSV" & _
" from OUTPUT where left(fax,4) = '" & rst!projid & "'"
CurrentDb.Execute strSql
rst.MoveNext
Loop
rst.Close

The first set rst = is wrapped, but would be on one....
 
S

sugargenius

Albert said:
change "fax" to charge_num .....
Click to expand...

Thanks Albert.

I put this in a module:

Sub MakeProjectOutput()
Dim rst As Recordset
Dim strSql As String
Set rst = CurrentDb.OpenRecordset("select distinct left([Chg No],4)
as ProjID from OUTPUT")
Do While rst.EOF = False
strSql = "select * into
[Text;FMT=Delimited;HDR=Yes;Database=C:\]." & _
rst!projid & "_ACTUALS.CSV" & _
" from OUTPUT where left([Chg No],4) = '" & rst!projid & "'"
CurrentDb.Execute strSql
rst.MoveNext
Loop
rst.Close
End Sub

I had to change DAO.Recordset to Recordset because I got an error
"user-defined type not defined"
Is there a reference I need?

After I removed DAO qualifier, I tried to run and it bombed on line
Set rst = CurrentDb.OpenRecordset("select distinct left([Chg No],4)
as ProjID from OUTPUT")
I get error:
"Run-time error '13'. Type mismatch"

Doesn't the OpenRecordset method return a recordset

I tried the query
select distinct left([Chg No],4) as ProjID from OUTPUT;

and that worked fine.

Thanks for your help
Woody
 
S

sugargenius

Albert,

I added a reference to DAO 3.6 Object Library, and it work fine.

Thanks again,
Woody
 
A

Albert D. Kallal

In all fairness, you likely could have saved the sql, and used the
transferText command, the code might have been a bit clearer...

But, there is always a zillion ways to do the same thing.
Albert,

I added a reference to DAO 3.6 Object Library, and it work fine.
Click to expand...

Great stuff on your part. (you obviously worked this out your own...).

Great, a2003 includes the reference by default now (a2002, and a2000 does
not). However, if you convert from a older mdb that does not have the
reference, then the setting is respected...
 
Top