Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-29-2015, 11:59 PM   #1
peterpann88
Newly Registered User
 
Join Date: Jan 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
peterpann88 is on a distinguished road
Question Linked delete query with a Max

Hi there

I have a delete query where i want to delete only the row that contains the max value of the IDnum field from the table STM, where it links two tables on CellTell

I currently have this:

DELETE DISTINCTROW STM.*, STM.IDNum
FROM dpl_00c_tbl_StmCellDups
INNER JOIN STM ON dpl_00c_tbl_StmCellDups.STM_CellTel = STM.CellTel
WHERE (((STM.IDNum)=(select max(IDNum) from `STM`)));

It doesn't want to throw out any values when i run it, or view it. Am i doing something wrong?

Appreciate any help

peterpann88 is offline   Reply With Quote
Old 01-30-2015, 01:33 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,438
Thanks: 40
Thanked 3,368 Times in 3,263 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Linked delete query with a Max

There is a definite problem with your subquery

(select max(IDNum) from `STM`)

a) you should not put quote marks around the table name - use square brackets if required
b) since this table is in your main query you also need to alias it so

(select max(IDNum) from STM AS T)

Not tested, but don't think you need DISTINCTROW which normally applies to SELECT

And I see no reason for the inner join - if the max IDnum is not in a record linked to dpl_00c_tbl_StmCellDups then nothing will be returned anyway. Perhaps your subquery should be referencing dpl_00c_tbl_StmCellDups rather than STM?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-30-2015, 01:34 AM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,289
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Linked delete query with a Max

As soon as you start aggregating records, databases lose track of individual records and can't process them, making the query non-updateable.

The DistinctRow could be a problem.

Sometimes problems with deleting records in joins can be worked around by updating a field to an out of scope value to flag the record and then running a delete on the flag.

Galaxiom is offline   Reply With Quote
Reply

Tags
delete , link , max value , query , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete query using non-linked table merry_fay Queries 6 08-23-2011 01:45 AM
Can't delete linked tables newby2VBA Tables 16 03-08-2010 01:36 PM
How to delete a record from table? (while subfrm linked to query) hjeff71 Queries 2 05-14-2009 11:57 AM
Delete query using linked tables MatMac Queries 8 01-14-2004 12:17 AM
Delete Query on Linked Table jwindon Queries 1 08-17-2001 04:58 AM




All times are GMT -8. The time now is 10:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World