Execute stored procedure and save values in a table

[talha]

Hello, I have around 1000 values that I'd like to execute against my SQL code and store the resulting data in a table for further analysis.
Here is an example of my SQL code:


INSERT INTO Summary_Final ( PD_Deadline, stock, sell, [count] )
SELECT x.PD_Deadline, x.stock, x.sell, x.[count]
FROM (

SELECT
-0.15 as PD_Deadline,
a.stock, a.sell, Count(*) AS [count]
FROM (
SELECT Data.Stock, Data.Date, Data.Buy_price*(1+(0.05)) AS Sell
FROM Data ) AS a
WHERE a.[Flag]="Y"
GROUP BY a.stock, a.sell

) as x;


The values of -0.15 and 0.05 in the code serve as adjustable parameters. If I were to execute the code manually, I'd modify these parameters individually for approximately 1000 instances and run the code for each specific set of values.





It would be more efficient to store these parameters in a table, transform the existing code into a function, and then invoke the function within a loop.
As a new bee, I would appreciate detailed guidance on this matter. Thank you in advance.

 

[June7]

Cross-post https://www.accessforums.net/showthread.php?t=89270

 

[arnelgp]

first i made table T1, for your variable PD_Deadline (AA field) and the Multiplier (BB field).
next i created your inner sub-query, query qA cross joining Data and T1 table.
next i created your outer sub-query, Total query, query qX.
finally created an append query, qInsert, using qX.

you put all your variables (and multiplier) to table T1.

 

[talha]

first i made table T1, for your variable PD_Deadline (AA field) and the Multiplier (BB field).
next i created your inner sub-query, query qA cross joining Data and T1 table.
next i created your outer sub-query, Total query, query qX.
finally created an append query, qInsert, using qX.

you put all your variables (and multiplier) to table T1.

How would you execute this requirement?

Data.Buy_price*(1+(0.05)) AS Sel

 

[GPGeorge]

How would you execute this requirement?

Data.Buy_price*(1+(0.05)) AS Sel

As you were told both in this thread and in the other thread, create a table. Arnelgp even went to the extent of creating a sample of that table for you.

The variable amount -- 0.05 in this case -- will be in one field in the table.

 

[arnelgp]

see the BB field of table T1. the first record say 1.05, it is the same as (1 + (0.05))

 

[talha]

Thank you for your help thus far...

I am unable to reproduce the suggestion in my database, so I'm sharing it with you for your examination and input.

Here is a quick summary of my tables:

Data: This table encompasses the main data.

TBL_Combinations: This table comprises the variables that I manually include in my append query.

Summary_Final: I store the data in this table after executing it against the variables stored in TBL_Combinations.

These are the manual steps I follow to achieve the desired output, and I'm seeking automation for this process:

1. Initially, I open the TBL_Combinations and document the variables. For instance, the first line contains PD: -0.15; Buy_1: -0.1; Buy_2: -0.1%; Sell: 0.05.
2. Subsequently, I access the "Query - Append data" and switch to the SQL view.
3. Following that, I modify the variables as indicated in the provided snapshot.

4. After making the adjustments, I execute the query to store the summary in the Summary_Final table.
5. I repeat steps 1 to 4 for the subsequent combinations.

Desired outcome

I want the manual process to be automated.

Thanks again for your assistance

 

[arnelgp]

see if the queries i made are correct and rectify if not.
the queries are numbered according to the sequence they are created.

 

[talha]

it worked.

Thanks again for your assistance I really appreciate it