Splicing Table Fields 1

[think]

I need to know how to "splice" a field to populate tables based on grouped criteria in that field. For exaple if I have an address file, I would want to take the state field and have access create 50 different files with the records of the diff states seperated into each one. EX. One table would be all the CA records, one would be all the AZ records and so on.

 

[Junior1544]

why split the one file into so many??
why not just use a query to pull the data you need???

on a side note, you can use a make table query to move the data as you'd like...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

Think, you do not want to create that many tables. As the other poster said, make a query, or many queries. That's the beauty of Access (and other RDBMSs)--you can have a pile of data and cut it as many ways as you want. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[think]

Ok, you need to know why I am doing this. I work for a company that does sales in territories....therefor I need to take a list and split it into states.....so I do need 50 seperate files so they can shoot the lists cross country to the appropriate sales reps......is there anything I can do without putting in 50 different queries???

 

[Junior1544]

you can setup some vba... it'll run slowly to do this type of work... but i would think coding it would be the best way...

unless this is a one time thing then do it by hand with queries...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

Either queries or vba (which really shouldn't be perceptibly slower) are the way to go, for sure, especially for the kind of thing you're talking about here.

Here's a little function to send a single query

Function MailTheQuery()
Dim strMsg As String
Call DoCmd.SendObject(acSendQuery, "qryPartition", _
acFormatXLS, "jwallace@tiaa-cref.org", , , "Partition Query", _
"Here's that query.", False)
End Function

What I'd do in your case is set up a tbl that stores the states each rep has, then loop through the a recrodset of all the reps, gathering all of the states into a comma-delimited string and use that in the where query of an sql string, which you would make be the sql of some pre-saved query, which would get sent to the rep, then loop to the next rep and do it all over.

The table would just have two fields RepID and State.

It would look something like this in structure (though this is NOT code, and clearly hasn't been tested). I just finished writing. It's far from usable, most of it's just in English, but this basic structure should work for you.

Let us know if you have specific problems.

Function SendRepsTheirData
dim variables: db, strSql, rst1, rst1, strStateList, qdf

set db
strsql = select reps from tblperson
set rst1 = strsql

do until rst1.eof
strsql = select states for this rep from that new table
set rst2 = strsql
do until rst2.eof
strStateList = strstatelist & ", " & rst2!Statelist
rst2.movenext
loop
strStatelist = mid(2,strstatellist) [wrong syntax]
strsql = Select stuff to send where State In (" _
& strstatelist & &quot"
set qdf = db.queries("YourQueryName&quot [?syntax?]
qdf.sql = strsql
call sendobject [send the named query to the e-mail
addy of the rep in rst1.
rst1.movenext
loop

Now clean up all your object variables, like a good kid

End Function
=============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.