Excel VBA Process in Access VBA

[RichF01]

Hi

I have got a search routine working in Excel, but need it in Access as the number of records is too large for Excel.

I have two tables. Table 1 has fields PCode and general data, Table 2 has fields Pcode and EXZ.

The number of Pcode in Table 2 is only exhaustive when the search routine below is used and is much less than the numbers of Pcode in Table 1. I eventually need to group the data in Table 1 by EXZ.

I have tried joining the two tables, but cannot get the procedure below to work across the join.


Below is the Excel coding that works.

Sub PostCode()

'Binary Search of PostCode Data

Dim PCFound As Boolean

For a = 2 To Workbooks("Post Code Development").Worksheets("Pcode A").UsedRange.Rows.Count Step 1
    PC = Workbooks("Post Code Development").Worksheets("Pcode A").Cells(a, 1)
    PC = UCase(PC)
    PClen = Len(PC)
    NumberPCs = Workbooks("PostCodeLookup").Worksheets("Post_code").UsedRange.Rows.Count - 1
    PCFound = False

    s = 2
    f = NumberPCs
    j = 0
    For j = PClen To 1 Step -1
        k = 0
        Do While k < 15
            k = k + 1
            i = Round((s + f) / 2, 0)
            'alpha = post code
            alpha = Mid(PC, 1, j)
            'beta = postcode being compared to
            beta = Workbooks("PostCodeLookup").Worksheets("Post_code").Cells(i, 1).Value
            If alpha > beta Then
                s = i
            ElseIf alpha < beta Then
                f = i
            Else
                exitzone = Workbooks("PostCodeLookup").Worksheets("Post_code").Cells(i, 2).Value
                PCFound = True
            GoTo Found
            End If
        Loop
    Next j

Found:

    If PCFound = True Then
       Workbooks("Post Code Development").Worksheets("Pcode A").Cells(a, 2) = exitzone
    Else
        Workbooks("Post Code Development").Worksheets("Pcode A").Cells(a, 2) = "---"
    End If
    
Next a

I am not familiar with Access VBA syntax, like how to call a table etc. I'm going on a course soon . . .

Cheers, hope it is all clear and thanks in advance

Rich

 

[JerryKlmns]

Hi RichF01,

Create a new query, add both tables, drag & drop the field PCode from table1 on table2, drag & drop all fields from Table1 on the grid (bouble clicking does the same) and only EXZ field of Table2, press that greek letter of sum (if you can 't find on menu View click Totals), press that [red]![/red] button and there goes your grouping ...

This is just to give you a start with queries ....

 

[RichF01]

Sorry, I might not have explained myself very well there. I have tried what you suggested.

All the post codes in Table 1 are of the form AB12 3CD.
However the post codes in Table 2 are of all forms A, AB, AB1, AB12, AB12 3, AB12 3C and AB12 3CD. So for example, in table 1, the Post Code WX89 0YZ might not be in Table 2, but WX, WX70 and WX90 0 might be. This will obviously not be found, the truncating of the post code in the search routine until WX89 0YZ becomes WX means that I want the EXZ related to WX.

If I had the post code WX89 0YZ, then I would return the EXZ of that.

The SQL of the query above is copied below. I believe I need to call the function in my first post

FROM Post_code INNER JOIN [Summary TCG Domestic trunc] ON Post_code.[Post Code] = pcodefunction([Summary TCG Domestic trunc].Expr1)

if you get what I mean. The logic of this isn't the problem, more the syntax and the way VBA works in Access

SELECT Post_code.[Exit Zone], [Summary TCG Domestic trunc].Expr1, Sum([Summary TCG Domestic trunc].[1]) AS SumOf1, Sum([Summary TCG Domestic trunc].[2]) AS SumOf2, Sum([Summary TCG Domestic trunc].[3]) AS SumOf3, Sum([Summary TCG Domestic trunc].[4]) AS SumOf4, Sum([Summary TCG Domestic trunc].[5]) AS SumOf5, Sum([Summary TCG Domestic trunc].[6]) AS SumOf6, Sum([Summary TCG Domestic trunc].[7]) AS SumOf7, Sum([Summary TCG Domestic trunc].[8]) AS SumOf8, Sum([Summary TCG Domestic trunc].[9]) AS SumOf9 INTO [Summary TCG Dom EXZ]
FROM Post_code INNER JOIN [Summary TCG Domestic trunc] ON Post_code.[Post Code] = [Summary TCG Domestic trunc].Expr1
GROUP BY Post_code.[Exit Zone], [Summary TCG Domestic trunc].Expr1;

This routine is used to reduced the number of post codes searched from ~1,000,000 to ~6,000. I also do not have access to the complete postcode list.

I still don't think I'm explaining this very well, sorry, but thanks to all that try and help muppets like me
R

 

[JerryKlmns]

So WX89 0YZ may have not an entry on table2 but WX89 0Y does?

Then use the logic above to retrieve exact 8 characters zip code and update ("Mark"), those entries with the corresponding EXZ of table2. Then create a query to make that 8 characters to 7 like

Select Field1, Field2,.... , left([Post Code], 7)
From Post_code Where Marked Is Null;

and use this query in place of table1, I guess it is named Post_code, to retrieve 7 characters zip code on table2 and so on for 6, 4, 3, 2 and finally 1 {left([Post Code], 6), left([Post Code], 4),left([Post Code], 3), left([Post Code], 2), left([Post Code], 1)}

 

[RichF01]

Jerry

Cheers for the help. Now working well. Still not convinced it is particularly elegant, but it does the job

Thanks

Rich

 

[JerryKlmns]

Hi Rich. Glad to be of help.