Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

delete query

Discussion in 'Information Technology' started by me, Jul 28, 2006.

Tags:
  1. me

    me
    Expand Collapse
    Guest

    I get the message "cannot delete from selected tables", when I change a
    select query into a delete query.

    Suppose I have 2 simple tables with identical variables.

    When I receive 1 or more items to update or delete in Table2, I can select
    the corresponding records in Table1 on ItemID and two dates succesfully
    with:

    SELECT Table1.*
    FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
    AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    [Table2].dtDate2);

    However, I can not delete the selected records from Table1 by changing the
    query into a delete query (gives error 3086):

    DELETE Table1.*
    FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
    AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    [Table2].dtDate2);

    Will someone be so kind to help me solving this?
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Assuming lngItemId is the primary key for Table1, try:

    DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
    AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    [Table2].dtDate2))


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "me" <me@localhost> wrote in message
    news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > I get the message "cannot delete from selected tables", when I change a
    > select query into a delete query.
    >
    > Suppose I have 2 simple tables with identical variables.
    >
    > When I receive 1 or more items to update or delete in Table2, I can select
    > the corresponding records in Table1 on ItemID and two dates succesfully
    > with:
    >
    > SELECT Table1.*
    > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
    > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > [Table2].dtDate2);
    >
    > However, I can not delete the selected records from Table1 by changing the
    > query into a delete query (gives error 3086):
    >
    > DELETE Table1.*
    > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
    > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > [Table2].dtDate2);
    >
    > Will someone be so kind to help me solving this?
    >
    >
     
  4. me

    me
    Expand Collapse
    Guest

    Table1 has no primary key, nor has Table2.

    Your suggestion, on my system with for this example only these two tables in
    a empty mdb, leads to deleting all records in Table1 with matching ItemID in
    Table2, instead of only those records satisfying 2 matching dates as well.

    (When I construct primary keys in both tables, based on ItemID, Date1,
    Date2, and a 4th variable, I do not solve the problem though, eg deleting
    wrong records.)

    When executed, the syntax is slightly altered by Access to:
    DELETE *
    FROM Table1
    WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
    ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
    [Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

    What is still wrong?


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
    news:OUcDRDTlGHA.4100@TK2MSFTNGP05.phx.gbl...
    > Assuming lngItemId is the primary key for Table1, try:
    >
    > DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
    > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > [Table2].dtDate2))
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "me" <me@localhost> wrote in message
    > news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > > I get the message "cannot delete from selected tables", when I change a
    > > select query into a delete query.
    > >
    > > Suppose I have 2 simple tables with identical variables.
    > >
    > > When I receive 1 or more items to update or delete in Table2, I can

    select
    > > the corresponding records in Table1 on ItemID and two dates succesfully
    > > with:
    > >
    > > SELECT Table1.*
    > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    [Table2].lngItemID)
    > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > [Table2].dtDate2);
    > >
    > > However, I can not delete the selected records from Table1 by changing

    the
    > > query into a delete query (gives error 3086):
    > >
    > > DELETE Table1.*
    > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    [Table2].lngItemID)
    > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > [Table2].dtDate2);
    > >
    > > Will someone be so kind to help me solving this?
    > >
    > >

    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    The original query you posted was joining the tables on Table1.lngItemID =
    [Table2].lngItemID

    Your new query is joining them on Table1.lngItemID = [Table2].lngID

    Which is correct?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "me" <me@localhost> wrote in message
    news:44999cb1$0$52185$dbd49001@news.wanadoo.nl...
    > Table1 has no primary key, nor has Table2.
    >
    > Your suggestion, on my system with for this example only these two tables

    in
    > a empty mdb, leads to deleting all records in Table1 with matching ItemID

    in
    > Table2, instead of only those records satisfying 2 matching dates as well.
    >
    > (When I construct primary keys in both tables, based on ItemID, Date1,
    > Date2, and a 4th variable, I do not solve the problem though, eg deleting
    > wrong records.)
    >
    > When executed, the syntax is slightly altered by Access to:
    > DELETE *
    > FROM Table1
    > WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN

    [Table2]
    > ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
    > [Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));
    >
    > What is still wrong?
    >
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
    > news:OUcDRDTlGHA.4100@TK2MSFTNGP05.phx.gbl...
    > > Assuming lngItemId is the primary key for Table1, try:
    > >
    > > DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    [Table2].lngItemID)
    > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > [Table2].dtDate2))
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "me" <me@localhost> wrote in message
    > > news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > > > I get the message "cannot delete from selected tables", when I change

    a
    > > > select query into a delete query.
    > > >
    > > > Suppose I have 2 simple tables with identical variables.
    > > >
    > > > When I receive 1 or more items to update or delete in Table2, I can

    > select
    > > > the corresponding records in Table1 on ItemID and two dates

    succesfully
    > > > with:
    > > >
    > > > SELECT Table1.*
    > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > [Table2].lngItemID)
    > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > [Table2].dtDate2);
    > > >
    > > > However, I can not delete the selected records from Table1 by changing

    > the
    > > > query into a delete query (gives error 3086):
    > > >
    > > > DELETE Table1.*
    > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > [Table2].lngItemID)
    > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > [Table2].dtDate2);
    > > >
    > > > Will someone be so kind to help me solving this?
    > > >
    > > >

    > >
    > >

    >
    >
     
  6. DMF

    DMF
    Expand Collapse
    Guest

    I'm following this thread and wonder whether or not there is a glitch in
    Access regarding the delete query. I had a similar problem and followed the
    instructions in help at:
    http://office.microsoft.com/en-us/assistance/HA010345581033.aspx for deleting
    duplicat records. I got the same error message as ME.

    So I build a new database with one table and five records of which only one
    was a duplicate in four of five fields--the fifth being a numeric tie-breaker
    to meet the requirements of the instructions mentioned above. After building
    the two queries, I still get the same error message.

    It would appear that one must build the queries using SQL in order for it to
    work--and even then it apparently does not.

    Why won't the delete query work when the "WHERE" criteria comes from another
    table or query--there are no permission or read-only issues.
    --
    DMF


    "Douglas J Steele" wrote:

    > The original query you posted was joining the tables on Table1.lngItemID =
    > [Table2].lngItemID
    >
    > Your new query is joining them on Table1.lngItemID = [Table2].lngID
    >
    > Which is correct?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "me" <me@localhost> wrote in message
    > news:44999cb1$0$52185$dbd49001@news.wanadoo.nl...
    > > Table1 has no primary key, nor has Table2.
    > >
    > > Your suggestion, on my system with for this example only these two tables

    > in
    > > a empty mdb, leads to deleting all records in Table1 with matching ItemID

    > in
    > > Table2, instead of only those records satisfying 2 matching dates as well.
    > >
    > > (When I construct primary keys in both tables, based on ItemID, Date1,
    > > Date2, and a 4th variable, I do not solve the problem though, eg deleting
    > > wrong records.)
    > >
    > > When executed, the syntax is slightly altered by Access to:
    > > DELETE *
    > > FROM Table1
    > > WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN

    > [Table2]
    > > ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
    > > [Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));
    > >
    > > What is still wrong?
    > >
    > >
    > > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
    > > news:OUcDRDTlGHA.4100@TK2MSFTNGP05.phx.gbl...
    > > > Assuming lngItemId is the primary key for Table1, try:
    > > >
    > > > DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > [Table2].lngItemID)
    > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > [Table2].dtDate2))
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "me" <me@localhost> wrote in message
    > > > news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > > > > I get the message "cannot delete from selected tables", when I change

    > a
    > > > > select query into a delete query.
    > > > >
    > > > > Suppose I have 2 simple tables with identical variables.
    > > > >
    > > > > When I receive 1 or more items to update or delete in Table2, I can

    > > select
    > > > > the corresponding records in Table1 on ItemID and two dates

    > succesfully
    > > > > with:
    > > > >
    > > > > SELECT Table1.*
    > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > > [Table2].lngItemID)
    > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > [Table2].dtDate2);
    > > > >
    > > > > However, I can not delete the selected records from Table1 by changing

    > > the
    > > > > query into a delete query (gives error 3086):
    > > > >
    > > > > DELETE Table1.*
    > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > > [Table2].lngItemID)
    > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > [Table2].dtDate2);
    > > > >
    > > > > Will someone be so kind to help me solving this?
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    >
     
  7. me

    me
    Expand Collapse
    Guest

    Sorry for the confusion. Actually, both are correct. In my effort to solve
    the problem, I created sets of two drastically simplified tables in a new
    database. And I am changing and trying variables, and names, and keys, and
    indexes. Because I do not understand the error, as I have several perfectly
    working deletequeries in my projects.


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
    news:uxMsNbflGHA.3588@TK2MSFTNGP02.phx.gbl...
    > The original query you posted was joining the tables on Table1.lngItemID =
    > [Table2].lngItemID
    >
    > Your new query is joining them on Table1.lngItemID = [Table2].lngID
    >
    > Which is correct?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "me" <me@localhost> wrote in message
    > news:44999cb1$0$52185$dbd49001@news.wanadoo.nl...
    > > Table1 has no primary key, nor has Table2.
    > >
    > > Your suggestion, on my system with for this example only these two

    tables
    > in
    > > a empty mdb, leads to deleting all records in Table1 with matching

    ItemID
    > in
    > > Table2, instead of only those records satisfying 2 matching dates as

    well.
    > >
    > > (When I construct primary keys in both tables, based on ItemID, Date1,
    > > Date2, and a 4th variable, I do not solve the problem though, eg

    deleting
    > > wrong records.)
    > >
    > > When executed, the syntax is slightly altered by Access to:
    > > DELETE *
    > > FROM Table1
    > > WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN

    > [Table2]
    > > ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
    > > [Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));
    > >
    > > What is still wrong?
    > >
    > >
    > > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in

    bericht
    > > news:OUcDRDTlGHA.4100@TK2MSFTNGP05.phx.gbl...
    > > > Assuming lngItemId is the primary key for Table1, try:
    > > >
    > > > DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > [Table2].lngItemID)
    > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > [Table2].dtDate2))
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "me" <me@localhost> wrote in message
    > > > news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > > > > I get the message "cannot delete from selected tables", when I

    change
    > a
    > > > > select query into a delete query.
    > > > >
    > > > > Suppose I have 2 simple tables with identical variables.
    > > > >
    > > > > When I receive 1 or more items to update or delete in Table2, I can

    > > select
    > > > > the corresponding records in Table1 on ItemID and two dates

    > succesfully
    > > > > with:
    > > > >
    > > > > SELECT Table1.*
    > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > > [Table2].lngItemID)
    > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > [Table2].dtDate2);
    > > > >
    > > > > However, I can not delete the selected records from Table1 by

    changing
    > > the
    > > > > query into a delete query (gives error 3086):
    > > > >
    > > > > DELETE Table1.*
    > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > > [Table2].lngItemID)
    > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > [Table2].dtDate2);
    > > > >
    > > > > Will someone be so kind to help me solving this?
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
  8. me

    me
    Expand Collapse
    Guest

    May be I found the answer. After adding DISTINCTROW to the sql-syntax, the
    query in the test database ran fine with muliple runs. The first tests on
    the more complex 'real' tables ran fine as well.

    It was just a guess, changing to Unique Records in queryproperties. After
    all, the SELECT query gave right results in all cases.



    "DMF" <DMF@discussions.microsoft.com> schreef in bericht
    news:A8499ED6-900B-41D4-834A-C864F24DCD1B@microsoft.com...
    > I'm following this thread and wonder whether or not there is a glitch in
    > Access regarding the delete query. I had a similar problem and followed

    the
    > instructions in help at:
    > http://office.microsoft.com/en-us/assistance/HA010345581033.aspx for

    deleting
    > duplicat records. I got the same error message as ME.
    >
    > So I build a new database with one table and five records of which only

    one
    > was a duplicate in four of five fields--the fifth being a numeric

    tie-breaker
    > to meet the requirements of the instructions mentioned above. After

    building
    > the two queries, I still get the same error message.
    >
    > It would appear that one must build the queries using SQL in order for it

    to
    > work--and even then it apparently does not.
    >
    > Why won't the delete query work when the "WHERE" criteria comes from

    another
    > table or query--there are no permission or read-only issues.
    > --
    > DMF
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > The original query you posted was joining the tables on Table1.lngItemID

    =
    > > [Table2].lngItemID
    > >
    > > Your new query is joining them on Table1.lngItemID = [Table2].lngID
    > >
    > > Which is correct?
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "me" <me@localhost> wrote in message
    > > news:44999cb1$0$52185$dbd49001@news.wanadoo.nl...
    > > > Table1 has no primary key, nor has Table2.
    > > >
    > > > Your suggestion, on my system with for this example only these two

    tables
    > > in
    > > > a empty mdb, leads to deleting all records in Table1 with matching

    ItemID
    > > in
    > > > Table2, instead of only those records satisfying 2 matching dates as

    well.
    > > >
    > > > (When I construct primary keys in both tables, based on ItemID, Date1,
    > > > Date2, and a 4th variable, I do not solve the problem though, eg

    deleting
    > > > wrong records.)
    > > >
    > > > When executed, the syntax is slightly altered by Access to:
    > > > DELETE *
    > > > FROM Table1
    > > > WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN

    > > [Table2]
    > > > ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
    > > > [Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));
    > > >
    > > > What is still wrong?
    > > >
    > > >
    > > > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in

    bericht
    > > > news:OUcDRDTlGHA.4100@TK2MSFTNGP05.phx.gbl...
    > > > > Assuming lngItemId is the primary key for Table1, try:
    > > > >
    > > > > DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > > [Table2].lngItemID)
    > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > [Table2].dtDate2))
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "me" <me@localhost> wrote in message
    > > > > news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > > > > > I get the message "cannot delete from selected tables", when I

    change
    > > a
    > > > > > select query into a delete query.
    > > > > >
    > > > > > Suppose I have 2 simple tables with identical variables.
    > > > > >
    > > > > > When I receive 1 or more items to update or delete in Table2, I

    can
    > > > select
    > > > > > the corresponding records in Table1 on ItemID and two dates

    > > succesfully
    > > > > > with:
    > > > > >
    > > > > > SELECT Table1.*
    > > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
    > > > [Table2].lngItemID)
    > > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > > [Table2].dtDate2);
    > > > > >
    > > > > > However, I can not delete the selected records from Table1 by

    changing
    > > > the
    > > > > > query into a delete query (gives error 3086):
    > > > > >
    > > > > > DELETE Table1.*
    > > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
    > > > [Table2].lngItemID)
    > > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > > [Table2].dtDate2);
    > > > > >
    > > > > > Will someone be so kind to help me solving this?
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >
    > >
     
  9. DMF

    DMF
    Expand Collapse
    Guest

    Actually I found a solution after poking around in the Queries Board. For
    some reason queries default to "no" in the unique record property. By
    changing the uinque record property in the query's properties list allows the
    query to be run without the annoying error message. It's a lot easier than
    messing with SQL language.
    --
    DMF


    "me" wrote:

    > Sorry for the confusion. Actually, both are correct. In my effort to solve
    > the problem, I created sets of two drastically simplified tables in a new
    > database. And I am changing and trying variables, and names, and keys, and
    > indexes. Because I do not understand the error, as I have several perfectly
    > working deletequeries in my projects.
    >
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
    > news:uxMsNbflGHA.3588@TK2MSFTNGP02.phx.gbl...
    > > The original query you posted was joining the tables on Table1.lngItemID =
    > > [Table2].lngItemID
    > >
    > > Your new query is joining them on Table1.lngItemID = [Table2].lngID
    > >
    > > Which is correct?
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "me" <me@localhost> wrote in message
    > > news:44999cb1$0$52185$dbd49001@news.wanadoo.nl...
    > > > Table1 has no primary key, nor has Table2.
    > > >
    > > > Your suggestion, on my system with for this example only these two

    > tables
    > > in
    > > > a empty mdb, leads to deleting all records in Table1 with matching

    > ItemID
    > > in
    > > > Table2, instead of only those records satisfying 2 matching dates as

    > well.
    > > >
    > > > (When I construct primary keys in both tables, based on ItemID, Date1,
    > > > Date2, and a 4th variable, I do not solve the problem though, eg

    > deleting
    > > > wrong records.)
    > > >
    > > > When executed, the syntax is slightly altered by Access to:
    > > > DELETE *
    > > > FROM Table1
    > > > WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN

    > > [Table2]
    > > > ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
    > > > [Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));
    > > >
    > > > What is still wrong?
    > > >
    > > >
    > > > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in

    > bericht
    > > > news:OUcDRDTlGHA.4100@TK2MSFTNGP05.phx.gbl...
    > > > > Assuming lngItemId is the primary key for Table1, try:
    > > > >
    > > > > DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
    > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =

    > > [Table2].lngItemID)
    > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > [Table2].dtDate2))
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "me" <me@localhost> wrote in message
    > > > > news:449930c0$0$29130$dbd4b001@news.wanadoo.nl...
    > > > > > I get the message "cannot delete from selected tables", when I

    > change
    > > a
    > > > > > select query into a delete query.
    > > > > >
    > > > > > Suppose I have 2 simple tables with identical variables.
    > > > > >
    > > > > > When I receive 1 or more items to update or delete in Table2, I can
    > > > select
    > > > > > the corresponding records in Table1 on ItemID and two dates

    > > succesfully
    > > > > > with:
    > > > > >
    > > > > > SELECT Table1.*
    > > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
    > > > [Table2].lngItemID)
    > > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > > [Table2].dtDate2);
    > > > > >
    > > > > > However, I can not delete the selected records from Table1 by

    > changing
    > > > the
    > > > > > query into a delete query (gives error 3086):
    > > > > >
    > > > > > DELETE Table1.*
    > > > > > FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
    > > > [Table2].lngItemID)
    > > > > > AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
    > > > > > [Table2].dtDate2);
    > > > > >
    > > > > > Will someone be so kind to help me solving this?
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    >
     

Share This Page