Duplicated (and edited) query does not behave as expected

[Jack Sheet]

Hi all. I am using Access 2003 with a 2002-2003 file structure.



I have a table T1

I have a select query Q1 for which the sole data source is T1

I have a select query Q2 for which the sole data source is Q1

I have a select query Q3 for which the sole data source is Q2



I wish to create two more select queries Q4 and Q5.

For Q4 the sole data source is to be Q1

For Q5 the sole data source is to be Q4.



Q4 and Q2 are very similar. They use the same fields from Q1, and differ
only slightly in the conditions.



Q3 and Q5 are essentially identical, except for the data source.



In the hope of saving myself some time, I created a duplicate of Q2 and
called it Q4, and I then created a duplicate of Q3 and called it Q5. I then
edited Q5 by changing the data source from Q2 to Q4 (in the properties of
the displayed query at the top half in design view). I was in design view
and as I had hoped the references to the source all automatically changed
across all of the fields, from Q2 to Q4 in the bottom half of the screen.



I then edited Q4 to change the conditions as desired.



I then ran Q5, but it produced exactly the same output as Q3. It is as if
it had disregarded the change of data source that I had entered in Q4 and
continued to refer to Q2 as the data source.



I tried deleting Q5 completely and reconstructing it using the "New query"
wizard, building up precisely the same syntax as the original Q5, and this
time it worked fine (ie referring to Q4, not Q2, as the data source, as
instructed).


Am I going mad? (No lectures, please, about how my database is
inefficiently designed. I know that already). Is there a fundamental
problem with copying queries and then editing them as I did above?

 

[Jack Sheet]

<snip>

PS sorry about the extra line breaks. I drafted the posting in Word, and it
looked OK. Then I selected all of the text copied to clipboard, and pasted
it into a new message window in Outlook, and it still looked OK, so I
clicked send. But then when it bounced back it created all the extra line
breaks. Oh well.

 

[Keith Wilby]

Am I going mad? (No lectures, please, about how my database is
inefficiently designed. I know that already). Is there a fundamental
problem with copying queries and then editing them as I did above?

Just a thought, but have you checked the SQL code to see if the changes were
reflected there?

Keith.
www.keithwilby.com

 

[tina]

I then

edited Q5 by changing the data source from Q2 to Q4 (in the properties of
the displayed query at the top half in design view). I was in design view
and as I had hoped the references to the source all automatically changed
across all of the fields, from Q2 to Q4 in the bottom half of the screen.

sounds like the Name Autocorrect option is turned ON, in your database. if
so, suggest you turn it OFF, via Tools | Options | General tab. or better
yet, create a new blank database, immediately turn OFF Name Autocorrect,
compact the db, then import all the objects from your "working" db into the
new one, compile all modules, and compact again. for more information on the
problems with Name Autocorrect, see
http://home.att.net/~california.db/tips.html#aTip3.

hth

 

[Jack Sheet]

Thanks, but the Name Autocorrect feature was already turned off.
I now find that I have the same problem with a Form:
I duplicated an existing form, and changed the data source to a different
select query, but it still refers to the original select query of the form
that I copied.

 

[Jack Sheet]

Belay that - my fault re. second (Form) problem.

Thanks, but the Name Autocorrect feature was already turned off.
I now find that I have the same problem with a Form:
I duplicated an existing form, and changed the data source to a different
select query, but it still refers to the original select query of the form
that I copied.