| re: Two Table Loop - Assigning Names to Records
OK, I'll give this try...
Assuming AnalystName exists in DataTable and AnalystTable -
Use this query to populate DataTable for three of the analysts:
UPDATE DataTable INNER JOIN AnalystTable ON
[DataTable].[ReportId]=[AnalystTable].[ReportId] SET DataTable.AnalystName =
[AnalystTable].[Analystname] WHERE (([AnalystTable].[Analystname] In
('AnalystH','AnalystCG','AnalystMEI')));
(Paste the above query into the SQL View of a new query in order to create
the query.)
Then use this code to update DataTable for the 'D' reports:
(I tested this in the Immediate Window).
' need reference to Microsoft DAO 3.6 Object Library, place it above any ADO
references.
Public Function UpdateDanalysts() As Boolean
Dim rst As Recordset
Dim strSQL As String
Dim i As Integer
Dim strArray(3) As String ' for four analysts
strArray(0) = "Analyst_1"
strArray(1) = "Analyst_2"
strArray(2) = "Analyst_3"
strArray(3) = "Analyst_4"
strSQL = "select AnalystName from DataTable where ReportId = 'D' and
AnalystName is NULL"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not rst.EOF Then
rst.MoveFirst
i = 1
While Not rst.EOF
rst.Edit
rst(0) = strArray(i Mod 4)
rst.Update
i = i + 1
rst.MoveNext
Wend
End If
rst.Close
UpdateDanalysts = True
End Function
Structure of AnalystTable with autonumber id.
id ReportId AnalystName
1 H AnalystH
2 CG AnalystCG
3 MEI AnalystMEI
4 D AnalystD1
5 D AnalystD2
6 D AnalystD3
7 D AnalystD4
Structure of DataTable with autonumber id.
id ReportId AnalystName
1 H AnalystH
2 CG AnalystCG
3 MEI AnalystMEI
4 D Analyst_2
5 D Analyst_3
6 D Analyst_4
7 D Analyst_1
8 D Analyst_2
9 D Analyst_3
10 D Analyst_4
11 D Analyst_1
12 D Analyst_2
13 H AnalystH
14 CG AnalystCG
15 MEI AnalystMEI
Hope this gives you some ideas to get started. The analyst names are
hardcoded into the arrays which is not good in the longterm.
If those names might change then better to load the names from AnalystTable
into the array, look at use of Redim and ubound
to build and navigate such an array.
Linda
"JC" <clemma@excite.com> wrote in message
news:36a88118.0401201245.2b19a32c@posting.google.c om...[color=blue]
> Hi,
> I have a database that imports 4 reports to my "data" table, each
> report has its own identifier. Then I have a table with Analysts with
> the report identifier that they are to be assigned to. What I need to
> do is loop the two tables so that an Analyst is assigned to each
> record in the "data" table based on the identifier. For example, I
> have one analyst that needs to be assigned to the "H" report records,
> one analyst that needs to be assigned to the "CG" report records, one
> anaylst that needs to be assigned to the "MEI" report records, and I
> have 4 analysts that need to be assigned to the "D" report records.
> The 4 analysts for the "D" report records need to be evenly
> distributed. So basically I need the loop to go through the "data"
> table and figure out what the identifier is for each record and assign
> the analyst from the "Analyst" table to the record. Obviously for the
> "D" report items it would have to loop through the "Analyst" table to
> alternate through the 4 analysts assigned to that report. Let me know
> if there are any questions.[/color]
| 
