Results 1 to 12 of 12
  1. #1
    peter_lawton is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2009
    Location
    London
    Posts
    29

    make table query wants to delete itself

    I want to create a make table query but when I try and run it I get the message "the existing query xxx will be deleted before you run the query" when xxx is the query I am trying to run. So of course it deletes itself then complains it can't run.



    Any explanations or solutions please?

    Peter

  2. #2
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481
    Please post the SQL.

  3. #3
    peter_lawton is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    SELECT HFA_HOK_WriterShare.SongTitleID, HFA_HOK_WriterShare.SongTitle, Sum(HFA_HOK_WriterShare.WritersShare) AS SumOfWritersShare INTO TestTable
    FROM HFA_HOK_WriterShare
    GROUP BY HFA_HOK_WriterShare.SongTitleID, HFA_HOK_WriterShare.SongTitle, HFA_HOK_WriterShare.Publisher
    HAVING (((HFA_HOK_WriterShare.Publisher) Like "House*"));


    Please note - this is from a select query I'm playing with at the moment but I tested at each stage (even though there are only three) so I know elements like sum aren't at fault

    Peter

  4. #4
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79

    Wink

    Anytime you run a make table query it will "overwrite" any previous version of its output table. I have a similar query in a photo archive DB that combines cells together to form a file path. The work around is basically either just accepting the fact that it will delete any previous table, or to rename the previous table to prevent the "overwrite".

    I am currently a little messed up on cold/flu meds, so writing code at the moment is prob not a good idea (can hardly stay awake). Others can help you with adjusting your code as they read your post.

  5. #5
    peter_lawton is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    It's not overwriting the table that is the problem. As noted in first post it is that the query wants to delete itself so it cannot even make the table in the first place. Peter

  6. #6
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79
    Hmm...well upon intial inspection I don't see anything in your SQL code....gonna have to think on this one.

    Anyone else see something I missed?

  7. #7
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79

    Wink

    Peter-

    Try making the change noted in blue text:

    Quote Originally Posted by peter_lawton View Post
    SELECT HFA_HOK_WriterShare.SongTitleID, HFA_HOK_WriterShare.SongTitle, Sum(HFA_HOK_WriterShare.WritersShare) AS SumOfWritersShare INTO TestTable
    FROM HFA_HOK_WriterShare
    GROUP BY HFA_HOK_WriterShare.SongTitleID, HFA_HOK_WriterShare.SongTitle, HFA_HOK_WriterShare.Publisher
    HAVING (([HFA_HOK_WriterShare.Publisher] Like "House*"));
    Adam

  8. #8
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    You don't mention the name of the query. Is it possible you gave the query the name TestTable? Personally I always use a naming convention with the first 3 characters telling me what the object is.

    tbl_ for all tables
    qry_ for all queries .....

  9. #9
    peter_lawton is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    Out of Interest the query is HFA_HOK_PubShare_Query

  10. #10
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    Ok, figured you'd checked that but had to ask. Open the query and look at the SQL View Copy it. Close the query and create a new query paste the SQL View into the new query and see if it does the same thing

  11. #11
    peter_lawton is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    That worked - I wonder why.

    When I'm trying out different approaches I usually use design view as it's easier to see the difference between them. I looked the SQL and couldn't see anything odd but didn't think to copy and paste.

    Thanks Peter

  12. #12
    admessing's Avatar
    admessing is offline GIS DBase Tamer
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Northern CO
    Posts
    79
    Be sure to note that this thread is solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Make Table Query
    By Lpitt56 in forum Access
    Replies: 3
    Last Post: 08-18-2011, 12:27 PM
  2. Replies: 11
    Last Post: 03-30-2011, 01:08 PM
  3. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  4. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums