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.

 

[Pat Hartman]

Save the SQL string as a querydef if you have not already done so. Then create a new select query that selects all the columns from the totals query. Change the querytype to make table or append if you want to keep adding rows to an existing table.

 

[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

 

[Pat Hartman]

Unless your SQL is dynamic, you should always be using querydefs (or SQL strings embedded in the RecordSource for forms/reports or RowSource for listboxes and combos) rather than building SQL strings in VBA. When a querydef is saved (or an object with an embedded SQL string), Jet calculates an execution plan and saves it. When you run dynamic SQL, that calculation step needs to be done EVERY time you run the query. This adds unnecessary overhead and contributes substantially to database bloat.

If you look in MSysObjects, you will see queries that start with an ~, they are the temp queries created from the SQL strings in the Record and Row sources.