Use a Query to generate a list of variables to plug into another query

[Nelz]

I have a table that's basically a spreadsheet of a year's worth of stock closing prices on 100 stocks. I have a macro that runs a series of about 11 querys to compare any two symblos to each other. What I need to figure out is if there is a way to query the master table to create a list of all 100 symbols, then plug them into the variable slot one pair at a time until the entire list has been run in every combination (1 to 1 ...1 to 2...1 to 3..etc). The last query in my macro creates a one line result, and what I wanted to do was make that an append query that would append that one line to a table...one line at a time for each pair. After all were run...that final table could be querey'd to see if any two pairs produced the desired result by using one final query with a bunch of IIf statements.

The way I have it now is that you have to plug in Stock1 and Stock2 on a form, and one of the first querys reads the symbols from there after you click a button to start the macro.

What the client wants is to hook the master table up to a constantly updating excel spreadsheet and just keep running every combination of the 100 stocks over and over again. Can this be done?

 

[hermanlaksko]

Hi Nelz

Tough one ;-) not sure if this will help you at all!
But first I would chanmge that macro into a module to get get an overview of what is going on. In my book macros are only for autoexec - but here I could be wrong.

In regard to the excel Q:
Yes it can be done, but from you Q I am notsure if you want to read from Excel into Access or vise versa.
If you want to read or write to/from Excel "over and over" I would use a timer in events to do this
Hope this little info will move you in the right direction.