Importing into a database with prior relationships

[pclutts]

I am trying to import an Excel file to a temporary table (tblARRAProjects) in Access and then use an append query to append the applicable information to the existing table (tblProjects). Two of the fields in tblProjects (District and Route) that will be 'appended to' are lookup fields that get their information from two other tables (tblDistricts and tblRoutes). There are only two fields each in each of these other tables. The two fields in tblDistricts are 'ID' (key field) and 'District'. The two fields in tblRoutes are 'ID' (key field) and 'Route'. Each of these lookup fields store the District and Route info respectively in tblProjects and not the ID info. In the Query Designer when I set up the Append Query, what do I put in the 'Append To' row for the District and Route fields? Do I need to somehow include the ID numbers from tblDistricts and tblRoutes to make it work. As it stands now, the append query will add the the records from the temporary table but the 'Route' and 'District' fields are not appended. Thanks in advance for any help.

 

[RuralGuy]

Have you already looked at this link?

 

[pclutts]

Thanks for the link. All it really did was scold people who use lookup fields in tables.

 

[RuralGuy]

Each of these lookup fields store the District and Route info respectively in tblProjects and not the ID info.

I believe you will find the exact opposite is true. It is why I supplied the link. I think you will need to create two new fields in your temporary table and set them to the ID's for these two Lookup's using an update query with the Switch() function.

 

[RuralGuy]

Actually, now that I think about it, you do not need the additional fields. They can simply be created in the append query using the Switch() function.