Select qry works - delete qry fails

Why does this select query return the correct records but when I make it a 
delete query I get a msgbox with "Could not delete from specified
tables".

   SELECT BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs)

   DELETE BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs)

Thanks

0
ms3400 (28)
2/25/2004 3:20:32 AM
comp.databases.ms-access 42532 articles. 0 followers. Post Follow

5 Replies
116 Views

Similar Articles

[PageSpeed] 52
I suspect it is because you've joined table together... I don't think you
can't delete records this way.

You should delete from one table and set up the relationships so the delete
will cascade to any associated tables.
-- 
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"ms" <ms@nospam.comcast.net> wrote in message
news:9t6dnWr1p8sPiaHdRVn-ug@comcast.com...
> Why does this select query return the correct records but when I make it a
> delete query I get a msgbox with "Could not delete from specified
> tables".
>
>    SELECT BMIDLog.*
> FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID =
stageBMIDLog.BattID)
> AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs
=
> stageBMIDLog.TotalChgAhs)
>
>    DELETE BMIDLog.*
> FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID =
stageBMIDLog.BattID)
> AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs
=
> stageBMIDLog.TotalChgAhs)
>
> Thanks
>


0
bradley9026 (125)
2/25/2004 5:10:26 AM
Thanks Bradley, but won't confuses me is, why does this Delete query work then? 
   What makes a join query in a delete statement work?

DELETE stageChargeLog.*
FROM ChargeLog INNER JOIN stageChargeLog ON 
([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
([ChargeLog].[BattID]=[stageChargeLog].[BattID]);


Bradley wrote:
> I suspect it is because you've joined table together... I don't think you
> can't delete records this way.
> 
> You should delete from one table and set up the relationships so the delete
> will cascade to any associated tables.

0
ms3400 (28)
2/25/2004 6:36:18 PM
I also followed Access Help (Delete records from one table or tables in a 
one-to-one (and one-to-many) relationship) using the query design window and 
still it works as a select but fails as a Delete.  No information, no help, 
nothing.  Just says can't delete.
This is what the query design query is in SQL:

DELETE stageBMIDLog.*
FROM stageBMIDLog, TMPstageBMIDLog
WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND 
TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND 
TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];


ms wrote:

> Thanks Bradley, but won't confuses me is, why does this Delete query 
> work then?   What makes a join query in a delete statement work?
> 
> DELETE stageChargeLog.*
> FROM ChargeLog INNER JOIN stageChargeLog ON 
> ([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
> ([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
> ([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
> 
> 
> Bradley wrote:
> 
>> I suspect it is because you've joined table together... I don't think you
>> can't delete records this way.
>>
>> You should delete from one table and set up the relationships so the 
>> delete
>> will cascade to any associated tables.
> 
> 

0
ms3400 (28)
2/25/2004 8:16:28 PM
I figured this out - enough to get my job done:
The delete query with a JOIN works when using .Execute from code vs. from the 
object window.
Don't know why though.
Thanks,
mark

ms wrote:
> I also followed Access Help (Delete records from one table or tables in 
> a one-to-one (and one-to-many) relationship) using the query design 
> window and still it works as a select but fails as a Delete.  No 
> information, no help, nothing.  Just says can't delete.
> This is what the query design query is in SQL:
> 
> DELETE stageBMIDLog.*
> FROM stageBMIDLog, TMPstageBMIDLog
> WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND 
> TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND 
> TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
> 
> 
> ms wrote:
> 
>> Thanks Bradley, but won't confuses me is, why does this Delete query 
>> work then?   What makes a join query in a delete statement work?
>>
>> DELETE stageChargeLog.*
>> FROM ChargeLog INNER JOIN stageChargeLog ON 
>> ([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
>> ([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
>> ([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
>>
>>
>> Bradley wrote:
>>
>>> I suspect it is because you've joined table together... I don't think 
>>> you
>>> can't delete records this way.
>>>
>>> You should delete from one table and set up the relationships so the 
>>> delete
>>> will cascade to any associated tables.
>>
>>
>>
> 

0
ms3400 (28)
2/25/2004 11:28:39 PM
I figured this out - enough to get my job done:
The delete query with a JOIN works when using .Execute from code vs. from the 
object window.
Don't know why though.
Thanks,
mark

ms wrote:
> I also followed Access Help (Delete records from one table or tables in 
> a one-to-one (and one-to-many) relationship) using the query design 
> window and still it works as a select but fails as a Delete.  No 
> information, no help, nothing.  Just says can't delete.
> This is what the query design query is in SQL:
> 
> DELETE stageBMIDLog.*
> FROM stageBMIDLog, TMPstageBMIDLog
> WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND 
> TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND 
> TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
> 
> 
> ms wrote:
> 
>> Thanks Bradley, but won't confuses me is, why does this Delete query 
>> work then?   What makes a join query in a delete statement work?
>>
>> DELETE stageChargeLog.*
>> FROM ChargeLog INNER JOIN stageChargeLog ON 
>> ([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
>> ([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
>> ([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
>>
>>
>> Bradley wrote:
>>
>>> I suspect it is because you've joined table together... I don't think 
>>> you
>>> can't delete records this way.
>>>
>>> You should delete from one table and set up the relationships so the 
>>> delete
>>> will cascade to any associated tables.
>>
>>
>>
> 

0
ms3400 (28)
2/25/2004 11:29:12 PM
Reply:
Similar Artilces:

Free Text Ad Advertising that REALLY works
<HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft DHTML Editing Control"> <TITLE></TITLE> </HEAD> <BODY> <DIV><FONT color=#000000><A href="http://adsvert.com/?uid=farpoint7" target=_blank><SPAN class=yshortcuts id=lw_1205857389_0>http://adsvert.com/?uid=farpoint7</SPAN></A></FONT></DIV> <DIV><BR></DIV> <DIV><FONT color=#000000>Text ads are a really great way to drive highly-targeted visitors straight to your website</FONT&g...

Hide/Show Selection List
I need the capabilty to hide/show a selection list, just the way its done at http://www.lufthansa.com (place the cursor over "Group Companies"). However, I am looking for a javascript that is much simpler. Here is what I have until now. Problems with my code: 1. The selection list becomes invisible when I try to select an option (in Firefox). 2. The selection list stays visible when I just place the cursor over selection list and move the cursor out (without clicking). Please help, Shashi <HTML> <HEAD> <script type="text/javascript"> function showSelect...

E4500, failed dual-scsi board and system crashes.
Hello, I've got an E4500 here. The owner came to me asking why the machine crashed recently. I couldn't find cores, and there was no mention of anything in the messages files. prtdiag (only) tells me: > Detected System Faults > ====================== > PROM detected failure > Detected Wed Jul 28 12:09:14 2004 ...so I look into the messages files deeper. I find the following: > [ID 744982 kern.notice] NOTICE: failed dual-pci board in slot 7 ....prtdiag doesn't even list this board, so I assume it's not initialized? The owner of the machine says ...

select in a loop
Hello everyone, I just started with PL/Sql and directly have a problem. I want to iterate in a loop and use an entry in a where-statement of a pretty complex query. I did the following: BEGIN FOR item IN ( SELECT * FROM test ) LOOP select * from (pretty complex subquetries...) where something.id = item.id END LOOP; END; Unfortunately this does not seem to work. Does anybody of you have an idea? Best regards, Alex On Jul 11, 3:40 pm, "alexander.stuckenh...@fernuni-hagen.de" <alexander.stuckenh...@gmail.com> wrote: > Hello ...

x86 Server Selection
We're doing some basic research on how corporate customers evaluate Intel/AMD servers. What we're trying to figure out is if vendor characteristics (service, reliability, etc) are more important than system characteristics (feeds & speeds) to real world customers. . We are asking interested enterprise customers to fill out a web survey. The first 300 participants will receive a $10 Amazon.com gift certificate delivered via email (hotmail, gmail, other anonymous accounts are OK). We aren't selling anything, will not convey info to any 3rd party, and will not use the email addres...

Selecting objects
Is it possible get the handle of a graphics object selected in a figure? help gco Laurren Vishal Mahulkar wrote: > > > Is it possible get the handle of a graphics object selected in a > figure? Vishal Mahulkar wrote: > > > Is it possible get the handle of a graphics object selected in a > figure? See the get_line_hand function in FEX <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=9631&objectType=FILE> ...

Multiple selection with QT library
Hi, I went into QT mailing list archive but with no luck. I'm trying to achieve the effect of selection with mouse. When you click left mouse on the desktop for example, and keep it pressed and move the mouse a rectangle isdrawed following the mouse. How can I do this with QT library ? -- Bye, Colossus Cpsed, a Linux OpenGL 3D scene editor http://cpsed.sourceforge.net/ ...

Free firewall with selectable port blocking?
Am looking for a free software firewall which can block specified ports (e.g. port 3137). It's for a slow PC running XP and I do not want to slow it down more with HIPS, anti-phishing or other additional functions. Just want a plain but configurable firewall. Any recommendations? I already tried these.... Agnitum Outpost Free has no fine tuning options. I had bad experiences with Zone Alarm in the past long ago. Comodo with HIPS disabled makes the PC run noticably slower. In the past Filseclab didn't block properly. "Anonymous" wrote: >"F...

daq usb 6008 output not working
Hi All , &nbsp; &nbsp;&nbsp;&nbsp;&nbsp; I was trying to acquire voltage of 9v in my DAQ USB 6008.I want to get o/p of 3v from same USB 6008&nbsp;as soon as my battery ip is received by USB 6008.I gave i/p at aio and was trying to get o/p from ai1 , but I am getting errors and am not able to either program it correctly in LabView or am missing something . Please guide me how can I get this functionality. &nbsp; Thanks ans Regards ! Sarita In all of my 31 years of experience, any time I have seen an I/O connection identified with a designation of the form aiX singin...

vector selection of a selection
suppose i have a= [1 2 3 4 5 6 7 8 9] somewhere in my code i have for example: a(5:8) is it possible to select the last two elements of a(5:8) in a single statement so something like a(5:8)(end-1:end)?? On 2006-05-18 20:03 kanivan said the following: > suppose i have a=3D [1 2 3 4 5 6 7 8 9] >=20 > somewhere in my code i have for example: > a(5:8) >=20 > is it possible to select the last two elements of a(5:8) in a single > statement so something like a(5:8)(end-1:end)?? No not in general, but you could use an anonymous function (but I don=B4t= =20 really see the point...

normpdf works for very small sigma values?
Hello I am using the normpdf function to plot gaussian distributions for specific mean and std values. I have noticed that when sigma is small sigma<0.5 the normpdf returns values that are higher than 1 (which of course is not correct). Do you know what might be wrong? Best Regards Alex. "Alex " <alaios@yahoo.com> wrote in message <hstpu4$k5h$1@fred.mathworks.com>... > Hello I am using the normpdf function to plot gaussian distributions for specific mean and std values. I have noticed that when sigma is small sigma<0.5 the normpdf returns values that ar...

Mechanize Select List Help Needed
Hello. I thought that I'd tap into the group to find out if anyone has had this problem with select lists through Mechanize. I am submitting a form that has a multi select list option. When I view the code submitted through Mechanize, it doesn't show that I selected any of my options in the list, but it will show that I checked one of the checkboxes. My select list code is this: search.fields.name('selectList').options[2].select Any ideas? Thanks. -- Posted via http://www.ruby-forum.com/. Any thoughts on where to look for help with my mechanize question? I am on the...

Zone transfer not working
Hi, my zone transfer does not work and I would appreciate some help. Setup: Master: (80.190.246.175) acl partners { 85.214.66.29; }; zone "linksabbieger.net" in { type master; file "linksabbieger.net.zone"; allow-query { any; }; notify yes; allow-transfer { partners; }; }; Slave: (85.214.66.29) zone "linksabbieger.net" in { type slave; file "slaves/linksabbieger.net.zone"; allow-query { any; }; masters { 80.190.246.175; }; allow-notify { 80.190.246.175...

Karmic Krapware: "EPIC FAIL" Angry victim gets 10 seconds of uptime before freeze and hard boot
"As soon as I boot up to the desktop, I get 10 seconds of using it before I freeze and have to hard boot. It's frustrating and I'm pretty ticked off about it. I was running 9.04 before and was having random freezes every now and then, probably because I Upgraded from 8.10, but not being able to boot into the desktop is an EPIC FAIL! " http://ubuntuforums.org/showthread.php?t=1305130&highlight=karmic+freeze Linux: it's a real keeper. ...

Re: PROC SQL--select DISTINCT #17
Summary: Distinct #iw-value=1 Tom White <tw2@MAIL.COM> wrote SAS does not recognize the distinct function. In other words, I cannot write ..... select(distinct VAR1), distinct(VAR2), distinct(VAR3), ..... quit; To understand why, consider the data X Y ----- 1 1 1 2 Which row would you eliminate to honor DISTINCT X? DISTINCT is a property of the row not the column. In select count(distinct x), count(distinct y) DISTINCT is a property of one column which really involves a special subquery. In other words, the DISTINCTs are distinctly different...

window selection
Dear All, In AutoCAD, the user can select the elements using window(standard or crossing). Can this be possible in UStation through MDL. In UStation if user wants to do any manipulation, either he need to select single element or place a fence, before doing something. I am looking for a example code for multiple selection to do my task easier. I'll be obliged if anybody could help me out. Thanks in advance Regards, san San, In Microstation you can use the Selection tools to select multiple elements. These are the two Arrow icons. Selector and PowerSelector. Chec...

need help with code irregular working code
The code below is designed to loop through rows of a database query obtaining email addresses and send an email to each. It is modified form fomr some code I found on the net. With each while loop, it updates the SENT field of the processed row to mark it sent. I have a sleep function in there to slow the processing down to see if that alleviated the following problem - it did help. What is happening is the first 20 emails it sends correctly. Then it starts sending with no $subject or $message but the remove form list text is still in the email. When I did it for a client, some clients en...

skewing a selection
Hi, Can anyone tell me how to skew just a selection (not the image itself)? Here's an example of what I'm talking about: http://www.entertaining-ideas.com/testing/skew.jpg BTW: I'm still using PSP7. Thanks! "TennisNut6" <tennisnut6@aol.com> wrote in message news:20040626122634.10883.00000335@mb-m25.aol.com... > Hi, > > Can anyone tell me how to skew just a selection (not the image itself)? Here's > an example of what I'm talking about: > > http://www.entertaining-ideas.com/testing/skew.jpg > > BTW: I'm still using PSP7. ...

select all elements of one selection list
hello I'm french so I hope I hunderstand your answers ! I'm begginer in javascript and I would like to do this : select all elements of one selection list (html) with a javascript fonction could you help me ? thanks lot ! gsmurf@mageos.com (GiSmo) writes: > hello I'm french so I hope I hunderstand your answers ! I'm begginer > in javascript and I would like to do this : > > select all elements of one selection list (html) with a javascript > fonction It requres the select element to accept multiple selected options (e.g., the tag contains the "multipl...

ifplugd working?
Does anyone here use ifplugd? Successfully? I have an LFS installation on a thinkpad, for which I want to run certain network daemons, only when my ethernet link is up. ifplugd seems to work on startup (brings up the daemons), but I cannot get it to (a) shutdown, or (b) bring down the daemons. I'm not a C programmer, but I'm attempting to feel my way through the code. My free time is limited however and it's a long and painful process. Any ideas? I have emailed the package creator, no response yet. I thought I may be able to get an answer now so I can get on with configurin...

How to re-print values from combobox selection
Hi, We have a combo box on our page, which gets populated via a MySQL Query in PHP. What we want to do is to print the values on the page in a table that correspond the to selection from the combo box without a page refresh. I assume that I'll need to query ALL the needed values from the database and store them in an array This can be done in PHP. Drawing the combo box is not a problem. But, once a selection is made, how do I print the values to the page? I'm assuming an 'on change' events needs to be attached to the box, but, it is not a form, so, there would not be any ...

Graphics, Charts, Images Prohibited In SUGI 29 Working Papers?
Has anyone gone through the entire SUGI 29 Contributed Paper submission process yet? I could be mistaken, but it looks like drafts are 'text only' this year. If true, it'll be interesting to see how the Data Presentation folks get their point across. :-) --> Welcome to the SUGI 29 Call for Papers Web Application --> http://support.sas.com/usergroups/sugi/sugi29/cfp_app_gateway.html "Please write and spell check your abstract, working draft, and bios in a word processor before you enter information about your paper into this application. You can then copy and past...

Lexmark 1020 don't work
I'am using a Lexmark 1020 which doesn't work under Mandrake 9.2. I've tried the driver shipped with Mandrake but it don't works. The config panel only show (after configuring) ready and printing, but nothing happend. On linuxprinting.org I've seen that there is something about HP Deskjet 500C emulation. Have I to install a HP Deskjet 500C instead of Lexmark 1020 (I've tried it, but with no results)? I've downloaded a PPD file, installed it, but it don't works too. Any ideas ? Thank you ! On Tue, 22 Jun 2004 23:36:58 +0200, Damien Fawks thoughtfully wrote: ...

@SFN and @ALTNAME fail
4NT 6.01.232U Windows XP 5.1 [C:\JHESF]echo %@sfn[%@altname[JPHELP_MANUAL.MNL]] C:\JHESF\JPHELP_MANUAL.MNL File system: NTFS. Presumably "version" 5, since junctions work. I tried both @ALTNAME and @SFN by themselves. dir /x shows NO SFN. PDIR /(fn fN) shows the LFN twice. I tried renaming the file to JPHELP.MNL and renaming it back to the LFN, still no SFN. I suspect this is a filesystem problem, however, only 4NT 6.00/6.01 was ever involved in any creation/copy activities. GUI programs never modified the file. -- Steve Steve F�bi�n wrote: > 4...

selecting nodes between other nodes
Hi! <root> <foo>bar</foor> <blah /> <blah2 /> <a> <b /> <c /> </> </root> I want to select everything between <foo> and <a>. Is this possible? Timo In article <2jbjjhFvf4ttU1@uni-berlin.de>, Timo Nentwig <tcn@spamgourmet.org> wrote: % <root> % <foo>bar</foor> % <blah /> % <blah2 /> % <a> % <b /> % <c /> % </> % </root> % I want to select everything between <foo> and <a>. Is this possible? How about <xsl:styles...