Batch Processing in Access

Discussion in 'Microsoft Access' started by Guest, Jan 26, 2007.

  1. Guest

    Guest Guest

    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.
    --
    My very best regards,

    Mike
     
    Guest, Jan 26, 2007
    #1
    1. Advertisements

  2. Guest

    Smartin Guest

    Hi Mike, please see comments within.

    Mike Cullinan wrote:
    > 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,

    --
    Smartin
     
    Smartin, Jan 26, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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
    __________________________________________

    "Mike Cullinan" wrote:

    > 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.
    > --
    > My very best regards,
    >
    > Mike
     
    Guest, Jan 26, 2007
    #3
  4. 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.

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, Jan 26, 2007
    #4
  5. Guest

    Guest Guest

    "Smartin" wrote:

    > 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.

    > Smartin
    >
     
    Guest, Jan 27, 2007
    #5
  6. Guest

    Guest Guest

    "Albert D. Kallal" wrote:

    ----------------------------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!
     
    Guest, Jan 27, 2007
    #6
  7. >
    > 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.....


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, Jan 29, 2007
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Charles L. Phillips

    Batch Processing

    Charles L. Phillips, Nov 3, 2003, in forum: Microsoft Access
    Replies:
    1
    Views:
    390
    Dirk Goldgar
    Nov 3, 2003
  2. lalexander

    BATCH EMAIL PROCESSING

    lalexander, Oct 4, 2004, in forum: Microsoft Access
    Replies:
    2
    Views:
    171
    lalexander
    Oct 4, 2004
  3. Replies:
    0
    Views:
    234
  4. Guest

    batch Processing

    Guest, Mar 1, 2007, in forum: Microsoft Access
    Replies:
    1
    Views:
    299
    Guest
    Mar 2, 2007
  5. Chip

    Batch Email processing

    Chip, Jun 7, 2008, in forum: Microsoft Access
    Replies:
    1
    Views:
    176
    Dave C
    Jun 7, 2008
Loading...

Share This Page