adding linked field numbers auto... 1

[mjonson]

Hi,

have managed to import Lotus 'Views' into access
I have a table[1] with a list dates,tasks completed, user etc and a table[2] with a list of all the tasks available

Have added a key column to the tasks available table
now I want to add the keys to table[1] automatically where the task text in table[1] matches the text in table[2]
make sense?

example

table[1]
date user task
---------------------------------
01/01/04 MG First Aid
01/01/04 GH First Aid
02/01/04 BN First Aid
02/01/04 MG Safety
03/01/04 BN Safety

table[2]
taskid task
---------------------
1 First Aid
2 Safety

updated table[1] should look like

table[1]
date user task taskid
------------------------------------------
01/01/04 MG First Aid 1
01/01/04 GH First Aid 1
02/01/04 BN First Aid 1
02/01/04 MG Safety 2
03/01/04 BN Safety 2

so i can delete the field task and lookup in table [2]
i have 3000 records so would like to do this automatically
ne ideas?
[bloody notes]

 

[RickSpr]

First add the taskid column to Table1 in Design View. Then run the following command in the Immediate Window:
DoCmd.RunSQL "UPDATE Table1 INNER JOIN Table2 ON Table1.task = Table2.task " _
& "SET Table1.taskid = Table2.taskid"


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[mjonson]

thanks Rick heres a * - one Q to run this code
i made a form with a button - in your answer you say 'Immediate Window', is there a way to run code
on a DB besides the approach above

 

[lespaul]

sure, you put this:

DoCmd.RunSQL "UPDATE Table1 INNER JOIN Table2 ON Table1.task = Table2.task SET Table1.taskid = Table2.taskid"

in the onClick event of your button!



Leslie

 

[mjonson]

...i meant to say 'i made a form with a button to run the code' but thought maybe you could run code from some sort of 'code run' window (that is not from within a form)

 

[RickSpr]

Well, the Immediate Window is a 'code run' window that is not within a form, isn't it? Maybe you should explain why the Immediate Window is unsatisfactory.

It's also unclear whether you're trying to execute this query just once, by hand, or whether you want it to be executed automatically as part of a larger process that involves importing Lotus views.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein