By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Two Table Loop - Assigning Names to Records

P: n/a
JC
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
OK, I'll give it a try... :-)
Assuming AnalystName exists in DataTable and AnalystTable. (Read to the end
for the table structures).

Here is the text of a query to deal with three analysts. Just paste this
into the SQL view
of a new query to have Access create the query for you.

UPDATE DataTable INNER JOIN AnalystTable ON
[DataTable].[ReportId]=[AnalystTable].[ReportId] SET DataTable.AnalystName =
[AnalystTable].[Analystname] WHERE (([AnalystTable].[Analystname] In
('AnalystH','AnalystCG','AnalystMEI')));

The D report has four analysts. The following code will assign those
analysts. I wrote a function
and tested it in the Immediate Window. Read beneath the code to see the
minimal tables constructed for testing.

' need reference to Microsoft DAO 3.6 Object Library
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
This is the updated DataTable after running both the query and the function.
id in both tables is just an autonumber.
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
Below is the AnalystTable.

id ReportId AnalystName
1 H AnalystH
2 CG AnalystCG
3 MEI AnalystMEI
4 D AnalystD1
5 D AnalystD2
6 D AnalystD3
7 D AnalystD4
Hope this helps you get started.

Linda
"JC" <cl****@excite.com> wrote in message
news:36**************************@posting.google.c om...
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.

Nov 12 '05 #2

P: n/a
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" <cl****@excite.com> wrote in message
news:36**************************@posting.google.c om...
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.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.