make table query wants to delete itself

[peter_lawton]

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

[hertfordkc]

Please post the SQL.

[peter_lawton]

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

[admessing]

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.

[peter_lawton]

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

[admessing]

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?

[admessing]

Peter-

Try making the change noted in blue text:

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

[RayMilhon]

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 .....

[peter_lawton]

Out of Interest the query is HFA_HOK_PubShare_Query

[RayMilhon]

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

[peter_lawton]

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

[admessing]

Be sure to note that this thread is solved.