Access database table... very urgent plz.

[artistlikeu]

i have a "roads" geodatabase in access. there is a table "nodes" which has fields ID(auto number), Shape(OLE Object) , Position_X(number) Position_Y(number) and node_number(number). Field node_number has duplicated values. i want to make a new table so that my field node_number do not have duplicate values. when i do it does not allow me. here is query.

SELECT distinct node_number, POINT_X, POINT_Y, Shape FROM nodes;

error message is that i can't have shape with unique value. when i remove distinct it does. i.e following query works.
SELECT node_number, POINT_X, POINT_Y, Shape FROM nodes;

how to solve this problem.....
regards
@rtist

[NeoPa]

I'm not sure you can get around it - that's what the err msg is trying to say.
Grouping on all those fields may work - just an idea.

[MMcCarthy]

i have a "roads" geodatabase in access. there is a table "nodes" which has fields ID(auto number), Shape(OLE Object) , Position_X(number) Position_Y(number) and node_number(number). Field node_number has duplicated values. i want to make a new table so that my field node_number do not have duplicate values. when i do it does not allow me. here is query.

SELECT distinct node_number, POINT_X, POINT_Y, Shape FROM nodes;

error message is that i can't have shape with unique value. when i remove distinct it does. i.e following query works.
SELECT node_number, POINT_X, POINT_Y, Shape FROM nodes;

how to solve this problem.....
regards
@rtist

Because Shape is an OLE Object it is probably causing problems.

As NeoPa says try a GROUP BY first including shape in the group by then excluding it.

 
SELECT node_number, POINT_X, POINT_Y, Shape FROM nodes
GROUP BY node_number, POINT_X, POINT_Y, Shape;
 

or

 
SELECT node_number, POINT_X, POINT_Y, Shape FROM nodes
GROUP BY node_number, POINT_X, POINT_Y;