Copying Expressions in a Query to a Table

[papadega3000]

Hello,

I have yet another question in regards to Access. I was tasked to figured out how to graph data historically by day that is generated by a SQL query of expressions. I currently coded a SQL query to formulate the numbers needed, but have no knowledge of how to graph it historically.

I was able to use the query I made to formulate the expressions to put them in a pivotchart and formulate graphs that way this just shows for one day or at the instance you run the query.

I want to devise a way to run the same query and gernerate the number and copying them into a table with a timestamp then just query the table and build a pivot chart in sorted by timestamp.

The SQL code I have generally looks like this :
(Please note this has undergone some modifications but for the most part is the actual code)

SELECT Run.Test_Case, 
 
Sum(IIf(Not IsNull([Attempted_Actual])<= Date(),[Points],0))/Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0))*100 AS ActualAttempted,
Sum(IIf([Completed_Actual]<= Date(),[Points],0))/Sum(IIf(Not IsNull([Completed_Actual]),[Points],0))*100 AS ActualCompleted,
Sum(IIf([Verified_Actual]<=Date(),[Points],0))/Sum(IIf(Not IsNull([Verified_Actual]),[Points],0))*100 AS ActualClosed,

Sum(IIf([Attempted_Planned]<= Date(),[Points],0))/(Sum(Task.Points))*100 AS PlannedAttempted,
Sum(IIf([Completed_Planned] <=Date(),[Points],0))/(Sum(Task.Points))*100 AS PlannedCompleted,
Sum(IIf([Verified_Planned]<= Date(),[Points],0))/(Sum(Task.Points))*100 AS PlannedClosed
FROM Run INNER JOIN Task ON Run.Run=Task.Group
GROUP BY Run.Test_Case
HAVING (((Run.Test_Case)=IIf(IsNull([Forms]![Status]![ComboStatusTestCase]),[Test_Case],[Forms]![Status]![ComboStatusTestCase])));

I would like to insert these expression values into a table. And I am not sure how to accomplish this.

My first several attempts where to use a INSERT INTO tablenameX SELECT ..."(sql statement from above)"

However, it will not allow the copy to happen. Is there a restriction on copying expressions???

Thanks for the help in advance.

 

[papadega3000]

Hello,

Thanks for your advice. I have not done the querydef in this section of code but I have used code like that in similar sections.

Essentially it will look like this:

Sub X
Dim strSQL1 As String
Dim querydef As QueryDef

strSQL1 = " Sum SQL Statement "
querydef = strSQL

DoCmd.Save ...

DoCmd. RunQuery.Name of Append Query

End Sub


I have more exact code but I am away from the project. I will see if I can come up with a more detailed answer and get back to you.


Thanks for all the help