SQL append

[cmac2210]

I know that this is more than likely very easy, but I can not seem to get the language right. I am trying to append all records from table 1 where the feild closed in table 2 is checked. I can get the append to work using the query generator in access, but I am trying to minimize the use of those queries. anyone know how to properly write this? the field closed is a checkbox.

 

[ByteMyzer]

You'll want to use something along these lines (substituting the highlighted text with the actual table/field names):

INSERT INTO [B][I]Table3[/I][/B]
SELECT [B][I]Table1[/I][/B].*
FROM [B][I]Table1[/I][/B]
INNER JOIN [B][I]Table2[/I][/B] ON [B][I]Table1[/I][/B].[B][I]MyLinkingField[/I][/B] = [B][I]Table2[/I][/B].[B][I]MyLinkingField[/I][/B]
WHERE [B][I]Table2[/I][/B].[B][I]Closed[/I][/B] = True;

By the way, something like this is quite easy to create with the Query Builder.

 

[raskew]

Hi -

Something like this maybe (when you say 'closed' you do mean checked, i.e. True?)

INSERT INTO tblTransfer2 (Category, CatID )
SELECT tblTransfer.Category, tblTransfer.CatID
FROM tblTransfer INNER JOIN tblTransfer2 ON tblTransfer.PymtID = tblTransfer2.PymtID
[COLOR="Red"]WHERE (((tblTransfer2.LoanExp)=True));[/COLOR]

HTH - Bob

 

[cmac2210]

I ended up getting it to do exactly what I wanted to with this little gem. I dont know why its different then the one you guys suggested, but like I said its working.

DoCmd.RunSQL "INSERT INTO tblremove 
SELECT jonesy.* 
FROM jonesy WHERE (((jonesy.remove)=True));"

 

[ByteMyzer]

...I dont know why its different then the one you guys suggested, but like I said its working...

Probably because you are selecting from one table instead of two, which is different from what you stated in your original post:

...I am trying to append all records from table 1 where the feild closed in table 2 is checked...

Did you perhaps mean that you are trying to append records TO table 1 FROM table 2 where the Closed field is checked?

 

[cmac2210]

Yes, that is exactly what I meant! Thanks.