Hi There,
I am trying to append address data from a table which I imported from excel and have created a number of lookup tables from (e.g. Town, StreetName, etc.) and want to append the original data into a new table linking to the lookup tables.
I know that the ID field on the new tables have number id field (i.e. the append is mismatching it appears).
I have seen a post from back in 2012 about this that suggests I use the id field in the append to field but while I see the lookup table and the original table and can append to the new address table, I can't select the append to the lookup id field.
I have set my outer joins and can see the data in View as I want it, but can't transfer it.
This is what I have at the moment. Any support would be much appreciated, I haven't used Access for so long, I am sure there is a sensible solution just round the corner.
This is how it looks so far (I have added the spaces here for reading only:
INSERT INTO tblAddresses ( Add2_BuildingName, Add1_BuildingNum, Add3_ResidenceRef, Add4_Close_Other_Identifier, Add5_StreetRoad, Add7_Town, Add8_Postcode, Polling_Area, Add9_Non_Postal_Indicator )
SELECT [Newsome Ward Data].Buiding_Name, [Newsome Ward Data].[Building No], [Newsome Ward Data].Residence_Ref, [Newsome Ward Data].[Close/Group without formal road], [Newsome Ward Data].Street_Road, [Newsome Ward Data].Town_City, [Newsome Ward Data].PostCode, [Newsome Ward Data].[Elector Number Prefix], [Newsome Ward Data].Non_Postal_Indicator
FROM tblNonPostalAddressesNames RIGHT JOIN (tblElectorialArea RIGHT JOIN (tblPostcodes RIGHT JOIN (tblTown RIGHT JOIN (tblStreetRoad RIGHT JOIN (tblBuildingNames RIGHT JOIN [Newsome Ward Data] ON tblBuildingNames.BuildingName = [Newsome Ward Data].Buiding_Name) ON tblStreetRoad.StreetRoadName = [Newsome Ward Data].Street_Road) ON tblTown.TownName = [Newsome Ward Data].Town_City) ON tblPostcodes.Postcode = [Newsome Ward Data].PostCode) ON tblElectorialArea.Electorial_Data_Area = [Newsome Ward Data].[Elector Number Prefix]) ON tblNonPostalAddressesNames.NonPostalAddressName = [Newsome Ward Data].Non_Postal_Indicator;
I am trying to append address data from a table which I imported from excel and have created a number of lookup tables from (e.g. Town, StreetName, etc.) and want to append the original data into a new table linking to the lookup tables.
I know that the ID field on the new tables have number id field (i.e. the append is mismatching it appears).
I have seen a post from back in 2012 about this that suggests I use the id field in the append to field but while I see the lookup table and the original table and can append to the new address table, I can't select the append to the lookup id field.
I have set my outer joins and can see the data in View as I want it, but can't transfer it.
This is what I have at the moment. Any support would be much appreciated, I haven't used Access for so long, I am sure there is a sensible solution just round the corner.
This is how it looks so far (I have added the spaces here for reading only:
INSERT INTO tblAddresses ( Add2_BuildingName, Add1_BuildingNum, Add3_ResidenceRef, Add4_Close_Other_Identifier, Add5_StreetRoad, Add7_Town, Add8_Postcode, Polling_Area, Add9_Non_Postal_Indicator )
SELECT [Newsome Ward Data].Buiding_Name, [Newsome Ward Data].[Building No], [Newsome Ward Data].Residence_Ref, [Newsome Ward Data].[Close/Group without formal road], [Newsome Ward Data].Street_Road, [Newsome Ward Data].Town_City, [Newsome Ward Data].PostCode, [Newsome Ward Data].[Elector Number Prefix], [Newsome Ward Data].Non_Postal_Indicator
FROM tblNonPostalAddressesNames RIGHT JOIN (tblElectorialArea RIGHT JOIN (tblPostcodes RIGHT JOIN (tblTown RIGHT JOIN (tblStreetRoad RIGHT JOIN (tblBuildingNames RIGHT JOIN [Newsome Ward Data] ON tblBuildingNames.BuildingName = [Newsome Ward Data].Buiding_Name) ON tblStreetRoad.StreetRoadName = [Newsome Ward Data].Street_Road) ON tblTown.TownName = [Newsome Ward Data].Town_City) ON tblPostcodes.Postcode = [Newsome Ward Data].PostCode) ON tblElectorialArea.Electorial_Data_Area = [Newsome Ward Data].[Elector Number Prefix]) ON tblNonPostalAddressesNames.NonPostalAddressName = [Newsome Ward Data].Non_Postal_Indicator;