CODEWITHSUNDEEP
ms-accessms-access-2010
JOSE LEON
JOSE LEON

Reputation: 19

Delete Query using another query in Access 2010

i have a problem deleting records. i linked two tables using a field named Asset_ID which it is my Primary key. i create a query that pulls all records that does not match, so those records that doesn't match i want to delete it. When i run the query it give me a error saying "Could not delete from specified tables", i did some research and they suggested to go to my delete query property and change the option "Unique Records" to Yes. Did not worked. please help me with this.

I am using Access 2010 query builder to do it but here is the Sql code:

DELETE DISTINCTROW [HANDHELD TABLE1 Without Matching HANDHELD TABLE].ASSET_ID
FROM [HANDHELD TABLE1 Without Matching HANDHELD TABLE]
WHERE ((([HANDHELD TABLE1 Without Matching HANDHELD TABLE].ASSET_ID) Is Not   Null));

Here is my HANDHELD TABLE1 Without Matching HANDHELD TABLE Sql.

SELECT [HANDHELD TABLE1].ASSET_ID, [HANDHELD TABLE1].INSPECTION_NUMBER, [HANDHELD TABLE1].INSPECTION_FLUSH, [HANDHELD TABLE1].LAST_PRESSURE, [HANDHELD TABLE1].LAST_INSPECT_DATE, [HANDHELD TABLE1].ADDRESS, [HANDHELD TABLE1].LOCATION_DESCRIPTION1, [HANDHELD TABLE1].COMMENTS, [HANDHELD TABLE1].MUNICIPALITY, [HANDHELD TABLE1].PRESSURE_ZONE, [HANDHELD TABLE1].FACILITY_CATEGORY, [HANDHELD TABLE1].LIFECYCLESTATUS, [HANDHELD TABLE1].MANUFACTURER, [HANDHELD TABLE1].INSP_BY, [HANDHELD TABLE1].PAINT, [HANDHELD TABLE1].[GROUND CLEARANCE], [HANDHELD TABLE1].[CURB CLEARANCE], [HANDHELD TABLE1].OBSTRUCTIONS, [HANDHELD TABLE1].[STUFFING BOX LEAK], [HANDHELD TABLE1].[CAULKING LEAK], [HANDHELD TABLE1].CAPS, [HANDHELD TABLE1].DRIP, [HANDHELD TABLE1].[DRIP COMMENTS], [HANDHELD TABLE1].FLUSHED, [HANDHELD TABLE1].[OIL STUFFING BOX], [HANDHELD TABLE1].[OIL CAPS], [HANDHELD TABLE1].DRAINAGE, [HANDHELD TABLE1].[DRAINAGE COMMENTS], [HANDHELD TABLE1].LUBRICATION, [HANDHELD TABLE1].[OPERATING NUT], [HANDHELD TABLE1].[OPERATING NUT COMMENTS], [HANDHELD TABLE1].NOZZLE, [HANDHELD TABLE1].[NOZZLE COMMENTS], [HANDHELD TABLE1].[MAIN WASHER], [HANDHELD TABLE1].[INSPECTION YEAR], [HANDHELD TABLE1].[TIME INSPECTED], [HANDHELD TABLE1].NOISE, [HANDHELD TABLE1].TIMEOPENED, [HANDHELD TABLE1].TIMECLOSED, [HANDHELD TABLE1].TIMETOCLEAR, [HANDHELD TABLE1].OPENEDCOLOR, [HANDHELD TABLE1].CLOSEDCOLOR, [HANDHELD TABLE1].FLUSHREMARKS, [HANDHELD TABLE1].DATEFLUSHED, [HANDHELD TABLE1].LASTFLUSHDATE, [HANDHELD TABLE1].FLUSHEDBY, [HANDHELD TABLE1].TIMEFLUSHED
FROM [HANDHELD TABLE1] LEFT JOIN [HANDHELD TABLE] ON [HANDHELD TABLE1].ASSET_ID = [HANDHELD TABLE].ASSET_ID
WHERE ((([HANDHELD TABLE].INSPECTION_FLUSH) Is Null) AND (([HANDHELD TABLE].ASSET_ID) Is Null));

Upvotes: 0

Views: 675

Answers (1)

random_answer_guy
random_answer_guy

Reputation: 856

It looks like from your supplied queries, you are linking the table unto itself in order to find field where is the inspection flush is null.

Your issue then is left joining your key field "ASSSET_ID" unto itself and looking for nulls. This case will never happen as its the key field.

try something like :

DELETE DISTINCTROW [HANDHELD TABLE_1].INSPECTION_FLUSH, [HANDHELD TABLE].*
FROM [HANDHELD TABLE] LEFT JOIN [HANDHELD TABLE] AS [HANDHELD TABLE_1] ON [HANDHELD TABLE].INSPECTION_FLUSH = [HANDHELD TABLE_1].INSPECTION_FLUSH
WHERE ((([HANDHELD TABLE_1].INSPECTION_FLUSH) Is Null));

Upvotes: 0

Related Questions