Change SQL Select Query into SQL Update/Append and/or Delete Query

[mrmmickle1]

I have created several queries that give me the results of records that I would like to either add to a table or delete from a table. I messed around with the INSERT INTO syntax and was not able to get it to work for my needs (I believe it will work I am just limited in my knowledge of this method). I was wondering if there was an easy way to do this. I'm fairly new to SQL so any help would be great. I will be using these SQL Queries via VBA code. I do not wish to do this manually in the MS Access Interface.

Query 1
'This Query Returns Samples in the PendingSamples Table that do Not Exist in TempPS Table ---> These records need to be deleted from the PendingSamples Table

SELECT * FROM PendingSamples
LEFT JOIN TempPS ON PendingSamples.Chart_Number = TempPS.Chart_Number
WHERE (((TempPS.Chart_Number) Is Null))

Query 2
'This Query Returns Samples in TempPS Table That Do Not Exist In PendingSamples Table ---> These records need to be added to PendingSamples Table

SELECT * FROM TempPS
LEFT JOIN PendingSamples ON TempPS.Chart_Number = PendingSamples.Chart_Number
WHERE (((PendingSamples.Chart_Number) Is Null))

Any help transforming Query 1 into a Delete Query and Query 2 into an Append/Update Query would be much appreciated. Thanks for your time.

[John_G]

Try these:

For query1:

Delete from PendingSamples where PendingSamples.Chart_Number in
(SELECT PendingSample.Chart_Number FROM PendingSamples
LEFT JOIN TempPS ON PendingSamples.Chart_Number = TempPS.Chart_Number
WHERE (((TempPS.Chart_Number) Is Null)))

This uses your original Query1 as a subquery to return the list of records to be deleted, identified by Chart_Number. Note the change in Red to return only one field with the sub-query.


For query2:

Insert into PendingSamples
SELECT * FROM TempPS
LEFT JOIN PendingSamples ON TempPS.Chart_Number = PendingSamples.Chart_Number
WHERE (((PendingSamples.Chart_Number) Is Null))

In order for this to work properly, the number of fields, and their names, must be the same in TempPS and PendingSamples.

John

[mrmmickle1]

John_G,

Thanks for the quick Response. The answer you provided for Query 1 works perfectly! Thanks for the help with this one.

On Query 2 I get an error that says: Duplicate output destination 'Type' ('Type' is a field in both tables that consists of one of about 12 different words. All entries are one of these 12 words)I looked at this Microsoft Help website: http://office.microsoft.com/en-us/ac...080760370.aspx but, was unsure what to do to fix the error. It looks like I may need to use some type of ALIAS??

I am absolutely positive the Fields are all exactly the same. This is a test so I copied the original table to help test the method. The tables are duplicates of each other with the exception of the records I have edited in order to return results. I have not set any Primary Keys in the table....

I appreciate the help. Thanks for your input on this matter.

[John_G]

Sorry -

Yes, I missed the fact that the select statement in query 2 retrieves more than just the fields from TempPS.

Try it this way:

Insert into PendingSamples
SELECT TempPS.* FROM TempPS
LEFT JOIN PendingSamples ON TempPS.Chart_Number = PendingSamples.Chart_Number
WHERE (((PendingSamples.Chart_Number) Is Null))

John

[mrmmickle1]

John_G,

Works Perfectly!!! Thanks so much for the assistance! I appreciate it.

[jas0501]

I have created several queries that give me the results of records that I would like to either add to a table or delete from a table. I messed around with the INSERT INTO syntax and was not able to get it to work for my needs (I believe it will work I am just limited in my knowledge of this method). I was wondering if there was an easy way to do this. I'm fairly new to SQL so any help would be great. I will be using these SQL Queries via VBA code. I do not wish to do this manually in the MS Access Interface.
...
...

Not sure why this is the case.
Just to be clear if you compose the query using the Access Interface to can the just switch to SQL view and cut the code and paste it into VB. Then with appropriate syntax edits you are done.

[mrmmickle1]

jas0501,

I am familiar with SQL view.

I meant that I do not want to create a macro in MS Access in order to execute the SQL statement. I am executing the SQL Statement through MS Excel in a much larger procedure that:

1. opens a .csv file
2. delimits the file ---> Due to strange hidden characters and odd delimiters and text qualifiers I am having trouble doing a "direct import" to Access
3. Connects to Access
3. Imports the data to Access one record at a time
4. Runs multiple SQL Statements
5. Closes Connection

The actual SQL statements are much larger:

UPDATED QUERY 2

INSERT INTO PendingSamples

SELECT MYSELECT.* FROM

   (SELECT SWITCH([Hold_Reason] LIKE '*X REPREP*', 'Reprep',
                           [Hold_Reason] LIKE '*X RAPIDFIRE*', 'RapidFire',
                           [Hold_Reason] LIKE '*X D/L ISOMER SEND OUT*', 'DLIsomer',
                           [Hold_Reason] LIKE '*X AMBIEN SEND OUT*', 'Quest_Sendout',
                           [Hold_Reason] LIKE '*X NEEDS DATA*', 'Needs_Data',
                           [Hold_Reason] LIKE '*X NEEDS SCREENING*', 'Needs_Screening',
                           [Hold_Reason] LIKE '*X TEST ORDER CONFIRMATION*', 'TO_Conf',
                           [Hold_Reason] LIKE '*X CLERICAL REVIEW CONFIRMATION*', 'Clerical_Review',
                           [Hold_Reason] LIKE '*X COMPLIANCE*', 'Compliance',
                           [Hold_Reason] LIKE '*X NO PAF*', 'NO_PAF',
                           [Hold_Reason] LIKE '*X POSITVE FOR ILLEGAL*', 'ILL_NARC',
                           [Pathologist] IS NULL, 'Other_NR',
                           TRUE, 'Other_NS'
                   ) AS Type, 

[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Date_Received] - [Date Specimen Taken] AS Pending_Days, [Pathologist]

FROM SamplesReceived
 WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND 
([Chart_Number] LIKE 'UC14-*' OR [Chart_Number] LIKE 'OF14-*' OR [Chart_Number] LIKE 'RF14-*' OR [Chart_Number] LIKE 'SA14-*')) AS MYSELECT

LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE (((PendingSamples.[Chart_Number]) IS NULL));