Repeat query with different values, values derived from a table

[Guest]

Sorry for posting this more than once, but I had it in queries, and I think
it might be more appropriately in programming now...

I would like to write some sort of code to run the following query
multiple times, replacing the Where information inside the quotes ("Name")
with a
different name each time. The info in the quotes would be gained by just
going down the "Animal Name" column of the "Animal ID Information" table.
INSERT INTO [Enrichment Assignments] ( [Animal Name] )
SELECT [Animal ID information].[Animal Name]
FROM Query4, [Animal ID information]
WHERE ((([Animal ID information].[Animal Name])="Name"));
Could you help me with that? It seems like it ought to be doable. The names
are already in a convenient list, it seems like there ought to be a way to
tell the query to run until it gets to the end of the list.
If I do it the normal way without the "where" clause, I don't get the
results I want (it has to do with random number generation, grrr). Thanks for
any help!

 

[Guest]

Clddleopard,

Maybe the following code is usefull for u?

Sub Run_animals
dim myAnimals as recordset
dim MySQL as string
dim MyAnimalName as string

'First open a recordset with all animal names. Use the DISTINCT to only
retrieve unique names (each name will only appear once in the recordset)

set myAnimals = currentdb.openrecordset ("SELECT DISTINCT [Animal ID
information].[Animal Name] FROM Animal ID information]")

'Now loop throught the recordset and execute the query
while not myAnimals.eof
myAnimalName = myAnimals.fields("Animal Name")
mySQL = "INSERT INTO [Enrichment Assignments] ( [Animal Name] )
SELECT [Animal ID information].[Animal Name]
FROM Query4, [Animal ID information]
WHERE ((([Animal ID information].[Animal Name])= '" & myAnimalName & "'));"
'Notice the additional ' in the Where clause. Assumed that the animal names
are text

'Execute the SQL statement
docmd.runSQL mySQL

'Move to the next record
myAnimals.movenext
wend


end sub

 

[Guest]

You are a genius! Thank you, thank you, thank you!!!!!