[AccessD] Circular reference error in query...

You are right.  I was thinking from my mainframe days that the orderby
needed to specify noprint.... showing my age.  He does have to declare,
in order, where the fields are coming from, and then in order, where to
populate the fields.  It is easier to build the query in access and then
view the SQL to get the syntax down correctly (cheat). 


Karen S. Long
Programmer Analyst
EG&G Technical Services, Inc.
Pittsburgh, PA
Phone: 412-386-6649
Email: cyx5 at cdc.gov


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mcgillivray,
Don [IT]
Sent: Thursday, April 06, 2006 1:30 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Circular reference error in query...

Bobby,

I'm nothing like an expert with SQL, so I probably shouldn't even hazard
a guess here, but I notice that you haven't explicitly stated the fields
being updated in the target table.  Assuming that this is generally
acceptable syntax, and that there's not some default behavior that
attempts to match data by field names (like I said, I'm no expert), your
query may be trying to push into the primary key field of the target
table the first field of the recordset returned by the select clause.
Since that field may contain duplicates, you'd get a key violation upon
execution when the first duplicate is encountered.

I'd try specifying the fields to insert into for the target table:

INSERT INTO wrkSearchAvailable (FieldID, FieldDescr, DBField,
UnionField, ColWidth, ColVisible, UserID)
	SELECT FieldID,FieldDescr,DBField,UnionField,ColWidth, _
		ColVisible,UserID 
	   FROM wrkSearchSelected 
	      WHERE ID IN (11) 
		ORDER BY FieldDescr ASC;

Hope this helps . . .

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: Thursday, April 06, 2006 9:09 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Circular reference error in query...

Hey,

I have the following:

wrkSearchAvailable and wrkSearchSelected tables (defined the same):
ID 		- autonumber/primary key
FieldID	- long/indexed (dupes ok)
FieldDescr	- text(255)/not indexed
DBField	- text(255)/not indexed
UnionField	- text(50)/not indexed
ColWidth	- double/not indexed
ColVisible	- boolean/indexed (dupes ok)
UserID	- text(75)/indexed (dupes ok)


Still here?  Good.

My query is built in VBA and the 11 in the 'IN (11)' is from a listbox -
there's only one in this example.

Here is the query:
INSERT INTO wrkSearchAvailable 
	SELECT FieldID,FieldDescr,DBField,UnionField,ColWidth, _
		ColVisible,UserID 
	   FROM wrkSearchSelected 
	      WHERE ID IN (11) 
		ORDER BY FieldDescr ASC;

This query copies the selected records from wrkSearchSelected to
wrkSearchAvailable.  The problem is that when this runs, I am getting
this
error:

error 3022
'The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or
relationship.
Change the data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit duplicate entries and
try again.'

If I paste the SQL into the QBE, and try to execute it by clicking on
the datasheet button, I get this error message:
"Circular reference caused by alias 'FieldID' in query definition's
SELECT list."

If I execute the query using the Run button (!), I get a message that it
could not update because of key violations.

I appreciate that you have made it this far.  Anyone have any ideas as
to why I am having this issue?

Thanks,
Bobby



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com