Loop through all records and do a string array update

[dsajones]

Hi All,

Still very new to MS Access but learning fast!!

I have two tables. The following is a simplified example structure for each.

Table_A

Field_1; AutoNumber index
Field_2; String of codes e.g. MAR;2CH;HOM;;;;;;;
Field_3; String to hold expanded code descriptions.

Table_B

Field_1; Code - Will match the individual codes in Table_A.Field_2
Field_2; Description of code.

So what I want to do is:

visit every record in Table_A
Read the string of codes and seperate them out
Read the description for each code and build a string of the descriptions concatenated together.
Save that string back in to Field_3 of Table_A

So in the example above the following descriptions might be what those codes mean:

MAR - Married
2CH - 2 Children
HOM - Homeowner

So I build that in to a string:
"Married. 2 Children. Homeowner"
Then save that complete string back in to Field_3 in Table_A

I can express this OK in Pseudo Code but trying to get the correct VBA syntax for using the split function, then joining the tables and updating Table_A in the middle of a loop is defeating me. Any help would be much appreciated.

Cheers
David

 

[Mr. B]

David,

Although what you are wanting to do can be done, I wanted to at least raise a question about the normalization of your database. I really think that that you need to create multiple records to capture this information correctly. Placing multiple values in a single field is not a good idea, especialy when you are then needing to link those values to existing records for getting other information like your long descriptions values.

I just think you should really think about this first and then we can talk about how to manipulate the data so that I works for your database.

 

[MarkK]

Is this a coding exercise? I would never embed multiple values in a single field like that because, as you know, you need to expend so much processsing power to extract them and put them to good use.
A table in a database typically respresents a single type of object, and each record in a table represents a single (atomic, discrete) instance of that type.
So maybe you have a customer table ...

tCustomer
CustomerID (Primary Key)
Customer
...

... and then, apart from any customer you have a list of attributes. See how the attributes are things? Even if you have no customers, you still have a list of attributes, so those require a table ...

tAttribute
AttributeID (PK)
Code (your 3 char label like MAR, 2CH)
Description

...and each single attribute gets its own record in that table.
Finally, where the rubber hits the road, a table that relates a customer to an attribute.

tCustomerAttribute
CustomerAttributeID (PK)
CustomerID (Foreign Key)
AttributeID (FK)

See how each record in this table connects a single attribute to a single customer?
Now, the attributes that belong to a customer can be returned in recordset using blisteringly fast SQL like ...

SELECT AttributeID
FROM tCustomerAttribute
WHERE CustomerID = <some customer id>

This is some of the thinking that Me B. alludes to. Does this look helpful, or do you just want the code you were asking about?

 

[dsajones]

Hi Mr B and Lagbolt,

Thanks for responding so quickly. Yes, I understand exactly where you're both coming from regarding normalisation. And I normally wouldn't design a database that looks like this. So a bit of additional background will help. This data actually comes in to us on a spreadsheet. One of several spreadsheets that we receive from a variety of different agencies, some come in on a daily basis others come in as and when. Some have a single sheet with additional info embedded in cells in that sheet and others have multiple sheets that look a bit like a normalised database.

The structures of all the spreadsheets differ but they all contain tenant names, addresses and additional information. In some of them, this additional information already comes to us as a string of normal words e.g. "Elderly and disabled" but in this one particular spreadsheet they use these strings of codes. Incidentally, the examples I've used are much simpler than the real things. There are over 50 codes and some of them are completely incomprehensible e.g. EIADP, HYGGE, 1RSUA etc. To people in this particular agency I'm sure they can look at these and instantly know what they mean! Obviously, given that there is a fixed number of ; in the incoming cell, this data has probably started life in a database where it may well be properly normalised. But it's much easier for me to just turn these codes in to a string of proper words and store the result in a single field than to get the agency's IT department to change how they extract and present the data to us.

All I'm trying to achieve is a quick reference for our call handlers. They get a call from a tenant, punch in the post code, select the correct address and they can quickly see relevant information about the tenant or the property. There's no further requirement to store this coded information or do any further database manipulation with it.

So yes, it's really just a coding exercise I'm doing rather than good database design.

Cheers
David

 

[MarkK]

OK, well here's what I recommend for coding a solution: write really short procedures. For instance you have this pseudo-code: "visit every record in tableA" There should be one routine that only does that and it should call another routine. This way you get each routine working in very small and independently testable steps.

Sub VisitEveryTableARecord
  dim rst as dao.recordset
  set rst = currentdb.openrecordset("TableA")
  with rst
    do while not .eof
      .edit
      !field3 = GetExpandedDefinitions(!field2)
      .update
      .movenext
    loop
    .close
  end with
  beep 
  msgbox "done", vbinformation
end sub

There's a start. One recordset, one loop, simple.
So you need a ...

Function GetExpandedDefinitions(delimitedcodes as string) as string
[COLOR="Green"]  'split the delimited codes into an array
  'traverse that array
      'call a routine to find one Description of Code
      'append it to a string
  'return the value to the calling routine
[/COLOR]End Function

Keep every step dead simple, only use one loop per procedure, and if you get stuck post specific questions.
Hope this helps,
Mark

 

[dsajones]

Hi Mark,

Thanks for the quick response and for the help. I'm in my 50s and haven't done any real coding for about 20 years - and that was good old character based apps and procedural languages. Your code for working through the table is nothing like I was expecting and I hadn't come across anything like that in my Dummies Guide or on-line resources. I was playing with SQL to do it as I've had a bit of experience of that in both Ingres and SQL server but that was using cursors for doing that sort of thing. What you've presented looks much simpler and very elegant! But I don't think I would have stumbled across the syntax with the ! and the . prefixes, so thank you for that.

I'll now have a play with the code for the function. I've tried out the split function and I think I'll be OK with that. Traversing the array is likely to be the thing that trips me up. I was thinking of using a bit of in-line SQL in the middle of the loop to look up the code description rather than calling a separate function or procedure. I can't think there will be anywhere else I would want to use that.

Thanks again for your help.

Little grey cells - let's go!!

Cheers
David

 

[MarkK]

This might be all you need. Some names need to be changed to protect the innocent, but ...

Function GetExpandedDefinitions(delimitedcodes As String) As String
   Dim var
   Dim i As Integer
   Dim tmp As String
   var = Split(delimitedcodes, ";")
   For i = 0 To UBound(var)
      tmp = tmp & ";" & DLookup("Field2", "TableB", "Field1 = '" & var(i) & "'")
   Next
   If tmp <> "" Then tmp = Mid(tmp, 2)
   GetExpandedDefinitions = tmp
End Function

Cheers,

 

[dsajones]

Mark, thanks for that, really appreciate it. I'd pretty much got that except for the DLookup. That's a neat alternative to SQL.

Cheers
David

 

[MarkK]

Yeah, I don't think you can use Access (Jet) SQL to return a value anyway. I think you need to open a recordset, or use that DLookup()
All the best.

 

[dsajones]

Hi Mark,

Fell at the first hurdle The line Dim rst as dao.Recordset returns a Compile Error: User-defined type not defined

Any ideas?

Cheers
David

 

[MarkK]

OK, this is a reference problem. In a code window go to Menu->Tools->References and make sure the Microsoft DAO 3.6 Object Library is checked. That reference is not selected by default in Access 2003.
See if that changes things...

 

[dsajones]

Thanks Mark. Yes, that's fixed it. I'm doing all the development work in Access 2007 but the application will actually run under 2003. Will that be an issue with the reference?

All working perfectly now thanks.

Cheers
David

 

[MarkK]

...but the application will actually run under 2003. Will that be an issue with the reference?

No, I think if you set the reference in the file you're working on, and that file can find the reference on the target machine, then you're fine. But I'd test stuff in Access 2003 before you deploy too.
Cheers,