Append Question

[steve21nj]

I am trying to break up a single table database into a normalized one.

The current table1 [vehicles] has several text fields that I want to append to a new table, table3.

I created an example of what I am attempting to do by creating table2 [tblLocations] with two fields, Location and LocationID.

For the criteria, I want to identify the Like (*) of [Seiz_Loc] and [Location] where the first three letters match.

In my head I would build this:
Exp1: Like Left([vehicles].[Seiz_Loc],3) = Left([tblLocation].[Locations],3)

Is it possible to link the data from table1 and table2, via text fields, and append the LocationID of table2 based on the first 3 letters?


TextField: Seiz_Loc, Location
AutoNumber: LocationID

Thank you in advance


Edit: I made this expression but when I go to the datasheet view I get #Error

Exp1: Left([vehicles.Seiz_Loc],3=Left([tblLocation].[Location],3))

 

[jdraw]

I think it would be easier to give you a more focused response if you could describe the purpose of the proposed database in plain English. If you can develop a data model/ERD of your database design, it will be easier to build your application.

Here is a tutorial re database design.

Good luck.

 

[Galaxiom]

The expression you posted has an error
Left([vehicles.Seiz_Loc],3)=Left([tblLocation].[Location],3))

However if you use this as a select criteria on an unjoined pair of tables it willbe a big calculation that begins with a cartesian product. The query will run faster by using the expression to join the tables.

Although joins in query designer are limited to equals joins, Access supports many more join constructs including the use of functions in the SQL view.