floundering - help with generating territory data

[Clay Hilton]

I am having difficulty with MS Access and I am not sure how to go about
getting assistance. At this point I have considered just hiring someone
locally to do this work but ultimately I just need the quickest solution so
I thought I would post to see if it was somehow easily explainable (and I
really don't know anyone who specializes in this anyway). I do not know VBA
but I think I can work with macros. Problem is - I am not very familiar
with access macros and I cannot figure a way to get this done.

I have one table (TABLE1)which has a customer list with a unique customer
number and complete address. I have another table (TABLE2) that list
territoires for the US by zip code (there is a field "territory" and each
territory contains certain zip codes). Here is what I need to do:

1. Write a query or macro that exports all of the data from TABLE1 sorted by
territory (the territory information only exists in TABLE2 currently but it
would reference the zip code field in each table to determine which
territory a customer would belong to).
2. Export the data generated for each territory to a separate excel file --
terr1.xls, terr2.xls, terr3.xls, etc. So each file would contain just the
customers for a particular territory.

If you have ideas on how to do this or a suggestion on how to get assistance
completing this taks it will be much appreciated.

thanks,
Clay

 

[Steve Schapel]

Clay,

Step 1 is pretty straighforward, assuming you mean you have the zip code
in the customers table as a separate field. As you suggested, all you
need is a query that includes both tables, joined on the zip code field
from each, and you will then immediately have access to the territory
for each customer.

Step 2 involves a looping type of procedure. Whereas this is possible
with a macro, it is awkward, and for looping functionality VBA provides
the smoothest solution. The basic concept would be to create a
recordset of the list of territories, and loop through this recordset in
code, for each one running a DoCmd.OutputTo method to export the data to
the Excel file for that territory. It is not all that hard, although I
realise there would be a learning curve for you if you haven't done
stuff like this before.

 

[Clay Hilton]

Thanks much Steve. I will give it a shot.

Clay,

Step 1 is pretty straighforward, assuming you mean you have the zip code
in the customers table as a separate field. As you suggested, all you
need is a query that includes both tables, joined on the zip code field
from each, and you will then immediately have access to the territory for
each customer.

Step 2 involves a looping type of procedure. Whereas this is possible
with a macro, it is awkward, and for looping functionality VBA provides
the smoothest solution. The basic concept would be to create a recordset
of the list of territories, and loop through this recordset in code, for
each one running a DoCmd.OutputTo method to export the data to the Excel
file for that territory. It is not all that hard, although I realise
there would be a learning curve for you if you haven't done stuff like
this before.