Writing snapshots of data in Access 1

[davejoyce]

I want to be able to perform a complicated select statement and then write the results, row by row to a snapshot table. I don't believe cold fusion will allow you to nest CFOUTPUT statements, so the only way I can think of to accomplish this is to generate the results set, store in an array of structures and then use that array as input to the inserts.<br><br>Is there a better way??<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Thanks, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dave<br><br>&lt;CFPARAM name=&quot;szDate&quot; default=#DateFormat(Now), &quot;mm/dd/yy&quot#&gt;<br><br><br>&lt;CFQUERY NAME=&quot;GetStatusData&quot; DATASOURCE=&quot;Sites&quot;&gt;<br> (SELECT&nbsp;&nbsp;plant, status, status_date, N1, Start_Date,<br> &nbsp;&nbsp;&nbsp;&nbsp;(Select count(*) FROM list1_tbl<br> WHERE resp_person = 'Dave' <br> AND status = 'Open'<br> AND plant = list2_tbl.plant) as dave_open, <br> (Select count(*) FROM list1_tbl<br> WHERE resp_person = 'John' <br> AND status = 'Open'<br> AND plant = list2_tbl.plant) as john_open<br> FROM&nbsp;&nbsp;list1_tbl<br> where N1 NOT EQUAL &quot;C&quot;<br> ORDER by Start_Date, plant)<br>&lt;/cfquery&gt;<br><br>&lt;CFQUERY NAME=&quot;CreateSnapshot&quot; DATASOURCE=&quot;archive_Status&quot;&gt;<br>INSERT INTO StatusSnapshot(SnapshotDate, Plant, nweek, dave_open, john_open, status, status_date)<br> Values ('#szDate#', '#nweek#', '#dave_open#', '#john_open#', '#status#', '#status_date#')<br>&lt;/cfquery&gt;<br><br>&lt;html&gt;<br>&lt;head&gt;<br> &lt;title&gt;Untitled&lt;/title&gt;<br>&lt;/head&gt;<br><br>&lt;body&gt;<br><br>&nbsp;&lt;CFOUTPUT QUERY=&quot;GetStatusData&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;CFSET nWeek = #DateDiff(&quot;WW&quot;, Start_Date, Now())# + 1&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;CFOUTPUT QUERY=&quot;CreateSnapshot&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/cfoutput&gt;<br>&lt;/cfoutput&gt;<br>&nbsp;

 

[DarkMan]

I'm not sure what you're saying here...&nbsp;&nbsp;The CFOUTPUT QUERY=&quot;CreateSnapshot&quot;&gt; would have no record set, since it is an insert...<br><br>To do a row by row insert from another query, you could try something like:<br><FONT FACE=monospace><b><br>&lt;cfquery datasource=&quot;mydb&quot; name=&quot;TheQuery&quot;&gt;<br>Select Field1,Field2,Field3 from MyTable<br>order by Date<br>&lt;/cfquery&gt;<br><br>&lt;cfoutput query=&quot;TheQuery&quot;&gt;<br>&nbsp;&nbsp;&lt;cfquery datasource=&quot;mydb&quot; name=&quot;CreateSnapShot&quot;&gt;<br>&nbsp;&nbsp;Insert into Snapshot (Field1,Field2,Field3)<br>&nbsp;&nbsp;Values (#Field1#,#Field2#,#Field3#)<br>&nbsp;&nbsp;&lt;/cfquery&gt;<br>&lt;/cfoutput&gt;<br></b></font><br><br>Is this what you're trying to do???

 

[davejoyce]

This is what I was trying to do. I kind of figured it out thru trial and error and by looking a other posts on the site. Thanks for the help though.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dave Joyce