create an array and fetch into fields under relevant columns

[manfred]

Hi gurus!

I have a query result set [band] like the following below:

Band     Country
AIR       FR
Bon Jovi  US
Oasis     UK
Blur      UK
Green Day US
Metalica  US
 

My final view I want is like the one as below, I have already used VBA to create a temp table [tmpBand] with 3 columns, namely FR, UK & US.

FR        UK        US
AIR       Blur      Bon Jovi
          Oasis     Green Day
                    Metalica
 

What I want is to create an array for each country by looping the record rows. For example, the arrray for US would be Bon Jovi, Green Day, Metalica (linebreak instead of having commas). Then fetch the relevant arrays under the correct country columns.

I tried to insert into [tmpBand] but seems not successful. What I should do?

[jimatqsi]

manfred, looks to me like you want to make a crosstab query. In the query designer, right-click in the top half and choose the query type. Band will be the value, Country will be the column heading, but you'll need one more data item to be the row heading. Maybe you've got something else or you could add a counter to your recordset.

Take a look at crosstab queries. They do this very simply.

Jim

[twinnyfo]

manfred,

Also, are you limiting your records to only those three countries? Any time you expand the number of results, you would be recreating your code.

A cross tab query might work, as Jim implies, but it looks liek you are simply establishing a list of countries, and a list of bands from those countries?

You might have to set up your arrays using multiple recordsets, the first one creating a list of countries, which cycles through each country. The secod record set pulls a list of all those bands assigned to each country.

Then you create your arrays based on those results.

What are you planning to use your arrays for? There might be better ways to get this data depending on how you want to use it.

[manfred]

Hi both!

Thanks in advance for yourhelp! Countries and bands will not be limited to these few but won't be more than 15 I suppose and it depends what we inserted. Crosstab view is what I want and I already had a query to make it.

However, for the crosstab I had made with the queries, we have some "layout issues" so I have to look into VBA for making it. For the crosstab view I had, it will happen like the following view. My superior doesn't want to have "blank fields" and prefer those bands aligned to the top and stick to the country. I tried to modify my query and also settings in report but it doesn't appear to what my superior prefers.

FR        UK        US 
                    Bon Jovi 
          Oasis     Green Day 
AIR       Blur      Metalica 
 

Seems the only solution is to use array. For the above view, it will be like one row of record. So for UK, the array will be [Oasis,vbCrLf,Blur] and insert as one field and [Bon Jovi,vbCrLf,Green Day,vbCrLf, Metalica] for US. The temp table created by VBA already had the country fields as MEMO type.

@twinnyfo

[manfred]

I have my pseudo code here:

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim sqlStr As String
sqlStr1 = ""
sqlStr1 = sqlStr1 & "SELECT Band, Country"
sqlStr1 = sqlStr1 & "FROM   [My-Query]"             ' refer to the query for extracting what we need 
sqlStr1 = sqlStr1 & "WHERE     ( Case"
sqlStr1 = sqlStr1 & "                    =" & Me.Case.Value    ' condition: Case = Case combo box
sqlStr1 = sqlStr1 & "          );"
 
' another query within this function creates another table [tblTempBandCountry_Case_N], with columns (MEMO type) according to the countries they have
 
Dim countryBand
 
Set db = CurrentDb
Set rs1 = db.OpenRecordset(sqlStr1) 
If Not (rs1.EOF And rs1.BOF) Then
    rs1.MoveFirst 'Unnecessary in this case, but still a good habit
    ' LOOPING STARTS
    Do Until rs1.EOF = True
            'DO SOMETHING HERE TO CREATE ARRAYS LIKE countryBandFR, countryBandUK, countryBandUS......
            'THEN countryBandFR="AIR"; countryBandUK="Blur,vbCrLf,Oasis"; countryBandUS="Bon Jovi,vbCrLf,Green Day,vbCrLf, Metalica" 
            'INSERT the countryBand arrays to the temp table  [tblTempBandCountry_Case_N] under relevant columns
        rs1.MoveNext
    Loop
    ' LOOPING ENDS    
Else
    MsgBox "There are no records in the recordset."
End If
End Sub