Could not delete from specified tables

I created the following query but I get the "Could Not Delete From
Specified Tables" error.  Although I have access to the table and can
delete from that table manually.

my query is as follows:

DELETE DISTINCTROW tblSatAdv.*
FROM tblSatAdv

INNER JOIN
	(
	SELECT Max(tblSatAdv.SnapshotInvDte) AS MaxOfSnapshotInvDte
	FROM tblSatAdv
	) AS MAXDTE
 ON tblSatAdv.SnapshotInvDte = MAXDTE.MaxOfSnapshotInvDte;
0
ISUTri
11/19/2007 9:57:41 PM
access 16762 articles. 3 followers. Follow

3 Replies
1930 Views

Similar Articles

[PageSpeed] 38

It is not possible to delete using a aggregate sub Query as a joined "table"

Pieter

"ISUTri" <GraberJ@gmail.com> wrote in message 
news:ce6eaad7-6db5-4f03-94f2-b9cdb6420f8b@d50g2000hsf.googlegroups.com...
>I created the following query but I get the "Could Not Delete From
> Specified Tables" error.  Although I have access to the table and can
> delete from that table manually.
>
> my query is as follows:
>
> DELETE DISTINCTROW tblSatAdv.*
> FROM tblSatAdv
>
> INNER JOIN
> (
> SELECT Max(tblSatAdv.SnapshotInvDte) AS MaxOfSnapshotInvDte
> FROM tblSatAdv
> ) AS MAXDTE
> ON tblSatAdv.SnapshotInvDte = MAXDTE.MaxOfSnapshotInvDte; 


0
Pieter
11/19/2007 10:13:39 PM
On Mon, 19 Nov 2007 13:57:41 -0800 (PST), ISUTri <GraberJ@gmail.com> wrote:

>I created the following query but I get the "Could Not Delete From
>Specified Tables" error.  Although I have access to the table and can
>delete from that table manually.
>
>my query is as follows:

Alas, no Totals query is ever updateable. You can get around it by using
DMax() rather than a subquery:

DELETE DISTINCTROW tblSatAdv.*
FROM tblSatAdv
WHERE SnapshotInvDte = DMax("ShapshotInvDte", "tblSatAdv");

This will delete either a single record, or that set of records tied for the
maximum SnapshotInvDte. If you want to delete the maximum record for each
<some field> you'll need a third argument to the DMax to specify the grouping.


             John W. Vinson [MVP]
0
John
11/20/2007 2:15:58 AM


On Nov 19, 8:15 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Mon, 19 Nov 2007 13:57:41 -0800 (PST), ISUTri <Grab...@gmail.com> wrote:
> >I created the following query but I get the "Could Not Delete From
> >Specified Tables" error.  Although I have access to the table and can
> >delete from that table manually.
>
> >my query is as follows:
>
> Alas, no Totals query is ever updateable. You can get around it by using
> DMax() rather than a subquery:
>
> DELETE DISTINCTROW tblSatAdv.*
> FROM tblSatAdv
> WHERE SnapshotInvDte = DMax("ShapshotInvDte", "tblSatAdv");
>
> This will delete either a single record, or that set of records tied for the
> maximum SnapshotInvDte. If you want to delete the maximum record for each
> <some field> you'll need a third argument to the DMax to specify the grouping.
>
>              John W. Vinson [MVP]

Thanks!  That worked!
0
ISUTri
11/20/2007 2:18:33 PM
Reply:

Similar Artilces:

How to delete or permantly change logo in personal settings
When I was just learnng how to do publisher I created a logo and Iwant to change it in the permanant settings for the personal information. I cannot delete it or edit it. Also on the other 3 settings for secondary, home etc. it does not alowany logo at all. Anyone know? Locate the following files and delete them: Primary Business - biz1logo.jsp Secondary Business - biz2logo.jsp Other Organization - Orglogo.jsp Home/Family - Perslogo.jsp -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no ...

Pivot Table Help #3
I have a lot of data that I am trying to analyze with a pivot table and am not sure how to go about it. Columns are (1) District (2) Store # (3) 2003 Score - these are #s or text ("incomplete") (4) 2004 Score - these are #s or text ("incomplete") For each district, I am trying to find out 3 things: (1) % of stores incomplete (2) Average score for 2003 & 2004 - I've got this one working properly (3) % change between 2003 & 2004 I can successfully analyze the data in a spreadsheet but there is too much to go through and thought a pivot table was the way to ...

Subtract colums in pivot table
I have a pivot table that has the following characteristics (Excel 2007): -rows (down the left) are values: "# Employees", "Total Pay" -Columns (across top) are Dates I want to calculate the difference between different date columns. Example: I have: Date 5/23/2010 5/16/2010 5/24/2009 Total # Emp 10 15 5 30 Pay 1000 15000 500 16500 I'd like: Date 5/23/2010 5/16/2010 5/24/2009 Total Vs. Last Wk % Change Vs. Last Yr % Change # Emp 10 15 5 30 -5 -33% 5 33% Pay 1000 5000 250 6250 -4000 -80% 750 15% Can anyone tell me how to cre...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

Delete partial string
Hello! I have a column with strin in cells 26-02-2004 09:27 jhfkfhg fhgfjfg 26-02-2004 10:28 ewqteter rerewewtrre I know delete date/hour group with Replace, but I need inverse. Preserv date/hour group and delete only alfanumeric string. Is possible in Excel? Thanks in advance. an Hi one way (if your data is always structured as below) - add a helper column (lets say B) - insert the following formula in B1 (if column A stores your data): =LEFT(A1,FIND(":",A1)+2) copy down - now you can copy this row and insert it again with 'Paste Special - Values' -- Regards Frank K...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

deleted document
I have deleted an important document and need to retrieve it. The document deleted was on our server and I don't know how to find it and restore it. Contact your IT staff and ask them to restore it from the last backup. But try to find out what share it was on (and folder and filename). It might mean a quicker response. dcalhoun wrote: > > I have deleted an important document and need to retrieve it. The document > deleted was on our server and I don't know how to find it and restore it. -- Dave Peterson Hope your network administrator does regular backups (like da...

Renaming table in a dB
Is there a short way in which i can modify all references to a table after i rename it? Or would i have to open every query and form and manually change the table references?Thanksramesh Access doesn't provide a way to do this.There are commercial products that do, e.g.: http://www.speedferret.com/-- Allen Browne - Microsoft MVP. Perth, Western AustraliaTips for Access users - http://allenbrowne.com/tips.htmlReply to group, rather than allenbrowne at mvps dot org."Ramesh" <ramesh2020@gmaildotcom> wrote in messagenews:uGgN$EuZHHA.4000@TK2MSFTNGP02.phx.gbl...> Is the...

Getting back a deleted test frame after saving the changes
My son just deleted a text frame with all his work and started a new text frame and then when asked if he wanted to save his work he clicked yes. Is there any way to retrieve that missing text frame when it won't allow you to undo? On Sat, 27 Aug 2005 16:04:01 +0100, Denise wrote (in article <09056FDE-03FC-4988-8AB0-D1EA0896BFC7@microsoft.com>): > My son just deleted a text frame with all his work and started a new text > frame and then when asked if he wanted to save his work he clicked yes. Is > there any way to retrieve that missing text frame when it won't a...

Deleting Blank lines with VBA
Help I have a macro that runs multiple formulas. However, when the formulas are finished I am left with about 64000 blank lines. I am unable to insert a row on the worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank Cell using CTRL-END). Here is a same of some of the formulas I am running ( the macro is very large). ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27" ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28" ActiveWorkbook.Names.Add Name:="TrunkFormulaK&qu...

Deleting a excel file
Thanks for reading. I'm on a network at work, no signing in needed on the computer. Is there a way I can make a Excel file or folder and have where others can't delete it? I know how to protect it where the file can't be changed, but sometimes someone will delete it. I know this is the Windows 2000 security question but hope to fine the answer here, I'm lost. Thanks Much Bob Talk to your IT staff. Ask them to give you a share or folder on that network that only you (and a trusted co-worker) have write access to. But make...

Still recieving e-mail for deleted users
We are receiving e-mails for users that were been deleted from our server years ago. Does anyone know how to block these e-mails. We have a SPAM program that allows you to blacklist but it does not stop the messages from coming through. Create ea distribution list containing no members, but which has all the long-gone users' smtp addresses. This is called a blackhole DL. Exchange will accept the mail but silently drop it - no NDRs, no storage. -- hth, SusanV "RB" <pr@epley-pr.com> wrote in message news:574a01c40071$05a154a0$a501280a@phx.gbl... > We are receiving...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

ACCESS 2000 is deleting records
Just yesterday I converted an Access 97 database to Access 2000. Have a large problem I need help with. After converting the DB I "split it" in order to use it as a backend, same way it was in Access97. After converting the database to Access2k we began to have a problem with Access2k deleting records. it deleted a total of 4 out of 62 records. the records are not together ( record #289365 then 289048 then 289128 then 289178 then this morning 289405) these record are auto-numbered incerement of one. They were entered by different people so I know its not just an operator probl...

Sumif across a table
I am looking for a function that works using a =sumif function to add things that are not in a range that are next to each other as seen below the letters in () are the columns that the values are in... so I am looking for a sum in column A "X" of the total work out time if the appl column is "Y" total work Running (D) Walking (F) Elliptical (H) out time (C) Appl (D) Time (E) Appl (F) Time (G) Appl (H) Time (I) X Y 20 N 0 Y ...

Dummy series and data table
Hi, I have a chart that presents 2005, 2006, 2007 summary data as a column chart and then 2007 by month as a line. To show the yearly data I have a yearly category, after which I have individual months where the yearly data is zero - sort of like a dummy series - because I only have one value for them. The 2007 detailed data has zero in the yearly column but all the individual values in the monthly columns. It worked fine until I was asked to add a data table to the chart. Now, since it has 2007 twice - once as the summarized for the year and the other as all these individual months - some us...

Aging report table for accounts receivable
Can somebody tell me what is the table for a/r aging..I want to make query/view in sql server 2005 Michael, You need to use RM20101 and RM10201 tables. You need to use date functions to get the aging for your view based on document date or due date field. If your aging is setup to be by Doc Date, Consider Doc Date and If it is due date, you should be taking it by Due Date. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Michael@nyresume.com" wrote: > Can somebody tell me what is the table for a/r aging..I want to make > query/view in sql server 2005...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...

Can't Delete Rules
I have a user with some rules but the rules can't be deleted. I got two error message and one is "The rules on this machine do not match the rules on your exchange server" and ask "Which rules do you want to keep" Client, Server, Cancel. I can delete Client rules and also Server rules, but they are all back once I restart Outlook. The other error message is "one or more rules could not be uploaded to Exchange server and have been deactivated." Is there any way I can remove all of the rules? Thanks for your comments in advance. Start-> Ru...

Office X 'clean' reinstall: delete all old files?
I'm about to try installing the latest bug-fix collection, sorry. "security update" for Office X, 10.1.6. I'm hoping it might reduce the frequency of Word's charming crashes. Who knows, I might get lucky and it'll even fix the completely messed up 'contact' feature in Word that is grayed out most of the time, but when it decides to work, will only list the 'A's' from my address book. Just useless. Anyway, back to the plot: since my Office X is so bent out of shape, I figure I'll do a 'clean install' from the original disk first, then...

pivot tables #3
I am trying to change the order in how the tables display. I don't want an accending or decending alpha order as it is set up now. I would like to pick and choose how I want them listed. How do I change the order? ...

pivot table, How to add 1 column
I just need to add one column only but pivot table create another one? I thinkc because I have a column with 2 parameters. I can turn on / off with the field drop down menu How can I get around with this problem? Thanks Daniel ...

delete dupl from 1 table that match table 2
I have 2 tables joined by an acct # but diff data in the 2 other columns. I want to delete the row of information from table A that have a matching acct # in table b. ie: appl acct # amount (table a) appl acct # amount (table b) b 1234 $1.00 b 1234 $5.00 c 111 $1.00 c 12345 $5.00 c 1001 $2.00 c 1001 $3.00 want to delete rows from table a. for accts #1234 & #1001 DELETE [Table A].[Acct #] ...

deleted or filed emails keep reappearing into inbox
Hello, I have a user that when ever he deletes an email or even if he files an email and moves it to a folder, a few days later that email keeps coming back to his inbox. He is using Outlook2007, is connected to Exchange 2007 and he's also a blackberry user on BES 5.0. I have tried the following: - recreating his profile in Outllook , -checked on his blackberry to make sure Mailbox wins on conflicts,- - he has no rules in Outtlook -turn on/off the Cashed mode Any ideas? ...

specify DC when creating a RUS?
According to every KB article i read you can't specify a DC when creating a RUS, but i'm trying to create a RUS for a child domain and exchange is picking up a 'former' domain controller...i thought i'd re-domainprep the child domain to fix, but no luck..this is the scenario: Our child domain was blown away and restored by some lovely remote staff, and it didn't go so well. Anyhow, i'm trying to create a new recipient update service for the remote child domain and its defaulting to the name of a DC that no longer exists and I dont' have an option to chang...