Find string match from one table and pull in another attribute value

[Pedrowedro]

Hi,
Please could you help me out with this problem...

I have the following set up:

Table1
======
Autonumber Field
MultipleVAlue (this Field holds info such as "E012; G344; T66;")
Description Field (This is currently empty)

Table2
======
Value (this Field holds information such as "E012;")
Description Field(i.e. "Over head lighting")


I am trying to Find a match for each value from MultipleValue in Table1 against Value in Table2.

Where a match occurs copy the Description from Table2 and concatinate it to the value of Description Field in Table1.

This needs to be done for each record of Table2.

Your help will be very appreciated...

Thanks

Pedro

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pedrowedro]

Thank you for your help PHV.

But the problem I am coming up against is that table 1's multiple value field contains a string value composed of a concatination of several values.

What I am trying to do is search each value in table 1's multiplevalue field in turn against table two and when a match is found place the matched description in table 1's description field. When another match for the same record in table 1 is found the next description value is concatinated to table 1's description field.

any help would be greatly appreciated

Thanks

 

[MajP]

I would reread PHV's link. Look at the section describing "First Normal Form". Your design clearly violates that property. You can do what you are asking, but you are far better off redesigning your table structure. If you design your tables correctly this problem is trivial. You would need to describe your model a little more. What is the information in table 2 describing?

 

[Pedrowedro]

Hi MajP,
Table 2 contains approx 7000 building codes and there description for the construction industry. This is used as a look up for pulling the description in other areas of the system.

I recieve a file that I import into the database that is already at third normal form.

The problem is that the file recieved is a flat CSV file. With muiltiple entries in the same field.

I presume the way to tackle this would be to select each record and in turn break up the string into several chunks that can then be inserted into an SQL statement to find its appropriate match.

I just need to work out how to work through each record in table 1 and capture the value data in a string variable where I can then run some itterative code against it.

Thanks again

 

[MajP]

This code will loop through and do what I think you are asking

Table2
Value Description
E012 Lighting
G344 Wire
H567 Plumbing
L54 Wallboard

Table1
multivalue multidescription
E012; G344
G344, H567
L54; H567


after loop

Table1
multivalue multidescription
E012; G344 lighting; wire
G344, H567 wire; plumbing
L54; H567 wallboard; plumbing

 Public Sub CreateMultiDescription()
  Dim rs1 As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim arrayValues() As String
  Dim desc As String
  Dim intCounter As Integer
  Set rs1 = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
  Set rs2 = CurrentDb.OpenRecordset("table2", dbOpenDynaset)
  
  Do While Not rs1.EOF
    desc = rs1.Fields("multiValue")
    arrayValues = Split(desc, ";")
    For intCounter = 0 To UBound(arrayValues)
     rs2.FindFirst ("Value = '" & arrayValues(intCounter) & "'")
     rs1.Edit
     rs1.Fields("multiDescription") = rs1.Fields("multiDescription") & rs2.Fields("description") & "; "
     rs1.Update
    Next intCounter
    rs1.MoveNext
 Loop

Summary
1) set up to recordsets based on table1 and table2
2)loop through table 2
3) read the multivalue field and split the values into an array
4) loop through the values in the array
5) find the description in table2
6) put this value in table 1 description field
7) go to the next value in the array
8) repeat steps 5 and 6
9) go to the next record in table 1 and repeat 3 through 8

I would work on getting the data in the correct format on the import. This code works for me, but not the approach I would take.

 

[MajP]

step 2 should say "loop through table 1" not table 2

 

[Pedrowedro]

Thank you very much for your help... works nicely.
Hopefully in time I could convince the powers that be that a re-design would be a better option...but until then...

Thanks again