relation problems
in [General]
|
From: SuzyQ on 20 Apr 2010 12:26 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?
From: Jerry Whittle on 20 Apr 2010 14:36 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" wrote: > 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? >
|
Pages: 1 Prev: MS Access 2003 Locks Up When Running mdb Developed In MS Access 20 Next: fatura |