Convert a particular table into a multidimensional array?

[Access9001]

Say I have a table like so (Branch, Area column names):

California, Los Angeles
California, San Francisco
California, Fresno
Colorado, Denver
New York, New York City
New York, Buffalo
Pennsylvania, Philadelphia

And I want to basically convert this table into an array, where something like:

Arr(0,0) -> California
Arr(0,1) -> Los Angeles
Arr(0,2) -> San Francisco
Arr(0,3) -> Fresno
Arr(1,0) -> Colorado
Arr(1,1) -> Denver
Arr(2,0) -> New York
Arr(2,1) -> New York City
Arr(2,2) -> Buffalo
Arr(3,0) -> Pennsylvania
Arr(3,1) -> Philadelphia

Or something to this effect, but dynamic. And with the ability to properly loop through each Branch in VBA code for each Area.

Is this doable? Thank you in advance!

 

[jdraw]

You can do the looping using a recordset based on a table. Why the array?
Just curious

 

[Access9001]

Because later on I need to be able to quickly create autofilter criteria for a worksheet where I am exporting different rows to different sheets.

Say I have a file, Source.xls, that has the following rows in Col A:

Fresno
Los Angeles
New York City

I first create an array of which Areas I am looking at, here (Fresno, Los Angeles, New York City) from the source file. Then, I need to be able to create an array with the relevant Branches (California, New York).

Finally, I create this sort of loop:

For each Branch
For all Areas in this particular Branch
Create custom filter that shows just the relevant Areas
Save this as Branch name.xls
Next
Next

 

[G37Sam]

A better way to do this would be to create two tables, one for the cities, another for the branches then do a little querying/DAO

 

[Access9001]

I mean I already have a query that links it all together (Branch and its corresponding Area), but how would I accomplish what I want, here?

 

[ChrisO]

Public Type Branch
    Branch  As Long
    Areas() As String
End Type

Public Type AllBranches
    Branches() As Branch
End Type


Sub TestIt()
    Dim BranchIndex As Long
    Dim AreaIndex   As Long
    Dim CurrBranch  As String
    Dim BranchTest  As AllBranches

    BranchIndex = -1
    AreaIndex = 0
    
    With CurrentDb.OpenRecordset(" SELECT Branch, Area" & _
                                 " FROM tblBranchAreas" & _
                                 " ORDER BY Branch", 2)
 
        Do Until .EOF
            If !Branch <> CurrBranch Then
                BranchIndex = BranchIndex + 1
                AreaIndex = 0
                CurrBranch = !Branch
                ReDim Preserve BranchTest.Branches(BranchIndex)
                ReDim Preserve BranchTest.Branches(BranchIndex).Areas(AreaIndex)
                
                BranchTest.Branches(BranchIndex).Branch = BranchIndex
                BranchTest.Branches(BranchIndex).Areas(AreaIndex) = !Branch
            End If
        
            AreaIndex = AreaIndex + 1
            ReDim Preserve BranchTest.Branches(BranchIndex).Areas(AreaIndex)
            BranchTest.Branches(BranchIndex).Areas(AreaIndex) = !Area
        
            .MoveNext
        Loop
    End With
    
    For BranchIndex = 0 To UBound(BranchTest.Branches)
        For AreaIndex = 0 To UBound(BranchTest.Branches(BranchIndex).Areas)
            Debug.Print BranchIndex & ", " & AreaIndex & "  " & BranchTest.Branches(BranchIndex).Areas(AreaIndex)
        Next AreaIndex
    Next BranchIndex

End Sub

 

[Galaxiom]

Just a random thought that I have not pursued.

Could this be done by making a recordset on a CrossTab query then using the GetRows method to make the array?

 

[gemma-the-husky]

if you do have a table like you describe, the basic problem is that it is not normalised.

I would have thought you would be better advised trying to normalise the structure than manipulate arrays.

 

[Access9001]

Can you explain what is meant by normalizing?

The query I am using is ultimately built on tables that use, say, Branch and Area id's

ChrisO : Apologies but it seems to not work. I'll try to study the code and see what it's doing, though.

 

[Access9001]

This is all I want to do:

I look at a file. I notice it has a list of maybe 3-4 branches. I then compare it to my full list of branches and areas, and get a unique list of all the areas associated with my branches into a new array.

Then, I loop through each Area in my array and filter for all its associated branches. Then I export an Area file. Move to next Area in my array etc.

Otherwise I could just loop through every Branch and Area in my master table but then I'd be filtering/exporting for things for which there is no data present in my file.

 

[ChrisO]

Access9001.

>>Apologies but it seems to not work.<<
That is not much to go on.

If you ran the code something must have happened.
What happened when you ran the code?
Did it raise an error?
Did you change the table name and field names to suite your conditions?
Did you look in the immediate window for the result?

Chris.

 

[gemma-the-husky]

Can you explain what is meant by normalizing?

The query I am using is ultimately built on tables that use, say, Branch and Area id's

ChrisO : Apologies but it seems to not work. I'll try to study the code and see what it's doing, though.

I may be misunderstanding the structure of your existing data.

Anyway, ideally you would have a separate table for the states, as otherwise you might get inconsistent variants of the state name in the state table. You may even want a separate table for the cities for the same reason, but maybe not.

I think the underlying point is why you think you need an array. Access is built to manipulate the tables, and it's hard to see how transforming a table into an array conveys any benefit. What are you trying to do with the array that you don;t think you can do with the table?

 

[Access9001]

ChrisO: That's probably why -- I'll give it another shot.

Gamma: Because I am interacting between my internal tables and an Excel source file. I need to store the unique row values somehow and then use this to compare against the table and, from this, generate criteria to filter the Excel by. I can't really do that with tables alone unless I am pretty much storing the Excel values in a new Table, which seems like a waste of resources when I could just put it into an array.