Append Query using lookup tables with different data types

GideonR

New member
Local time
Today, 11:29
Joined
Apr 28, 2019
Messages
8
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;
 
Not following. Which ID from where needs to go where? Maybe show some sample data (dummy addresses of course).
 
Thanks for coming back June7

I'll clarify

Original table is from Excel and has fields such as Town, Street, Close, building name, etc. to build up a comprehensive address, along with other private data

NDATA (Table derived from the original data)

BuildingName (text string)
Town (text string)
Postcode (text)

I have set up separate tables for the fields to remove duplicates, so I now have:

tblTown
townID (autonumber)
townname (short text)

tblPostcode
postcodeID (autonumber)
postcode (short text)

tblBuildingName
buildingnameID (autonumber)
buildingname (autonumber)

There are a number of other similar tables to reduce duplicates, as the query sql showed

Each of the above tables have been linked (lookup) to a new table called tblAddresses, which is were I want to append the data to:

tblAddresses
Add2BuildingName (lookup to tblBuildingName)
Add7Town (lookup to tblTown)
Add8Postcode (lookup to tblPostcode)

etc.

I realise the lookup in tblAddresses are number fields but the data in NDATA is text.

Reading a very old post it suggested I needed to join the text field (e.g. tblTown.townname outer join (in my case) to Town (NDATA) and then I needed to use the lookup table number field (e.g. townid as the append to.

However. I do not get any option to do that, I only get the tblAddresses fields to select from (which makes sense as it is where the appended data is going).

As I have said I see the correct data in the View (query) but the mismatch of data type won't let it transfer the data into the new table. With approx 12000 rows of data in the original that is a lot of duplicate data.

Does that help?

GideonR
 
First of all, I strongly recommend you change the field names removing all spaces and special characters such as '/'. Personally I would also scrap all the underscores as they are just unnecessary extra typing. Similarly do you really need the prefixes Add1_ etc
Instead use CamelCase. The Postcode field is fine.

Next do you really need lookup tables for Town, StreetName etc
When you want to search for e.g. York or High Street, just add these as filter criteria in a query of tblAddresses.

I believe you are saying that your query works as a SELECT query e.g. in datasheet view. If so, the problem will be in the INSERT clause.
There are 8 fields in the INSERT clause but 9 fields in the SELECT part...if my counting is correct.
Identify the discrepancy and add or remove a field as appropriate
If your PK field in tblAddresses is an autonumber, then exclude it from your append query. If its a number field, then you must include that for the append to work.

As you are UK based, you may find it worth looking at the evaluation version of my Http://www.mendipdatasystems.co.uk/uk-postal-address-finder/4594138311UK Postal Address Finder application.

Finally I typed this before realising you had replied to June. Apologies if you've covered any of the above points already
 
Hi Isladogs,

OK thanks for the thoughts on syntax, as I said in my intro I am very rusty. the "/" was legacy from the import as were the spaces (excel)

The AddNo at the beginning is to try and get some understanding and order into data that is confused when I get it.

I only want to use the original table to move the data across into the new tables. It is my intention to scrap it after that.

In terms of lookups - I probably don't need towns but for streets and other fields I am trying to manage other users putting in rubbish data, so it is as much about management as it is about deleting duplicates.

I'll check the query for matching data

I'll take a look at the link as well

OK I think I understand you in terms of autonumber but in tblAddresses the fields are number fields. I believe the number field in the tblAddresses is being selected in the append to but the data in the equivalent original table is text and not a number. Please see my reply to June7. I think I will come across this issue as I go along building this database if I don't know how to fix it now.

GideonR
 
The datatypes of all fields being appended needs to be the same as those used for the destination fields. If not you will get a datatype mismatch and the append will fail. For example, you cannot append text field data to a number field.

The comment about autonumber field only applies to the primary key field.

I'm still far from convinced that using a lookup table will prevent duplicate data or nonsense data. You could use a list of postcodes (approx. 2.6 million) to reduce the risk of nonsense or even obtain a complete list of all UK Addresses (approx. 28 million).

To prevent duplicates you should use indexes on appropriate fields or you could use the unique UDPRN for each individual UK address
 
isladogs,

I do understand the requirement for matched data, but if I have a table with a look up field but want to populate the data from a third table's data which doesn't match but provides the result I need then how is that done?

Example:

tblThings has 2 fields ID (number) and Things (text), if Things field is joined to a look up table tblList (list of things to choose from) with an ID (number) and Things descriptor (text).

If I want to populate the tblThings from some previous set of data in a field (say imported from excel) and that data is text and matches the Things descriptor, then how do you append it or is that impossible to work around. I know the field that joins tblList (ID) to tblThings will be number but visually I want the user to see the Things descriptor (text).

Does that make sense? I appreciate that we all have different ways of developing and managing databases, but for now I would like to resolve this problem because it could be needed for other inputs which are more sensitive and has potentially 12000 rows of data which I don't want to have to type in.

I do hope you can help! It is much appreciated.

GideonR
 
Using 'Things' at this stage doesn't help clarity.
As already suggested by June, please post some example data. It doesn't need to be real but make sure it is realistic.
Then one of us can make appropriate suggestions if we can see a better alternative.

As you have less than ten posts, you will need to zip your files.
 
Last edited:
OK Thanks isladogs, I will post something a bit later when I put it together.

How do I post a zip file?

GideonR
 

Users who are viewing this thread

Back
Top Bottom