468,278 Members | 1,565 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,278 developers. It's quick & easy.

create an array and fetch into fields under relevant columns

Hi gurus!

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

Expand|Select|Wrap|Line Numbers
  1. Band     Country
  2. AIR       FR
  3. Bon Jovi  US
  4. Oasis     UK
  5. Blur      UK
  6. Green Day US
  7. Metalica  US
  8.  
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.

Expand|Select|Wrap|Line Numbers
  1. FR        UK        US
  2. AIR       Blur      Bon Jovi
  3.           Oasis     Green Day
  4.                     Metalica
  5.  

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?
Jun 23 '14 #1

✓ answered by 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.

4 857
jimatqsi
1,252 Expert 1GB
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
Jun 23 '14 #2
twinnyfo
3,650 Expert Mod 2GB
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.
Jun 23 '14 #3
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.

Expand|Select|Wrap|Line Numbers
  1. FR        UK        US 
  2.                     Bon Jovi 
  3.           Oasis     Green Day 
  4. AIR       Blur      Metalica 
  5.  
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
Jun 24 '14 #4
I have my pseudo code here:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4.  
  5. Dim sqlStr As String
  6. sqlStr1 = ""
  7. sqlStr1 = sqlStr1 & "SELECT Band, Country"
  8. sqlStr1 = sqlStr1 & "FROM   [My-Query]"             ' refer to the query for extracting what we need 
  9. sqlStr1 = sqlStr1 & "WHERE     ( Case"
  10. sqlStr1 = sqlStr1 & "                    =" & Me.Case.Value    ' condition: Case = Case combo box
  11. sqlStr1 = sqlStr1 & "          );"
  12.  
  13. ' another query within this function creates another table [tblTempBandCountry_Case_N], with columns (MEMO type) according to the countries they have
  14.  
  15. Dim countryBand
  16.  
  17. Set db = CurrentDb
  18. Set rs1 = db.OpenRecordset(sqlStr1) 
  19. If Not (rs1.EOF And rs1.BOF) Then
  20.     rs1.MoveFirst 'Unnecessary in this case, but still a good habit
  21.     ' LOOPING STARTS
  22.     Do Until rs1.EOF = True
  23.             'DO SOMETHING HERE TO CREATE ARRAYS LIKE countryBandFR, countryBandUK, countryBandUS......
  24.             'THEN countryBandFR="AIR"; countryBandUK="Blur,vbCrLf,Oasis"; countryBandUS="Bon Jovi,vbCrLf,Green Day,vbCrLf, Metalica" 
  25.             'INSERT the countryBand arrays to the temp table  [tblTempBandCountry_Case_N] under relevant columns
  26.         rs1.MoveNext
  27.     Loop
  28.     ' LOOPING ENDS    
  29. Else
  30.     MsgBox "There are no records in the recordset."
  31. End If
  32. End Sub
  33.  
Jun 24 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Raptor | last post: by
12 posts views Thread by =?Utf-8?B?RGFuaWVs?= | last post: by
reply views Thread by davaahuu | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
1 post views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.