delete query - could not delete from specified table

Hello, 

i am trying to delete (in delete query) records in a table. 

where in the delete query the table is join (3 inner) to a query (select).

i get the message . . . "could not delete from specified table" 

is it posibble ?   where i am wrong ?

Thank's
 
תודה רבה
0
Utf
12/6/2009 9:49:01 AM
access 16762 articles. 3 followers. Follow

5 Replies
931 Views

Similar Articles

[PageSpeed] 12

"יריב החביב" <yarivr@tasmc.health.gov.il> schrieb im Newsbeitrag 
news:BE7CFAB6-2E5E-460B-9D1A-198AA0281090@microsoft.com...
> Hello,
>
> i am trying to delete (in delete query) records in a table.
>
> where in the delete query the table is join (3 inner) to a query (select).
>
> i get the message . . . "could not delete from specified table"
>
> is it posibble ?   where i am wrong ?
>
> Thank's
>
> תודה רבה 

0
Ezra
12/6/2009 10:24:52 AM
The error means that ACCESS cannot identify the unique records that you want 
to delete.

Post the SQL statement of the delete query that you're trying to run, and we 
can show you how to rewrite it so that you can delete the desired records.

-- 

        Ken Snell
http://www.accessmvp.com/KDSnell/


"???? ?????" <yarivr@tasmc.health.gov.il> wrote in message 
news:BE7CFAB6-2E5E-460B-9D1A-198AA0281090@microsoft.com...
> Hello,
>
> i am trying to delete (in delete query) records in a table.
>
> where in the delete query the table is join (3 inner) to a query (select).
>
> i get the message . . . "could not delete from specified table"
>
> is it posibble ?   where i am wrong ?
>
> Thank's
>
> ???? ??? 


0
Ken
12/6/2009 12:58:10 PM
Thank You !

Here is the code,

DELETE BasicSalleryAllValues.*
FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON 
(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND 
(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND 
(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);

-- 
תודה רבה


"Ken Snell" wrote:

> The error means that ACCESS cannot identify the unique records that you want 
> to delete.
> 
> Post the SQL statement of the delete query that you're trying to run, and we 
> can show you how to rewrite it so that you can delete the desired records.
> 
> -- 
> 
>         Ken Snell
> http://www.accessmvp.com/KDSnell/
> 
> 
> "???? ?????" <yarivr@tasmc.health.gov.il> wrote in message 
> news:BE7CFAB6-2E5E-460B-9D1A-198AA0281090@microsoft.com...
> > Hello,
> >
> > i am trying to delete (in delete query) records in a table.
> >
> > where in the delete query the table is join (3 inner) to a query (select).
> >
> > i get the message . . . "could not delete from specified table"
> >
> > is it posibble ?   where i am wrong ?
> >
> > Thank's
> >
> > ???? ??? 
> 
> 
> .
> 
0
Utf
12/6/2009 1:53:01 PM
This should do it:

DELETE *
FROM BasicSalleryAllValues
WHERE EXISTS
    (SELECT *
     FROM QMonth_Key_choice
     WHERE QMonth_Key_choice.Timing_calc 
     = BasicSalleryAllValues.Sub_Timing
     AND QMonth_Key_choice.CalcPeriod
     = BasicSalleryAllValues.Timing
    AND QMonth_Key_choice.FixedDate
    = BasicSalleryAllValues.FixedDate);

The subquery will only return a row where there is a match with the current
row of the outer query, so by using the EXISTS predicate to restrict the
outer query only those rows with matches will be deleted.

When undertaking set operations like this its imperative that you back up the
BasicSalleryAllValues table first of course, until you are absolutely sure
its deleting the correct rows.

Ken Sheridan
Stafford, England

יריב החביב wrote:
>Thank You !
>
>Here is the code,
>
>DELETE BasicSalleryAllValues.*
>FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON 
>(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND 
>(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND 
>(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);
>
>> The error means that ACCESS cannot identify the unique records that you want 
>> to delete.
>[quoted text clipped - 17 lines]
>> 
>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
KenSheridan
12/6/2009 5:16:09 PM
Thank You Very Very Much ! ! !
-- 
תודה רבה


"KenSheridan via AccessMonster.com" wrote:

> This should do it:
> 
> DELETE *
> FROM BasicSalleryAllValues
> WHERE EXISTS
>     (SELECT *
>      FROM QMonth_Key_choice
>      WHERE QMonth_Key_choice.Timing_calc 
>      = BasicSalleryAllValues.Sub_Timing
>      AND QMonth_Key_choice.CalcPeriod
>      = BasicSalleryAllValues.Timing
>     AND QMonth_Key_choice.FixedDate
>     = BasicSalleryAllValues.FixedDate);
> 
> The subquery will only return a row where there is a match with the current
> row of the outer query, so by using the EXISTS predicate to restrict the
> outer query only those rows with matches will be deleted.
> 
> When undertaking set operations like this its imperative that you back up the
> BasicSalleryAllValues table first of course, until you are absolutely sure
> its deleting the correct rows.
> 
> Ken Sheridan
> Stafford, England
> 
> יריב החביב wrote:
> >Thank You !
> >
> >Here is the code,
> >
> >DELETE BasicSalleryAllValues.*
> >FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON 
> >(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND 
> >(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND 
> >(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);
> >
> >> The error means that ACCESS cannot identify the unique records that you want 
> >> to delete.
> >[quoted text clipped - 17 lines]
> >> 
> >> .
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
> 
> .
> 
0
Utf
12/7/2009 2:38:01 PM
Reply:

Similar Artilces:

Delete All Jobs Except 2?
Using something that would work in 2005 and 2008, I am trying to figure out how to delete all jobs except for two. Some runaway scripts have added 40+ crazy-named jobs, and I need to get rid of them all, but keep just two. Can anyone point me to the right queries to capture all jobs into a table var or something better? Thanks. Select * FROM msdb.dbo.sysjobs ~ List all jobs including Job_id In a cursor or loop Delete msdb.dbo.sysjobs where Job_Id = HTH -- RJ Roberts DB Architect/Developer "SnapDive" wrote: > Using something that would work in 2...

Multiple expressions in one query returning errors
Greetings, I am an Access Neophyte, self-teaching as I set up a client database for my company. I am trying to run a query that will return info from 4 expressions; each using a different field. Expression 2 returns the expected data. All the others return "#Error" What am I doing wrong? Expr2: IIf([Pregnant]="Yes","1","0") Expr3: IIf([IV_Drug_User]="Yes","1","0") Expr4: IIf([SACPA_Probation]="Yes","1","0") Expr5: IIf([SACPA_Parole]="Yes","1","0") Thanks so m...

Access 2003
I maintain an equipment inventory database in Access 2003 with just a handful of tables and several regular queries. Each piece of equipment has a unique 6-digit asset tag, and that field is the primary key. No two records can have the same asset tag. Periodically equipment gets replaced, and I am looking for a way to do the following when replacing multiple pieces of equipment. 1. Multiple asset tags would be entered into a form or table. Their corresponding records would be found in TABLE-1 2. Certain fields of the records found would be appended to TABLE-2 showing them as having been repla...

Using SQL Query Code in Form Module to Return Record Count
Hi Team, I am trying to call the following SQL Query from within the Code to return the count of records that meet a criteria where a field value in the Table would be equal to the Value of a Control that is within a Subform: Dim rst As Recordset Dim SQL As String SQL = "SELECT Count(Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskResponsiblePersonRecordID) AS COUNTER " & _ "FROM Tbl_Risk_LocalProfiles_Subform INNER JOIN Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles_Subform.ProfileUpdate_ID = Tbl_Risk_LocalProfiles_ResponsibleP...

Question for Peo-Pivot table
Hello Peo: Thanks for your reply, I have taken out the month as advised, but d you might going a little slower. When I right-clicked on my pivot table there is no 'select average option there for me to click on, also when I right-clicked on the bil day field in my pt and clicked on select group and show detail, then o select group, I get the message "Cannot group that selection". The PT is presently structured as: Bill Day in Row field, Analyst i column field and Current Due in Data field. Thank you very much for your patience. Kem -- Message posted from http://www.ExcelFor...

Count the dates in a specified range of data (column).
I am trying to count the number of dates in a column of data. I am looking for a formula that could count the specified dates. Thanks example: Sep/03 1 oct/03 19 Nov/03 0 Dec/03 6 Example column: 10/24/03 10/24/03 12/2/03 10/24/03 10/24/03 12/2/03 10/24/03 10/24/03 12/2/03 10/24/03 10/24/03 12/2/03 10/24/03 10/24/03 12/2/03 10/24/03 10/24/03 12/2/03 10/24/03 9/23/03 10/31/03 10/31/03 10/24/03 10/10/03 10/10/03 =COUNTIF(Data,"12/22/03") -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Faraj" <FA6@dcx.com> wrote in message ne...

Pivot Table
I have a simple jobs sold list with dates and salesman. I would like to put the date on the page field and then filter by periods like month or quarters. But I dont see anyway for appling an advanced filter for a less than and greater than date. selecting individual date entries would be tedious. Thanks for any suggestions. -- Ingersoll If I understand your question correctly, you could try putting date data in the Row Field. Then, right-click on a date and choose Group and Show Detail. Click Group... Then, choose the Time Periods you want to group the dates by. Click Okay. You will k...

Pivot table changes from 97 to 2000
In Excel 97 on a pivot table you could double click on a data cell in the pivot table and a new worksheet would be created that contains all the data that was being summarized on the pivot table for that particular cell. When I open a pivot table in 2000 that was created in 97, this functionality seems not to exist. Is there a way to enable this functionality in Excel 2000? Any assistance will be greatly appreciated. David Grimsled If you haven't changed any of the settings in the table, it should allow drilling down in Excel 2000, just as it does in Excel 97. To turn the fea...

Deleted mail notification
In a GroupWise system you can receive notification when a recepient deletes messages you have sent them, is this functionality available in Outlook / Exchange? Thanks. _______________________________________________________________________________ Posted Via Uncensored-News.Com - Accounts Starting At $6.95 - http://www.uncensored-news.com <><><><><><><> The Worlds Uncensored News Source <><><><><><><><> In the email select Options and Request read receipt. That'll tell yo...

Windows cannot create object...Specified User already Exists
We just finished our migration to Exchange from Notes. We are using Exchange 2003 on a W2k3 box. One Exchange server, 4 domain controllers. We had an alias called "Everyone" in Lotus Notes. I created a query based distribution group on AD for Everyone. I want it to just be a global distribution group. I deleted "Everyone" and was unable to create a global distribution group using that alias. I got the error that Everyone already exists even though it was deleted already. I tried the Mailbox Cache Age Limit fix and it still gives me the same error. I just tried to...

How do I insert the name of a table into my "master" table?
So I'm doing an append query, appending data from about 300 separate tables into one master table. I want to include the name of the originating table name as a field for each record that I append into the master table. Does anyone out there have any ideas/suggestions on how I would go about doing this, please? Responses are very much appreciated, thanks in advance! Adrienne Are you trying to set up a single append query that appends from ALL 300 tables at once? Are you confident that each of the 300 is "well-formed" and matches up properly to the destination? If creating ...

Where is tables in reports
Is there anyway to get tables into reports? If there is none then is there a work around. The forms I have to make use many block cells. In word tables makes things easy. ...

Lookups Based on Table
I have a table called Products. For simplicity it contains 2 fields. Product and Size. Here is some sample data Coke 1L Coke 6x355ml Coke 2L Pepsi 1L Oreos 500g Here is what I want to do... On a form I want 2 combo Boxes, Product and Size. If I Select Coke from a unique list of Products then Size displays the unique sizes associated with Coke. If I Select 500g then I get the list of unique products that come in that size. Basically linking the combo boxes based on the valyue of the other... This should be easy but it has been too many years for me since I did t...

passing criteria from form to query
I have a form with a list of all the fields in query. Each listed field has a checkbox control beside it. I want the user to be able to select which fields to display by putting a check mark in the appropriate box. How do I pass from the form to the query which boxes have been checked to tell the query to display only those fields in a report or Access spreadsheet? I believe you need code that will build the full SQL of your query. Then use DAO code to update the SQL property of your saved query. There is a sample qbf applet at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook...

Query Help 08-25-10
Dear All i have following DDL and sample data [sql 2000] create table main_tab (t_id varchar(10),fname varchar(30), lname varchar(30), gender char(1)) create table im_tab (t_id varchar(10),flag char(1)) insert into main_tab values('a0101','Ali','Hussain','M') insert into main_tab values('a0102','Noor','Inam','F') insert into main_tab values('a0103','Siddique','Hussain','M') insert into main_tab values('a0104','Farooq','Ali','M') insert into mai...

Delete rows based on multiple criterias
Hi there, I have a task i have to complete weekly which is really starting to bore me!! Basically, i receive a report in excel detailing the SMS messages everybody in the company has sent. I.e. i have a date, senders email, destination, first name, last name, blah blah blah and the actual message sent. Now im only interested in the messages sent by my company and not out sister company. What i have been doing is auto filtering on the Senders email column for the lines im not interested in and manually delete them, thus leaving me with just the ones i want to analyse. My question is, do...

Autonumber deleted IDs resued after Compact?
Hi, thanks in advance... If you were to delete some records from a table with an autonumber PK, will those ID's that were used for the deleted records be available as new ID's after a compact and repair? I'm setting up a notifications system and am worried that with the high volume on the table I may overflow the Long datatype in a few years. I plan to have the notifications table purged on a weekly basis, and I have a scheduled compact/repair on the backend on a monthly basis already... just curious if I can plan on the PK re-using the purged values or if I shou...

Query help 01-25-08
I am trying to write an iif statement for a query but keep getting it wrong. I am pulling from 3 tables, 06-07 projects, 07-09 projects, and project type. I want the query to look like this: 06-07 project # project type 07-09 project # project type 00001 new addition 00002 new HVAC 00003 new roof 00004 new plumbing 00005 new electrical 00006 new flooring 00007 new cabinets 00008 ...

indexing in a table
Hi, Can anyone confirm (or deny) that when indexing in a table is set to 'Yes - No Duplicates' and then you try and append data from a sorted query into that table, DOES THE TABLE ALWAYS ACCEPT THE FIRST ROW (as sorted by the query) or is it more random than that. thanks S On Sun, 28 Feb 2010 14:19:01 -0800, sdg8481 <sdg8481@discussions.microsoft.com> wrote: SQL is not random. In fact it is firmly rooted in mathematical set theory, and all its operations are 100% predictable. If you meant to ask "I have another table that has data that would violate t...

Grand Average in Pivot Table?
Hello, I'm pretty much a Pivot Table idiot, so I might be overlooking something horribly obvious, but I'm trying to have a "Grand Average" column and row in a Pivot Table instead of a "Grand Total." I see the Grand Total checkboxes in the Options dialog box, but I've yet to stumble upon a way to specify a function other than Sum. Is this possible? If so, can someone let me know how? Excel2000. If I didn't explain that clearly enough, let me know and I can give an example. Thanks for the help. Mike When you drag the number header into the data ...

How to hide summary field in Pivot table
Hi I have Pivot table where I had grouped some fields, what I wish to do is to make one group to summarise and other not to. Becouse when I change properites I can only make changes to whole group. When I hide one others hide as well. You could manually hide some of the rows, but you can't change the pivot table to show only some of the subtotals for a field. To hide a row, right-click on a row button, and choose Hide Piotr wrote: > Hi I have Pivot table where I had grouped some fields, what I wish to > do is to make one group to summarise and other not to. > Becouse when I ch...

How do I add on 15 days to a specified date
Column D of my spreadsheet has the dates that I begin various tasks I would like Column E to automatically fix a date 15 days later to be my Target Completion Date Goal. Then I would like Column F to tell me how many days left until the Target Date in Column E The dates in Columns D & E would not change; but the Days Left in Column F would change each day. Please tell me what formulas I should put into Columns E & F Many thanks in advance. ILanger, with date in D2, in E2 put =D2+15 in F2 put =E2-TODAY() and format as general -- Paul B Always backup your data before trying somethi...

pick up particular number from a table
Hi, I have below table: A B C D E 1 04/10 04/10 04/10 04/10 2 AB YZ AB YZ 3 MARY 45 56 16 62 4 PETER 50 48 84 45 5 JOHN 60 78 65 50 How can i get number 45 at B3 when I have reference 04/10, AB, and Mary in another sheet? thanks a lot! -- Lowan sorry, D1 and E1 should be 05/10 -- Lowan "Lowan Chan" wrote: > Hi, > > I have below table: > > A B C D E > 1 04/10 04/10 04/10 04/10 > 2 AB YZ AB YZ > 3 MARY 45 56 16 62 > 4 PETER 50 48 84 45 > 5 JOHN 60 78 65 50 > > How can i get number 45 at B3 wh...

Pivot table calculated field using MAX of another field
How do I get a calculated field to calculate using the MAX of another field? Say I have the following: SIZE COLOR Max of Qty Sum of Cost Sum of QtyXCost large blue 8 5 40 green 2 4 8 red 10 6 60 large Total 10 15 300* medium green 4 11 44 red 5 8 ...

How to let pivot table chart's format stay after refreshing data.
I am using 2007 to do some pivot table pie charts. I selected a red and blue based format from DESIGN tool bar, and change blue to green. after I saved then refreshed data, the blue color comes back. How can I let the green color stay instead of blue? thanks. Hi, It appears that you are changing the color in the wrong place: 1. With a single slice selected choose PivotChart Tools, Format 2. Click Format Selection 3. Pick the Fill tab 4. Select Solid fill 5. Pick a Color and Close the dialog box. If this helps, please click the Yes button. Cheers, Shane Devenshire "JJ" wrote:...