Update many to many - one at a time 2

[micang]

Access 2003

Hi All,

I have 2 Tables:

table1:

Field1,Field2
TT,NULL
TT,NULL
YY,NULL
YY,NULL
UP,NULL

Table2:
Info1,Info2,Info3
TT,1,Yes
TT,2,No
TT,3,No
YY,1,Yes
YY,2,No
YY,3,No
YY,4,No
UP,1,Yes
UP,2,Yes
UP,3,No
UP,4,No

What I would like to do is update table1, field2 with the data from table 2, info2 where the first instance of "No" is, then the next record and so on, then once table1 is updated, update the "No" to "Yes" in table2.

In the example above the result will be:

table1:

Field1,Field2
TT,2
TT,3
YY,2
YY,3
UP,3

Table2:
Info1,Info2,Info3
TT,1,Yes
TT,2,Yes
TT,3,Yes
YY,1,Yes
YY,2,Yes
YY,3,Yes
YY,4,No
UP,1,Yes
UP,2,Yes
UP,3,Yes
UP,4,No

I am familiar with doing updates with joins, but in this case I can't use an inner join between field1 and info1 as there are duplicates on either side. And this kind of has to loop or something simalar I think?

So I don't know where to start to achieve this.

As always, any information is appreciated.

Michael

 

[fneily]

Could you post the tables structures - actual names. Field1, Field2, etc. aren't helpful in seeing the big picture.

Since you have a many-to-many relationship, where's the junction table?

I can't tell if your database is designed correctly without your tables - all of them.

As is you'll need to do some coding which would include testing, holding, looping. If the database is designed correctly, most of this may be avoided.

 

[micang]

Hi fneily,

Thank you for your help.

There is one main table, this table has fields MakeCode and ModelCode

(MakeCode filed format is text 2 alpha characters e.g. MA)
(ModelCode filed format is text 3 alpha characters e.g. AZY)
These 2 fields combined are unique in the table (primary key)

Once a month I recive a text file with some data which I have to insert into the above main table.

Thes file contains a few fields 2 of them being: ManufCode and VariantCode
(ManufCode filed format is text 2 alpha characters e.g. TP)
(VariantCode filed format is numeric 3 numeric characters e.g. 001)

I need to append these monthy records and have to re-create new unique codes (MakeCode and ModelCode) from the ManufCode and VariantCode. 2 catches:

1.) I have to use the ManufCode for the MakeCode
2.) I cannot use the VariantCode as the ModelCode, therefore I need to create a new 3 alpha character code, but it can only begin with either I or O and and ofcourse make sure it's not already in the main table (no duplicates).

So I went about creating avaliable future combinations in a separate table, then updating the ones already used, then wanted to link on the 2 fields (MakeCode and ManufCode) and use the next avaliable ModelCode.

Apologies that this is a bit confusing,am finding it a bit difficult to explain it properly.

Tahnsk for your effort.

Michael

 

[Golom]

Fneily is correct about your table design ... at least from what you've shown. Table 1 has duplicate records and (again from what appears here) no primary key. For that reason there's no way to control which of the "TT,NULL" records gets updated first (for example).

You would need a combination of SQL and code

Dim db  As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim SQL As String

Set db = CurrentDb()
Set rs1 = db.Openrecordset("Select Field1, Field2 " & _
                           "From Table1 " & _
                           "Where Field2 IS NULL ")

Do Until rs1.EOF
   SQL = "Select Info1, Info2, Info3 From Table2 " & _
         "Where Info1 = '" & rs1![Field1] & "' AND " & _
         "      Info3 = 'No'"
   Set rs2 = db.Openrecordset ( SQL, dbOpenDynaset )
   
   If Not rs2.EOF Then
      [COLOR=black cyan]' Update Table1[/color]
      rs1.Edit
      rs1![Field2] = rs2![Info2]
      rs1.Update

      [COLOR=black cyan]' Update Table2[/color]
      rs2.Edit
      rs2![Info3] = "Yes"
      rs2.Update
   End If

   rs1.MoveNext
Loop

 

[micang]

Thanks Golom,

I agree that the design is the real issue here.

Thanks for the code, I will give it a try to see if in the interim it will solve the issue.

Michael

 

[micang]

Thanks to both of you for your input.

Golom, the code you posted does indeed to the exact job required.

Appreciate your time.
Michael