How to Update 70,000++ Records

[UCBFireCenter]

Interesting.



Total noob question: How does one create a module....do I need to do this in VBA? I'm looking at a text and it seems to imply this can't be done in SQL. Is there no command in the ribbon to do this?

Also...my queries don't connect succesfully using the "AND" operator. Not totally sure what you mean by "...use a Select Case in your function and pass the function both owner fields and the acreage field..."...

[RuralGuy]

Let's create a new standard module first. On the ribbon go to Create> and then on the right you see Macro with a down arrow below it. Click the down arrow and select Module.

[UCBFireCenter]

Kay, I'm there.

I'm looking at O'Reilly Access 2007 while I'm doing this...

So you think I should create a Macro, to enforce a sequence of actions (i.e. classify first by Govt, the classify remaining records Forest, and so on and so on) right?

well, one concern: Some of the criteria in my search statement is going to have to change from county to county (I have a table for each county--41 to be precise). So, for example, for the Humboldt table I am using the words "HUMBOLDT COUNTY" in my in-string search statement. I can't do that for any other county then Humboldt...so the criteria I use in Humboldt can't be done in El Dorado county (for example).

Is this a problem? If not, do I just cut and paste all the SQL code...and don't add any operators?

[RuralGuy]

I was stuck helping someone on LogMeIn but I'm back for a bit. I am not a fan of macros and do not use them. I use only code.

[UCBFireCenter]

I see.

So, okay, in the Module interface...just copy and paste the SQL code....

since the AND operator wasn't working on connecting the separate statements in the Query Design...should I do something about it?

[RuralGuy]

Type the following in the module:
Public Function Foo() As String

[RuralGuy]

Access will add an End Function on a subsequent line to complete the Function.

[RuralGuy]

Here's starting code for your new function:

Public Function ClassType(InOwner As String, LotSize As Double) As String
'-- Examine the Owner field and return the Class Code
   If InStr(1, InOwner, "CALIFORNIA STATE") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner, "PL") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner, "HUMBOLDT COUNTY") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner, "CITY OF") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner, "YUROK") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner, "HOOPA") Then
      ClassType = "GOVT"
   Else
      ClassType = "Unknown"
   End If
End Function

[UCBFireCenter]

Okay, thanks...

So I assume adding:

ElseIf InStr(1, InOwner1, "SCOTIA PACIFIC COMPANY") Then
ClassType = "FOREST INDUSTRY"

For the other classes will input the different classes correctly>?

How do you call a function?

[RuralGuy]

Your SQL will look something like:

SELECT Humboldt_firerank_APN, Owner1, LotAcres, _
ClassType([Owner1], [LotAcres]) As OwnerClass _
FROM Humboldt_Join_noURB;

...and your assumption on the rest of the function is correct.

[UCBFireCenter]

So the SQL Query calls the function?

I tried it (as exactly what you wrote in)...and I got a syntax error. Admittedly, I'm not the best at syntax so...I'll check it.

I'm nervous about the VB code (that's VB right?). I've got an hierarchical order that this has to be classified in (meaning: first I have to do GOVT, then FOREST INDUSTRY, then NON-INDUSTRY OR BUSINESS...). The hierarchy has to exist because there are classes that could be misclassed if not weeded out first.

For example:

The entry "SCOTIA PACIFIC LUMBER LLC" is a "FOREST INDUSTRY"...but because it has "LLC" in it, it could be classified as "NON-INDUSTRY OR BUISNESS" if it hadn't first been classified as a FOREST INDUSTRY.

Is this how this code works?


Also, for the FAMILY >10 acres and <10acres....can I use Query Design to get at them??

Here is the VB code I have so far...:
Public Function ClassType(InOwner1 As String, LotSize As Double) As String
'-- Examine the Owner field and return the Class Code

If InStr(1, InOwner1, "CALIFORNIA STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "DISTRICT") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HUMBOLDT COUNTY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CITY OF") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "YUROK") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HOOPA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "UNITED STATES OF AMERICA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CALIFORNIA THE STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "COMMUNITY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "SCOTIA PACIFIC COMPANY") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "BARNUM TIMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EMMERSON R H & SON") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SOPER-WHEELER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SIERRA PACIFIC HOLDING") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "THE PACIFIC LUMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EEL RIVER SAWMILLS") Then
ClassType = "FOREST INDUSTRY"
ClassType = "Unknown"
Public Function ClassType(InOwner1 As String, LotSize As Double) As String
'-- Examine the Owner field and return the Class Code
If InStr(1, InOwner1, "CALIFORNIA STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "DISTRICT") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HUMBOLDT COUNTY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CITY OF") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "YUROK") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HOOPA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "UNITED STATES OF AMERICA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CALIFORNIA THE STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "COMMUNITY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "SCOTIA PACIFIC COMPANY") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "BARNUM TIMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EMMERSON R H & SON") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SOPER-WHEELER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SIERRA PACIFIC HOLDING") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "THE PACIFIC LUMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EEL RIVER SAWMILLS") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "TRUST") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "COMPANY") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "PROPERTIES") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "BANK") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "LTD") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "LP") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "L P") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "DEVELOPMENT") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "INC") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "CORPORATION") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ClassType = "Unknown"
Else
ClassType = "Unknown"
End Function

[RuralGuy]

So the SQL Query calls the function?

I tried it (as exactly what you wrote in)...and I got a syntax error. Admittedly, I'm not the best at syntax so...I'll check it.

Post exactly what you have that is giving you a syntax error.

I'm nervous about the VB code (that's VB right?).

That's VBA - Visual Basic for Applications.

I've got an hierarchical order that this has to be classified in (meaning: first I have to do GOVT, then FOREST INDUSTRY, then NON-INDUSTRY OR BUSINESS...). The hierarchy has to exist because there are classes that could be misclassed if not weeded out first.

The code will take the first match and then jump to the last End If as you have described. Just put your test in the order you want them to execute.

The entry "SCOTIA PACIFIC LUMBER LLC" is a "FOREST INDUSTRY"...but because it has "LLC" in it, it could be classified as "NON-INDUSTRY OR BUISNESS" if it hadn't first been classified as a FOREST INDUSTRY.

Is this how this code works?

Exactly!

Also, for the FAMILY >10 acres and <10acres....can I use Query Design to get at them??

I called the field LotAcres in my sample. The field needs to be passed to the function along with the Name, for the Family test.

[UCBFireCenter]

Post exactly what you have that is giving you a syntax error.

I just cut and pasted what you posted earlier...which was:

SELECT Humboldt_firerank_APN, Owner1, LotAcres, _
ClassType([Owner1], [LotAcres]) As OwnerClass _
FROM Humboldt_Join_noURB;

I cut and pasted that in the Query SQL design box....wrong way to go?

[RuralGuy]

The SQL view of a query does not accept the line continuation character "_". Get rid of the two "_" and put everything on one line. Let the view break the line if it wants. Is that the name of your three fields? Humboldt_firerank_APN, Owner1, LotAcres

[UCBFireCenter]

Hi RG,

Alas, computer crashing. No idea what's happening. And if it doesn't crash I get a field called "OwnerClass" filled with the "#Error" responses (and then it crashes--erg).

So, I have several concerns. One I don't see how this SQL statement (which I write in Query Design) is calling the module. However, I KNOW, it must be calling the module because it is creating an error. Before crashing I get this sweet memo:

Microsoft Visual Basic:
Compile Error
Block If without End If

As to your last question, I have 3 fields in my SQL statement: Humboldt_firerank_APN, Owner1, FACRES (not LotAcres)...

Here is my SQL statement (written in Query Design):

SELECT Humboldt_firerank_APN, Owner1, FACRES,
ClassType([Owner1], [FACRES]) As OwnerClass
FROM Humboldt_Join_noURB;

Here is my module (written in VB), and called "ClassType"

Public Function ClassType(InOwner1 As String, FACRES As Double) As String
'-- Examine the Owner field and return the Class Code
If InStr(1, InOwner1, "CALIFORNIA STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "DISTRICT") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HUMBOLDT COUNTY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CITY OF") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "YUROK") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HOOPA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "UNITED STATES OF AMERICA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CALIFORNIA THE STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "COMMUNITY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "SCOTIA PACIFIC COMPANY") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "BARNUM TIMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EMMERSON R H & SON") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SOPER-WHEELER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SIERRA PACIFIC HOLDING") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "THE PACIFIC LUMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EEL RIVER SAWMILLS") Then
ClassType = "FOREST INDUSTRY"
ClassType = "Unknown"
ElseIf InStr(1, InOwner1, "TRUST") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "COMPANY") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "PROPERTIES") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "BANK") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "LTD") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "LP") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "L P") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "DEVELOPMENT") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "INC") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "CORPORATION") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
End Function

Any more wisdom...this crashing computer syndrome kinda is a bummer....