relation problems

S

SuzyQ

I have the following tables

GISRoads

Fnode_ Double 8
Tnode_ Double 8
Lpoly_ Double 8
Length Double 8
Rdall_ Double 8
Rdall_id Double 8
Entity_Lab Text 7
Lanes Integer 2
Road_Width Integer 2
Road_name Text 50
Road_Alias Text 50
Unique_id Double 8
Rt_Of_Way Integer 2
District Text 10
Cty_Rd_No Text 7
TR_A_C_L Text 50
Bufferdist Integer 2
City Text 5
Surfacetyp Text 5

and

Roads

DistrictID Text 2
RoadKey Text 7
RoadNumber Text 10
RoadName Text 50
State_ID_No Double 8
SurfaceType Double 8
Description Text 75
BeginMilePost Single 4
EndMilePost Single 4
Length Single 4
Width-pavement Single 4
Width-shoulder Long 4
RightOfWay_Deeded Single 4
RightOfWay_Perscriptive Single 4
Subdivision Text 50
Condition Long 4
RuralFunctionalClassification Long 4
IncludeRoadStabilization yes/no 1
RoadStabilizationMiles double 8
Location Text 25

GISRoads is a .dbf table of an ESRI shape file.
Roads is a native Access table (Version 2003)

I have two fields that are common in the two tables and they are

GISRoads.Unique_id contains the same data, and is the same data type as
Roads.State_ID_No (Double)

GISRoads.Cty_Rd_No contains the same data, and is the same data type and
size as Roads.RoadKey (Text)

I'm trying to verify the integrity of this data by comparing the two tables.
I can create a query linking the two tables on unique_id -> State_id_no but
I can't link the tables using the text fields cty_rd_no -> roadKey I don't
get any errors, but no records either when I know there should be.

Originally I just opened a new database project and linked to the esri dbf
and linked to my roads table in another databse, but that didn't work, so I
imported the esri dbf, still no luck, so I imported the roads table as well
so that I can make modifications to the data types if necessary (without
disturbing the original tables ) to match fields so that I can link the two
tables together inependently on each of the two fields.

RoadKey is unique and is primary key to the Roads table
This fields should link to the cty_rd_no field which is not unique in that
table because of multiple segments of the same road.


Unique_Id is not unique in the GIS table, but is unique to a specific road
(not unique in the table because a gis road can be made up of multiple
segments, but is still the same road)

The state id's the roads with one number regardless of the road names, our
county road id's change with a change of a road name that's why I need to
check on both fields to make sure nothing is missing and everything is as it
should be. So in our roads table two different roads could have the same
state unique id. Anyway all I need to do is link the two table on RoadKey -
Cty_rd_no a text field size 7.

SELECT GISRoads.UNIQUE_ID, GISRoads.CTY_RD_NO, GISRoads.ROAD_NAME,
GISRoads.ROAD_ALIAS, roads.RoadKey, roads.[State ID No], roads.RoadName
FROM GISRoads INNER JOIN roads ON GISRoads.CTY_RD_NO = roads2.RoadKey;

This is my sql statement from the query builder. Does anyone know any
reason why this is not working with the specified tables?
 
J

Jerry Whittle

I'm making a WAG that there might be spaces in those fields either on one
side or another. The data could look the same, but the spaces could make a
difference.

SELECT GISRoads.UNIQUE_ID,
GISRoads.CTY_RD_NO,
GISRoads.ROAD_NAME,
GISRoads.ROAD_ALIAS,
roads.RoadKey,
roads.[State ID No],
roads.RoadName
FROM GISRoads INNER JOIN roads
ON Trim(GISRoads.CTY_RD_NO) = Trim(roads2.RoadKey);

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


SuzyQ said:
I have the following tables

GISRoads

Fnode_ Double 8
Tnode_ Double 8
Lpoly_ Double 8
Length Double 8
Rdall_ Double 8
Rdall_id Double 8
Entity_Lab Text 7
Lanes Integer 2
Road_Width Integer 2
Road_name Text 50
Road_Alias Text 50
Unique_id Double 8
Rt_Of_Way Integer 2
District Text 10
Cty_Rd_No Text 7
TR_A_C_L Text 50
Bufferdist Integer 2
City Text 5
Surfacetyp Text 5

and

Roads

DistrictID Text 2
RoadKey Text 7
RoadNumber Text 10
RoadName Text 50
State_ID_No Double 8
SurfaceType Double 8
Description Text 75
BeginMilePost Single 4
EndMilePost Single 4
Length Single 4
Width-pavement Single 4
Width-shoulder Long 4
RightOfWay_Deeded Single 4
RightOfWay_Perscriptive Single 4
Subdivision Text 50
Condition Long 4
RuralFunctionalClassification Long 4
IncludeRoadStabilization yes/no 1
RoadStabilizationMiles double 8
Location Text 25

GISRoads is a .dbf table of an ESRI shape file.
Roads is a native Access table (Version 2003)

I have two fields that are common in the two tables and they are

GISRoads.Unique_id contains the same data, and is the same data type as
Roads.State_ID_No (Double)

GISRoads.Cty_Rd_No contains the same data, and is the same data type and
size as Roads.RoadKey (Text)

I'm trying to verify the integrity of this data by comparing the two tables.
I can create a query linking the two tables on unique_id -> State_id_no but
I can't link the tables using the text fields cty_rd_no -> roadKey I don't
get any errors, but no records either when I know there should be.

Originally I just opened a new database project and linked to the esri dbf
and linked to my roads table in another databse, but that didn't work, so I
imported the esri dbf, still no luck, so I imported the roads table as well
so that I can make modifications to the data types if necessary (without
disturbing the original tables ) to match fields so that I can link the two
tables together inependently on each of the two fields.

RoadKey is unique and is primary key to the Roads table
This fields should link to the cty_rd_no field which is not unique in that
table because of multiple segments of the same road.


Unique_Id is not unique in the GIS table, but is unique to a specific road
(not unique in the table because a gis road can be made up of multiple
segments, but is still the same road)

The state id's the roads with one number regardless of the road names, our
county road id's change with a change of a road name that's why I need to
check on both fields to make sure nothing is missing and everything is as it
should be. So in our roads table two different roads could have the same
state unique id. Anyway all I need to do is link the two table on RoadKey -
Cty_rd_no a text field size 7.

SELECT GISRoads.UNIQUE_ID, GISRoads.CTY_RD_NO, GISRoads.ROAD_NAME,
GISRoads.ROAD_ALIAS, roads.RoadKey, roads.[State ID No], roads.RoadName
FROM GISRoads INNER JOIN roads ON GISRoads.CTY_RD_NO = roads2.RoadKey;

This is my sql statement from the query builder. Does anyone know any
reason why this is not working with the specified tables?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top