Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Deleting Criteria

I included a Sample Database.
 Problem is the Delete query.  When I view it gives the right information.
But when I hit Run  it states “Specify the table containing the records you want to delete.  Everything  I TRIED AS NOT WORKED.
The Records need to delete out table ‘CMTS_Daily_Extract and the MinTime Taken only leaving the Max Time.

This is the criteria :WHERE (([CMTS_Daily_Extract]![TakenTime]=[DupsWithOnlyMINTime]![TakenTime]));

SQL View: DELETE CMTS_Daily_Extract.CMTID, CMTS_Daily_Extract.TakenTime
FROM CMTS_Daily_Extract, DupsWithOnlyMINTime
WHERE (([CMTS_Daily_Extract]![TakenTime]=[DupsWithOnlyMINTime]![TakenTime]));

I greatly appreciate any help!


DeleteCriteria.mdb
0
ca1358
Asked:
ca1358
  • 3
  • 2
1 Solution
 
PatHartmanCommented:
The query doesn't include a specific join so it is creating a Cartesian Product which is NOT updateable.

Try
SQL View: DELETE CMTS_Daily_Extract.*
 FROM CMTS_Daily_Extract Inner Join DupsWithOnlyMINTime
 ON (([CMTS_Daily_Extract]![TakenTime]=[DupsWithOnlyMINTime]![TakenTime]));

If that doesn't work, it is because of the aggregation in the Dups query.  A subselect might work so you can try that.  If nothing else works to make the query updateable, then you need to create a temp table to hold the dups and join to that instead of to the query.  Most of the time, you should avoid temp tables like the plague.  Use them only if you start with a large number of rows and through lots of manipulation end up with a few rows and you need to use the result set more than once OR as in this case if your action query must join to aggregated data and that is making the query non-updateable.
0
 
ca1358Author Commented:
It did not work . Error "join Expression not supported "ON [CMTS_Daily_Extract]![TakenTime]=[DupsWithOnlyMINTime]![TakenTime]"
0
 
PatHartmanCommented:
Rather than trying to debug the syntax with our eyes, build the query using the QBE.  

Add both tables.
Draw the join line.
Change the query type to Delete.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ca1358Author Commented:
Including File with query "Delete_QRY
DeleteCriteria.mdb

When Hit Run Error is "Specify the table containing the records you want to delete.
0
 
Rey Obrero (Capricorn1)Commented:
try this query, copy and paste

DELETE CMTS_Daily_Extract.*
FROM CMTS_Daily_Extract
Where exists(select DupsWithOnlyMINTime.TakenTime From DupsWithOnlyMINTime where DupsWithOnlyMINTime.CMTID=CMTS_Daily_Extract.CMTID And DupsWithOnlyMINTime.TakenTime=CMTS_Daily_Extract.TakenTime)
0
 
ca1358Author Commented:
Thank you!!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now