Batch Processing in Access

[Mike Cullinan]

I'm working in a small real estate office and have been using Access in
conjunction with downloaded MLS data to help investors find properties with
the most equity, with potential for fixing up and selling. I want to take it
to the next level buy being able to do comparables at a subdivision level
within the queries. This would save an immense amount of time looking for
candidate properties.

I've got the general idea how to go about it and what needs to be done. For
each zip code, there are a certain number of residential subdivisions. The
idea is to standardize the subdivision names in all the area's zip codes.
Once that is done, an average can be taken of the sold properties in each
subdivision. Then, the zip code and the standardized subdivision name can be
a combo key to use in a join.

I've been reading a beginner's book on Access VBA, and I've gotten a lot out
of it, but it doesn't have any sections on batch processing and updating of
tables. It's mainly about users updating data in tables by filling out text
boxes on a form and clicking a button to update, add, or delete. Can any one
direct me to a good source for batch processing in Access, such as a book or
a web site?

I'm not talking about millions of records here, just a few thousand at a
time. The data in my tables is very transient, with records constantly being
added and removed as properties come onto the market or get sold. Thanks in
advance.

 

[Smartin]

Hi Mike, please see comments within.

I'm working in a small real estate office and have been using Access in
conjunction with downloaded MLS data to help investors find properties with
the most equity, with potential for fixing up and selling. I want to take it
to the next level buy being able to do comparables at a subdivision level
within the queries. This would save an immense amount of time looking for
candidate properties.

I've got the general idea how to go about it and what needs to be done. For
each zip code, there are a certain number of residential subdivisions. The
idea is to standardize the subdivision names in all the area's zip codes.
Once that is done, an average can be taken of the sold properties in each
subdivision. Then, the zip code and the standardized subdivision name can be
a combo key to use in a join.

So, you will need to create and maintain a table or two of zip codes and
subdivision names. If subdivisions can span more than one zip code there
will be two tables involved to handle this relationship. The end result
you need is to allow the choice of zip/sub to come only from this
structure. That provides the standardization. Then you can create
queries/reports that roll up to the zip and/or subdivision level.

I've been reading a beginner's book on Access VBA, and I've gotten a lot out
of it, but it doesn't have any sections on batch processing and updating of
tables. It's mainly about users updating data in tables by filling out text
boxes on a form and clicking a button to update, add, or delete. Can any one
direct me to a good source for batch processing in Access, such as a book or
a web site?

I don't think batch processing is what you are looking for. Data
normalization, on the other hand, might be worth your time to read
about. Lots of websites and books discuss this, plus it's a frequent
topic in this and related groups.

I'm not talking about millions of records here, just a few thousand at a
time. The data in my tables is very transient, with records constantly being
added and removed as properties come onto the market or get sold. Thanks in
advance.

As a suggestion, you may find you can get more value from your database
by not deleting old records. If a property is sold, you can indicate
this in a field and exclude such properties from active searches. If you
retain old records you will have the ability to extract historical
information. There is little to fear in retention, Access can more than
likely handle the volume.

Hoping this helps,

 

[Tom Wickerath]

Hi Mike,

A recent example of batch processing, with transient data, can be found in
post# 25 in this thread:

http://groups.google.com/group/micr..._frm/thread/7b380894aba9aab6/4e13e75f99839aa1

The tblItemsSoldExcel table represents the temporary table, where records
are constantly being added and deleted. You might try working through this
example to see if it gives you an idea of how to approach your particular
situation.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Albert D. Kallal]

The problem here is that I think you using the wrong term as to what you
are looking for.

Batch jobs tend to refer to a process that is run daily, or nightly to
have "further" processing done on the data that you entered.

This so called batch processing was far more common when we had
punched cards (you had no choice, but to organize your work into
sequential batches data to work with).

Today, computers are so much more interactive, and also faster.

So, I don't think you are actually talking about a daily, or nightly
batch job that you run to process your work after you done it.
(besides...with the speed we have today, most processing
can be done on the fly).

The industry as a general rule has much gone away from batch
processing as a concept.

However, what I do think you are looking for is how do you write
code to update tables? For any type of payroll, accounting system
there will often be "processes" that you need to run that modifies
the table data..and this is not done through editing the data on
a form, or interactively.

I've been reading a beginner's book on Access VBA, and I've gotten a lot
out
of it, but it doesn't have any sections on batch processing and updating
of
tables.

Well, either the book does not touch on the use of sql update statements, or
reocrdses
(It should), or you looking for the "wrong" term. I don't think the term
'batch' processing
is appropriate here. However, if you can update data via code...you are home
free in
your quest.

In general, to update tables in code, you simply sql update statements, or a
recordset.

eg:

dim strSql as string

strSql = "update tblcustomers set City = 'New York' " & _
" where City = 'N.Y'"

currentdb.Execute strSql, dbFailOnError

So, you can well see in the above that the code would change all occurrences
of N.Y. to New York.

You can also do sequential record processing by using a recordset.

We could skip the use of sql as above...and simply write code to do what
that sql update does


dim rstData as dao.recordset

set rstData = currentdb.OpenrecordSet("tblCustomers")

do while rstData.EOF = false
if rstData!City = "N.Y." then
rstData.Edit
rstData!City = "New York"
rstData.Update
end if
rstData.MoveNext
loop
rstData.Close

And, you can even mix the use of sql and recordsets...

eg:

dim rstData as dao.recordset
dim strSql as string

strSql = "select * from tblcustomers where City = 'N.Y."
set rstData = currentdb.OpenrecordSet(strSql)

do while rstData.EOF = false
rstData.Edit
rstData!City = "New York"
rstData.Update
rstData.MoveNext
loop
rstData.Close

In the above example, you don't have to "test" for the city, since we
limited the processing loop to only records with city = N.Y.

Of course, the above loop example is quite poor, since that whole loop can
be reached with one line of code (like the first example). All 3 of the
above code examples actually make the same results.

So, when you write code to update data, you typically use sql statements, or
recordsets to process that data via code.

As for the batch processing? That is really more of a issue if you going
have a button on a form that user presses to run your update code, or we
decide to run this process as a nightly batch job via the windows scheduler
(you can use the windows scheduler to launch ms-access...run some code...and
shut down).

I don't think a book is going to much talk about batch processing in
ms-access. And, further, the above quite well covers how you can use code to
update data.

 

[Mike Cullinan]

Hi Mike, please see comments within.

Mike Cullinan wrote:
----------------------------------snip
So, you will need to create and maintain a table or two of zip codes and
subdivision names. If subdivisions can span more than one zip code there
will be two tables involved to handle this relationship. The end result
you need is to allow the choice of zip/sub to come only from this
structure. That provides the standardization. Then you can create
queries/reports that roll up to the zip and/or subdivision level.

That's pretty much it. One table is going to be the "subdivision lexicon"
composed of zip codes with subdivision names that are standardized, sorted by
zip and then subdivision name. Another table will have all the listing
information with non-standardized subdivision names, sorted in the same
order. The idea is to read the first row in the subdivision lexicon and use
a like query to select all records with that zip code and like subdivision
names. Then the code will go through the result set sequentially and replace
the non-standard subdiv names on the target table with the standardized name
from the lexicon, for example:

Lexicon
zip subdiv
75002 ALLEN NORTH

TargetTable
Zip subdiv
75002 ALLEN NORTH #03
75002 ALLEN NORTH #04

sql = "select zip, subdiv from TargetTable where TargetTable.zip =
Lexicon.zip and TargetTable.subdiv Is Like Lexicon.subdiv*"

and then
Replace TargetTable.subdiv* with Lexicon.subdiv

I know that's pretty crude pseudo-code, pseudo-sql or whatever, but you get
the idea. The process will have to be done to two different tables: one
table with several months of sold information to get the average per square
foot on the sold properties per subdivision. That will be the comps. Then
the process will need to be done on the downloaded MLS information to
standardize the subdivision names to the lexicon. Then the zip/subdivision
can be a combo key to do a join the comps with the active listings.


----------------------------snip

I don't think batch processing is what you are looking for.

Almost everyone who responded pointed that out. Yes, a better term for me
to use probably would have been "automated process" as opposed to "batch
processing".

Data
normalization, on the other hand, might be worth your time to read
about. Lots of websites and books discuss this, plus it's a frequent
topic in this and related groups.

I will definitely check that out. It's a problem similar to address
standardization. That problem presented itself when I first tried to join
the foreclosure information by address to the downloaded MLS listings. There
are too many ways a simple address can be written. I took a clue from the
way IT department at MLS support did it, which to take only the house number
(only one way that can be written and have it be meaningful and accurate) and
the street name, disregarding all the extraneous designations such as
"drive", "parkway" etc.

As a suggestion, you may find you can get more value from your database
by not deleting old records. If a property is sold, you can indicate
this in a field and exclude such properties from active searches.

This is handled in the way I download the MLS listings, setting the software
to download only active listings. The MLS listings that I download daily, or
sometimes several times a day, are the real transient stuff. The records of
foreclosures that I keep on my side is permanent. Records are added each
month but nothing is deleted.

If you
retain old records you will have the ability to extract historical
information. There is little to fear in retention, Access can more than
likely handle the volume.

I'm very appreciative of the power of Access, and have some other ideas for
initiatives I would like to start on once I get this project figured out and
working.

Hoping this helps,

It certainly did, thanks again.

 

[Mike Cullinan]

:

----------------------------snip

So, when you write code to update data, you typically use sql statements, or
recordsets to process that data via code.

As for the batch processing? That is really more of a issue if you going
have a button on a form that user presses to run your update code, or we
decide to run this process as a nightly batch job via the windows scheduler
(you can use the windows scheduler to launch ms-access...run some code...and
shut down).

It will most likely be a form with a button to click and launch the update
process whenever I want. I'm looking at your examples, and comparing with
those from the book.

I'm assuming it's possible to have more than one cursor at a time. I would
be dealing with two record sets, one would be from the source table with the
standardized subdivision names. The other record set would be from the
target table where the zip code matches the source table's current zip, with
subdivisions like the source tables current subdivsion name. I would advance
the cursor through each row of the target table, updating the subdivision
names until EOF, then advance the cursor on the source table to the next
subdivision name within that same zip, and get another result set. Once all
the subdivision names for that zip are complete, go to the next zip in the
source table, get the first subdivision name there, and keep looping like
that until all the subdivision names in the target table are standardized to
the ones in the source table.

I don't think a book is going to much talk about batch processing in
ms-access. And, further, the above quite well covers how you can use code to
update data.

I will certainly put it to use, thanks!

 

[Albert D. Kallal]

I'm assuming it's possible to have more than one cursor at a time.

Yes....(we using the term recordset as cursor...and that kind of ok in this
case).

I would
be dealing with two record sets, one would be from the source table with
the
standardized subdivision names. The other record set would be from the
target table where the zip code matches the source table's current zip,
with
subdivisions like the source tables current subdivsion name. I would
advance
the cursor through each row of the target table, updating the subdivision
names until EOF

I don't think you need a loop and record by record processing. Why not
use a relational join in the sql query? for all your reports etc, you can
join in that zip. Of course if the zip code is a "range" that you must
compare to, and not a 1 to 1 translation/lookup to the zip table, then
yes...you must use/write looping code. However, if it is just some
lookup values in anther table..then no code is needed at all, and
simply use a relational join in the query.....