SQL command in Access 2003

[stvlai]

Hi

I am trying to run merge 2 queries (that is using SQL command) into 1
query. Can you do that in Access? e.g I am trying to Delete duplicate
records using the SQL command Delete ... From ..Where. 2nd thing I
like to do is to Insert new records into another table.

Can I have the SQL Delete and Insert in 1 same Query instead of 2? I
am lost. Thanks for any any suggestions or alternatives.


DELETE tbl_Group2.ProductID, tbl_Group2.SN
FROM tbl_Group2
WHERE (((tbl_Group2.ProductID)<>(SELECT Min(ProductID) AS MinProduct
FROM tbl_Group2 AS Dupe
WHERE (Dupe.SN = tbl_Group2.SN))));

INSERT INTO tbl_Group1 ( SN, ModelNo, [Date], ID, [Time], JobID )
SELECT Buffer.SN, Buffer.ModelNo, Buffer.Date, Buffer.ID, Buffer.Time,
Buffer.JobID
FROM Buffer
WHERE (((Buffer.SN) Like "part*")) OR (((Buffer.SN) Like "rma*")) OR
(((Buffer.SN) Like "sold*")) OR (((Buffer.SN) Like "work*"));

Thanks

Steven

 

[John W. Vinson]

Can I have the SQL Delete and Insert in 1 same Query instead of 2? I
am lost. Thanks for any any suggestions or alternatives.

By default, you cannot: each query needs to stand alone. You can run multiple
queries together by calling them from a Macro of (better) from VBA code,
wrapping them in a Transaction if you need to ensure that they either both run
to completion or neither runs at all. See the VBA help for OpenTransaction.

 

[stvlai]

John

Thanks a lot for the help and tip! Will check it out.

Steven