Assigning values via loop

Any assistance would be appreciated.

I have to assign values to a table based on another table.

Ex: table 1 has the following fields: ID(autonumber) Item Assignee
(Assignee is not populated)

table 2 (Assignees) has the following fields: ALName AFName


Using the sorted ID field to keep the order straight, I need to loop
through the list of names in table 2 and assign them one by one to a
line in table 1.

Also, more items could be added to table 1 so when it runs I think it
needs to check what name it left off on and continue on down the list
when I update it.

Is there anyone who can shed some light on this for me?


What I should end up with is: 1 Red Fringe John Smith
2 Orange Beets April Wine

etc..

Thanks in advance..

re: Assigning values via loop

I think your problem is conducive of bad design. If you are determining what
table 2 name goes into what table1 Assignee field just by sorting, you're
going to run into problems. What happens when a new entry changes the sort?

Mike Storr
www.veraccess.com


"Smythe32" <Smythe32@aol.com> wrote in message
news:7f494502.0403051233.457f0ad6@posting.google.c om...[color=blue]
> Any assistance would be appreciated.
>
> I have to assign values to a table based on another table.
>
> Ex: table 1 has the following fields: ID(autonumber) Item Assignee
> (Assignee is not populated)
>
> table 2 (Assignees) has the following fields: ALName AFName
>
>
> Using the sorted ID field to keep the order straight, I need to loop
> through the list of names in table 2 and assign them one by one to a
> line in table 1.
>
> Also, more items could be added to table 1 so when it runs I think it
> needs to check what name it left off on and continue on down the list
> when I update it.
>
> Is there anyone who can shed some light on this for me?
>
>
> What I should end up with is: 1 Red Fringe John Smith
> 2 Orange Beets April Wine
>
> etc..
>
> Thanks in advance..[/color]

re: Assigning values via loop

Smythe,
Given: Table 1 = ASSIGNEE_ITEM_TBL and the columns you have and Table 2 =
ASSIGNEE_TBL then what you have is a standard one-to-many relation between
ASSIGNEE_ITEM_TBL and ASSIGNEE_TBL. And an SQL INSERT statement should be
enough to add all your assignees to ASSIGNEE_ITEM_TBL with nulls for ITEM.
This shouldn't require code to write & run.
Also, it is a generally accepted rule that writing code that depends on a
sort is a bad idea. Relational databases should be assumed to be unsorted
and the sorts are unstable therefore code needs to be written so as to avoid
relying on a particular sort order.
All this and I am really foggy on what your database is for. I can infer
from your tables that it is something to do with item assignments, but more
info would help us help you.
"Smythe32" <Smythe32@aol.com> wrote in message
news:7f494502.0403051233.457f0ad6@posting.google.c om...[color=blue]
> Any assistance would be appreciated.
>
> I have to assign values to a table based on another table.
>
> Ex: table 1 has the following fields: ID(autonumber) Item Assignee
> (Assignee is not populated)
>
> table 2 (Assignees) has the following fields: ALName AFName
>
>
> Using the sorted ID field to keep the order straight, I need to loop
> through the list of names in table 2 and assign them one by one to a
> line in table 1.
>
> Also, more items could be added to table 1 so when it runs I think it
> needs to check what name it left off on and continue on down the list
> when I update it.
>
> Is there anyone who can shed some light on this for me?
>
>
> What I should end up with is: 1 Red Fringe John Smith
> 2 Orange Beets April Wine
>
> etc..
>
> Thanks in advance..[/color]