473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Two Table Loop - Assigning Names to Records

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
2 2676
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
3
by: Phil Rutter | last post by:
Hello All, I have about 700 word documents that have 2 tables one is static 4 colums x 5 rows the other is 5 colums x rows ranging from 2 to 100 what i wolud like to do is open the word doc....
7
by: Amy | last post by:
I'm trying to add an autoincrementing id to a table based on an existing field Name, but Name has duplicated records. How can I do that in ACCESS? Thanks. Amy
1
by: Randy | last post by:
Access= 2002 I'm NOT a Programmer, but I have used VB in the past to do some things ( Spaghetti Code King) so I have some understanding of Coding I need to replace a text field (teacher) in...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
9
by: john | last post by:
I have imported an Excel spreadsheet in Access. This table has 150 fields. The first field is 'user name', and all the other fields represent application names of which the value can be True of...
11
by: Tim Hunter | last post by:
Hi I am using WinXP and Access 2003 Is it possible to store the field names of a table in an array and then loop through the array and update the table using the field names stored in the array? I...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
3
by: DWolff | last post by:
My application is to re-assign leads to different groups of salespeople by sequentially assigning them to each salesperson. I've got an Access 2000 front end to an MS-SQL database. Currently, I...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.