Page 1 of 4 1234 LastLast
Results 1 to 15 of 55
  1. #1
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38

    Exclamation How to Update 70,000++ Records

    Hi all,



    So--new problem-- I have 41 Tables that look something like this:

    APN OWNER ACRES Site.ADDRESS Mail.ADDRESS

    10220 Freddie 4 124 Right Rd. 124 Right Rd.
    13445 USA 10 Washington DC Washington DC
    15687 IRS 23 Washington DC Washington DC
    13455 Tammy 12 ABC avenue ABC avenue

    ...with anywhere from 70,000-1 million records....

    Basically, I want to summarize this information by creating an "OWNERSHIP CLASS" field which summarizes the information in the "OWNER" fields.

    Ideally, I would like to fit all my data into 5 key classes:

    GOVERNMENT
    FOREST INDUSTRY
    NON-INDUSTRY/BUSINESSES
    FAMILY>10acres
    FAMILY<10acres

    So, if accomplished, my above sample table should look something like this:

    APN OWNER ACRES ADDRESS OWNER CLASS

    10220 Freddie 4 124 Right Rd. FAMILY<10acres
    13445 USA 10 Washington DC GOVERNMENT
    15687 IRS 23 Washington DC GOVERNMENT
    13455 Tammy 12 ABC avenue FAMILY>10acres


    I can't seem to find a clear cut way of doing this using the Append Query....and since I have 41 tables (with 70,000-mil records) I need to find a pretty good system of doing this.

    Any thoughts???

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I guess I would ask how many different entries will you find that fall into the other than Family class? How many different strings will we be looking for?

  3. #3
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    "..how many different entries will you find that fall into the other than Family class? "

    Do you mean of the "70,000-1 mil" entries? Well, in one table I have 42,466 different listed owners (there is 70,080 records total). I'd say the bulk of that 42,000 is in the 2 Family classes....like at least 3/4ths of it. It will vary though from table to table....

    "How many different strings will we be looking for?"

    Do you mean how long the owner name is...? The whole classifying scheme will be based on the name.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I figured we could determine the GOVERNMENT, FOREST INDUSTRY and NON-INDUSTRY/BUSINESSES records first and the rest would be Family and you just need to look at the acreage value to catagorize. How many different string values in those first three classes will we be looking for? USA, IRS, etc. Will these strings all be in a table so they can be looked up and classified?

  5. #5
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    Hmm...I think I understand what you are asking.

    I do have a complete list of all possible "FOREST INDUSTRY" (I can email that to you). "GOVERNMENT" could be done... (if I create a list for it)...but it's a little more intuitive (If it has California or United States anywhere in the string/title--then it's government).

    I have no way whatsoever of deciphering what is NON-INDUSTRY/BUSINESS...(there are a million names, and nothing in common with each other). So, I forsee a problem separating that class from the FAMILY classes.

    Does that answer the question....? I've just had one person tell me this can't be done...but if I can query by group from 70,000+ records to 4,000+ records....maybe the task of doing it manually is plausible?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That is going to be the stopping point. Remember, a computer is actually pretty dumb and can only do what we tell it to do. If you can come up with a method to clasify the records then the rest is all doable.

  7. #7
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    Okay, well then I have an idea (maybe you know how to implement it)?

    So, I create a list of names for "Govt" and "Forest Industry", and then--after I classify those--I do a filter/search option on the remaining strings to find key words that are frequently used in businesses (i.e. like "Partnership", "LLC", "CORPORATION"). Classify that, and then do the Family Farms (like you suggested by acreage).

    I also have another column of data classes (in my original spreadsheet) that distinguishes urban land classes from forest. If I can query those from my database, before doing the classification scheme mentioned above...I think I will reduce the # of errors from classifying the businesses based on their ending titles...do you think I can do this (and how?)

    Govt
    Forest Industry
    Non-Industry businesses,etc
    Family
    >10 acres
    Family
    <10 acre

    UNITED STATES OF AMERICA PL or
    UNITED STATES OF AMERICA SRNF PL
    Or UNITED STATES OF AMERICA PARKS PL
    GREEN DIAMOND RESOURCE COMPANY
    PARTNERSHIP



    CALIFORNIA STATE OF PARKS PL or CALIFORNIA STATE OF PL
    SCOTIA PACIFIC CO LLC
    LLC



    HUMBOLDT COUNTY OF PL
    SCOTIA PACIFIC COMPANY LLC
    TRUST



    Local
    BARNUM TIMBER CO
    COMPANY



    District
    EMMERSON R H & SON LLC
    PROPERTIES



    Tribal or YUROK and HOOPA
    SOPER-WHEELER COMPANY
    BANK




    SIERRA PACIFIC HOLDING CO
    LTD




    THE PACIFIC LUMBER CO
    L P




    EEL RIVER SAWMILLS INC
    DEVELOPMENT




    THE PACIFIC LUMBER COMPANY
    INC





    CORPORATION

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Can any of this be done in Excel before the import?

  9. #9
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    RG,

    Man is this tricky.

    Well, I played around with compound statements using the logical functions in Excel...and that's not really cutting it.

    What I think I need to do is create a query that looks up all key words in all the strings. For simplicity, those key words are "TRUST", "COMPANY", and "LTD" (using an "or" operator, so when if the word is ROGER LTD the expression still recognizes that there is "LTD" even though "TRUST" and "COMPANY" is not in the phrase)?.

    If the words "TRUST", "COMPANY", and "LTD" exist in the string a new field earmarks those strings in the class "NON-INDUSTRY/BUSINESSES"...

    Doable?

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Probably but it may take more than one pass. Look into the Like operator in your query. I should warn you that queries and SQL are not currently my strong points.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You might take a shot at the string table. At least two fields: a string to look for and a field that tells what class this belongs in. You might as well put in an Autonumber fields as well. You never know when you are going to need it.

  12. #12
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38

    Solved

    Hey RJ,

    Sorry to be incommunicado. Figured it out. Thought I'd post for future ref.

    So...assume I have this table named "Owners.Name"

    NAME OWNER1

    Bob Fred
    Dudley Doright The Doright Trust
    Nancy P. Smith Nancy Smith
    Rob Red The Red Trust


    ...and I want to create a new field (i.e. "Class1") which evaluates the Owner1 field and finds all the records that have the word "Trust" in it. Then I want the label 'Trust' to be added accordingly to Class1.

    In SQL query mode I write:

    SELECT Owners.Name, Owners.Owner1, IIf(InStr(1,[Owner1],"Trust")<>0,"TRUST","") AS CLASS1
    FROM Owners;

    From that I will get the "Class1" Field and the corresponding information...

    Thanks again RJ!

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    There is so much more learning happening when you solve issues yourself. Excellent! Glad to hear you got it sorted.

  14. #14
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38

    Almost there....

    Well, maybe I spoke to soon...

    So I figured out how to conduct an instring search and put the results in the classes...but no good way to combine it. Also, since I'm weeding two classes out by the default of it being the last classes (i.e. Family >10acres and Family<10acres) it's important to nail down a system of combining the previous classified data so the I know what's left (and is therefor in one of the "Family" classes.

    So, in SQL mode, if I write:
    SELECT Humboldt_Join_noURB.Humboldt_firerank_APN, Humboldt_Join_noURB.Owner1,
    IIf(
    InStr(1,[Owner1], "UNITED STATES OF AMERICA")
    OR InStr(1,[Owner1], "CALIFORNIA STATE")
    OR InStr(1,[Owner1], "PL")
    OR InStr(1,[Owner1], "HUMBOLDT COUNTY")
    OR InStr(1,[Owner1], "CITY OF")
    OR InStr(1,[Owner1], "YUROK")
    OR InStr(1,[Owner1], "HOOPA")
    <>0 ,"GOVT","")
    AS OWNERCLASS
    FROM Humboldt_Join_noURB;
    I get all the entries in the Government class. Same query, and same operators (with different "word options") gives me the entries for "Forest Industry" and "Non-industry buisnesses". However, I can't seem to combine these separate queries into one compound statment that puts ALL the classified ids (i.e. "Government", "Forest Industry", and "Non-Industry") into ONE column.

    I figured I would need an "AND" operator to combine them...but all versions of doing this that I've tried won't work, or are not putting all the ids into ONE column...

    Kinda stuck...seems so simple...blargh.

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Create a Public User Defined Function (UDF) in a standard module and make sure the module is not named the same as *any* procedures anywhere. THat is why we usually start a module name with basNameOfSomeKind. Use a Select Case in your function and pass the function both owner fields and the acreage field and return the OWNERCLASS as a string. It will be a lot easier to understand and maintain. I take it you will be running this code on a regular basis otherwise you could just take one shot at it.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Return records not updated by update query
    By ars80 in forum Queries
    Replies: 2
    Last Post: 05-01-2006, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums