InsertToQuery.mdb

<div>
tx everyone ! &nbsp;<br />
<br />
s
</div>


<div>
...But then why not just make another query...?<br />
<br />
When you base one query from another query, you have to remember to *never* delete the precedent query.<br />
Then you have to keep track of *Every* query that has dependent queries<br />
:-O<br />
<br />
So instead of doing this:<br />
Q1<br />
SELECT Fld1, Fld2,...etc<br />
FROM SmoeTable <br />
Where Fld2=7<br />
&nbsp; &nbsp; ...Then This...<br />
Q2<br />
SELECT Fld1, Fld2<br />
FROM Q1<br />
Where Fld1=9<br />
<br />
<br />
...Just do something like this in a new query from the table:<br />
Q_New<br />
SELECT Fld1, Fld2<br />
FROM YourTable<br />
WHERE Fld1=9 AND Fld2=7<br />
<br />
JeffCoachman
</div>


<div>
&quot;Linking&quot; queries also means that you have to know how every change in the precedent query will effect all the dependent queries...
</div>


<div>
Hi Jeff,<br />
<br />
tx for writing.... you have telepathy.... i really want to combine my canned queries into one...<br />
<br />
was having trouble with my inner joins using one query...<br />
<br />
&nbsp;so figured let me just get the data, to give me a &quot;little&quot; win and perhaps give my boss some data<br />
and while my boss is analyzing i have time to retool<br />
<br />
perhaps a little later will post a related question with my inner join issues....<br />
<br />
tx for understanding intuitively where i really want to head ....<br />
<br />
tx , s
</div>


<div>
@boag2000<br />
Depends upon the complexity of the base queries involved.<br />
I don't like big monstrous SQL statements in my VBA code.<br />
They're hard to read, hard to debug and just plain ugly.<br />
So I can see why someone would like to be able to cut some of that out by INSERT INTO a query instead of a really gnarly SELECT statement<br />
<br />
Personally, because I am never inserting more than one record at a time, I much prefer<br />
Dim rs as recordset<br />
set rs = CurrentDb.OpenRecordset(&quot;S<wbr />omeQuery&quot;,<wbr />dbOpenDyna<wbr />set)<br />
with rs<br />
&nbsp; &nbsp; .AddNew<br />
&nbsp; &nbsp; !SomeField = whatever<br />
&nbsp; &nbsp; ...<br />
&nbsp; &nbsp; .Update<br />
end with<br />
<br />
As being maintainable code. &nbsp;And my naming scheme makes me aware of the purpose of a query so I know where to look for dependencies.<br />
But that's me!<br />
YMMV
</div>


<div>
&lt;Depends upon the complexity of the base queries involved.&gt;<br />
Yep, <br />
My post was just an FYI.<br />

</div>


<div>
@nick<br />
<br />
I understand your points....<br />
<br />
question: &nbsp;can you elaborate on your naming scheme for queries<br />
<br />
comment: &nbsp;am doing &quot;bulk&quot; insert, bec. am processing external vendor file called a<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &quot;this year file&quot; &nbsp; into a historical table that acts as a repository<br />
<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;the vendor's file needs to be massaged a bit, by adding fields from another table<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;hence the need for inner joins, which resulted in created a canned query<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from which you guys explained that i can SELECT FROM to do the bulk insert into<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;the repository table.<br />
<br />
tx, s 
</div>


<div>
Incidentally, Sandra, you could breakdown your inserts into chunks<br />
<br />
dim db as database<br />
set db = CurrentDb<br />
Dim TheLynchPinID as Long<br />
Currentdb.Execute(&quot;INSERT INTO YourMainTable (Field1, Field2) SELECT Field1,Field2 FROM YourQuery&quot;)<br />
set rs = db.opendynaset(&quot;Select WhateverID from YourMainTable where field1 = something and field2= something, order by WhateverID desc;&quot;)<br />
TheLynchPinID = rs!WhateverID<br />
rs.close<br />
Currentdb.Execute(&quot;INSERT INTO AnotherTable (TheIdField,Field1, Field2) Values (TheLynchPinID, SomeThing, SomeThingElse)<br />
Currentdb.Execute(&quot;INSERT INTO AThirdTable (TheIdField,Field1, Field2) Values (TheLynchPinID, SomeThing, SomeThingElse)
</div>


<div>
I always name a query I am going to <i>keep</i> starting with <b>qry</b><br />
The rest of the name is dependent on the situation where i use it.<br />
I'll put a description in of object that have dependencies <a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/509207/description.jpg" target="_blank" title="query description (36 KB)"><img alt="query description" class="file-block lazyload" style="max-width: 394px;" data-src="https://filedb.experts-exchange.com/incoming/2011/10_w41/509207/description.jpg" /></a>between both, I am aware enough to be careful.<br />
<br />
And in the code Ctrl-F is my friend, once I know I need to be careful
</div>


description.jpg

<div>
<div class="content wysiwyg-content">
Hi EE,<br />
<br />
Access 2003: &nbsp;Can i code an INSERT ... SELECT FROM... from a canned &nbsp;**query** not a table....? If yes, how is it coded.... just using the query name instead of the table<br />
<br />
and if so, will Access know that i mean a query and not a table...<br />
<br />
tx for your help, sandra
</div>
</div>


Hi EE,

Access 2003:  Can i code an INSERT ... SELECT FROM... from a canned  **query** not a table....? If yes, how is it coded.... just using the query name instead of the table

and if so, will Access know that i mean a query and not a table...

tx for your help, sandra

Access 2003:  Can i code an INSERT ... SELECT FROM... from a canned  **query** not a table....?

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.