Updating - Based on a query

[Cosmicnoodle]

I have a list of sites (approx 15,000) that I need to update.
Basically, against each site we store a region code (HR1 - HR7) and we have decided to increase the number of regions we operate with. I have created a spreadsheet that states the post codes included in each new region (NR) and now I want to create a query that looks at the post code of the site, then according to my spreadsheet, changes it to the new region. There are no new sites to be added, simply old ones to be updated.

How can I do this easily?
I have imported my spreadhseet into access, and I have all the data there - just unsure of the query to update.....

 

[neileg]

Assuming you want to use the query grid rather than raw SQL:

Make a backup of your database

Open a new query and add in your site table and your new region table. Create a join between the two tables on the post code field. Ensure that the join type returns all records from the site table those from the new table that match. Add the key field from the site table to the grid and the field that holds the region code. Add the region code from the new table to the grid. Run this query and have a look at the results and make sure you are getting sensible answers and that all sites have a corresponding new region code.

If this is OK, change the query type from select to update. In the new line that appears in the grid for Update To, in the column that holds the region code from your site table, enter the field name from your new table that holds the new region code. Run this query and your data will be updated with the new region code.

 

[Cosmicnoodle]

The problem I have isnt that i cannot create the update query, but more that I do not know how to use wildcards within a query.
For example, I want the query to look at the table with new regions (see below) and if the postcode BEGINS "AB" then change the HR2 code to NR1

ID Post Code New Region
1 AB NR1
2 AL NR10
3 B NR8
4 BA NR13
5 BB NR5
6 BD NR3
7 BH NR13

The post code could be "AB4 8UJ" but i need the query to select it based on the fact that it starts "AB"

Can I just use an asterisk?

Would this be easier to achieve with SQL?

 

[Mile-O]

Like "AB*"

Or, if referencing a textbox on a form

Like [Forms]![FormName]![TextBox] & "*"

 

[Cosmicnoodle]

Not sure if this is the way im explaining my objective incorrectly, but i think I should try again

I have 15,000 sites that all have a post code, and a HR area code.

I need a query that will look at each sites individual post code, and (in the same field - Site_Area_Code in table SCSite) change the old HR code to one of the New NR codes that it will need to look up from the table tbl_NewRegions

Each site has a post code that woul be something along the lines on "AL4 8UJ" but the referance table only has "AL"

I cannot create a query for each of the 127 post code beginnings...

 

[neileg]

Create a calculated field in a query based on your site table that looks like this:-
NewField: Left([PostCode], 2)

Now create a new query. Add the first query and your region table. Join this on NewField and the corresponding field in the region table make it an update query as described before.

 

[Cosmicnoodle]

I know this is a pain, but is it possible to select the left 2 charactors ONLY if they are letters, not numbers?
For example, east london post codes are like "E4 7UH" or "E17 5TH" so I would want these to return "E"
But northampton post codes are "NN3 6EU" so I would want them to read "NN"

 

[ScottGem]

You would need to do this with a custom function. Something like:

Public Function RegionPrefix(strPost As String) As String

Dim strFirst As String
Dim strSecond As String

strFirst = Left(strPost,1)
strSecond = Mid(strPost,2,1)

If IsNumber(strSecond) Then
RegionPrefix = strFirst
Else
RegionPrefix = strFirst & strSecond
End If

End Function

Disclaimer: This code is off the top of my head and untested. You may have to play with the syntax to get it to work, but the logic is sound.

 

[Cosmicnoodle]

Ok,
well a very experienced Access programmer has given me the following code,
Private Function retpc(v) As String

retpc = IIf(Asc(Mid(v, 2, 1)) >= 48 And Asc(Mid(v, 2, 1)) <= 57, Left(v, 1), Left(v, 2))
End Function

Public Sub asd()

Dim sql$, rs As New ADODB.Recordset, cn As New ADODB.Connection

Set cn = CurrentProject.Connection
sql = "select * from Tbl_Temp_Update"

rs.Open sql, cn, 3, 1
Do Until rs.EOF
sql = "update Tbl_Temp_Update set Site_Post_Code=""" & retpc(rs!Short_Post_Code) & """ where id=" & rs!id
cn.Execute sql
rs.MoveNext
Loop
rs.Close

End Sub

but I cannot get it to work!?

 

[Mile-O]

well a very experienced Access programmer

They missed out a few things and I've changed a few things to make it look better and more functional although I wouldn't advocate a few things in it such as creating queries as strings.

Private Function RetPC(ByVal v As String) As String

    Dim intCode As Integer
    
    intCode = Asc(Mid(v, 2, 1))

    If intCode >= 48 And intCode <= 57 Then
        RetPC = Left(v, 1)
    Else
        RetPC = Left(v, 2)
    End If
        
End Function

Public Sub asd()

    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim strSQL As String

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    strSQL = "SELECT * FROM [Tbl_Temp_Update]"
    
    rs.Open strSQL, cn, 3, 1
    
    Do Until rs.EOF
        strSQL = "UPDATE [Tbl_Temp_Update] SET [Site_Post_Code] = """ & RetPC(rs!Short_Post_Code) & """ WHERE  ID = " & rs!id
        cn.Execute strSQL
        rs.MoveNext
    Loop

    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing

End Sub

 

[ScottGem]

Well their retpc function is basically the same as mine. They are just using the ASCII value to check if its a number instead of the IsNumber function. Frankly, I think my code is easier to follow.

As for the other module, You can do it in code, but I would just create my query usaing the function to return either the first or first 2 characters. Then use that as a join in an update query rather then looping thru.

 

[Cosmicnoodle]

Ok,
Im still having no luck in getting this to work so I have attached a cut down version of the file so that you can have a proper look...
Just to recap:
The table Tbl_Temp_Update (cut down to save size) contains a Site_Area_Code field, which currently read HR1, HR2 etc - this needs to be changed to NR1, NR2 etc based on the first 1/2 charactors in a post code.
Tbl_New_Regions contains the list of Post code beginnings for each of the NR codes.

Basically I need to set up a system that will automatically look at the post code, and give me the New Region, and preferably overwrite the existing HR code with the new NR one....
Hope this makes sense - and any help would be great as I am afriad this ones beating me!