John:
we create the 48 subset files for each township and export them to .dbf
format for each township. When 'clients' request voter registration records
we then burn the .dbf files along with others to a cdrom and provide them
with the data. Since .dbf is a common denominator file structure we do not
have to worry about what software the 'clients' are using on the other end.
They are able to import it into whatever they want... including excel or back
into access.
All I want to do is without writting 48 different queries break the large
table I created from the comma delimited file into 48 different tables (one
for each towhship) so that I can then export them into 48 different .dbf
files that can be given to our 'clients', who may or maynot have (most of the
time don't) any comfortablity with the magic box. [Most of the time have to
walk them through finding the files on the CD-ROM
]
I have this code in a module but have not figured out how to get it to run.
Function Create_Table(strTableName As String, strTWS As String)
Dim strSQL As String
strSQL = "SELECT Sos0205_M.ID, Sos0205_M.CONG, Sos0205_M.LEG,
Sos0205_M.REP, Sos0205_M.TOWNSHIP,"
strSQL = strSQL & " Sos0205_M.WARD, Sos0205_M.PCT , Sos0205_M.RD_MM,
Sos0205_M.RD_DD,"
strSQL = strSQL & " Sos0205_M.RD_YY, Sos0205_M.LAST, [first] & "" "" &
[suf] AS FIRST_NAME,"
strSQL = strSQL & " Sos0205_M.ADDRESS, Sos0205_M.CITY, Sos0205_M.ZIP,
Sos0205_M.SEX, "
strSQL = strSQL & "Sos0205_M.BD_MM, Sos0205_M.BD_DD, [bd_cc] & [bd_yy]
AS BD_YR, "
strSQL = strSQL & "Sos0205_M.PHYSIMP, Sos0205_M.feb05, Sos0205_M.mar04,
Sos0205_M.nov04, "
strSQL = strSQL & "Sos0205_M.feb03, Sos0205_M.apr03, Sos0205_M.mar02,
Sos0205_M.nov02, "
strSQL = strSQL & "Sos0205_M.feb01, Sos0205_M.apr01 "
strSQL = strSQL & "INTO " & strTableName & " FROM Sos0205 "
strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS
strSQL = strSQL & "ORDER BY Sos0205_M.PCT, Sos0205_M.LAST,
Sos0205_M.ADDRESS, [first] & " ' '" & [suf]; "
DoCmd.RunSQL strSQL
End Function
I called it with this query code...
SELECT Create_Table([«strTableName»],[«strTWS»]) AS Expr1
FROM Sos0205_M;
but get error message that debug indicates my DoCmd.RunSQL with strSQL won't
work. so close yet so far....
--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL
312-603-1123 Phone
312-603-0982 Fax