Looking up a value from another table and presenting it in the main table (1 Viewer)

Although it may sound inefficient, I have a requirement to display a field called Type (representing the aircraft type) in the main table called TblTcAOG which is also present in the table tblactype.

This field is related to REG (the registration of the aircraft) via a sort of convoluted process linked via 2 tables.

In TblTcAOG I want to specify the aircraft's registration REG and then for Access to look up the REG from TblTcAOG in TblAcrft and give me the ModelLink which in turn corresponds to the actypeid in tblactype to then give me the Type.

I have written an expression which gives me exactly what I want in appearance on the form that I am using for filling in the details for TblTcAOG, but I don't know how to get this Dlookup to write the Type information into TblTcAOG.


If anything I have said is confusing or needs further explanation, please let me know and I will try to help.

easier to create a function to get the Type, eg

to get the Type:
fnLookup(strRegNumber)

this is the function

Public function fnLookup(strReg As String)
Dim rs As Dao.RecordSet
set rs = CurrentDB.OpenRecordset("SELECT T1.Type From TblAcrft Inner Join tblAcType As T1 On TblAcrft.ModelLink = T1.actypeid Where tblAcrft.Reg = '" & strReg & "'")
If Not (rs.BOF and rs.EOF) then
fnLookup=rs(0)
End If
rs.Close
set rs=nothing
End Function

sample:

strType = fnLookup("12345678")

There is no reason to store the type a second time. Rather than nesting the DLookup() you should be able to simply use a left join to obtain the data. In the vast majority of cases, DLookup()s can be replaced with joins which are much more efficient.

From a database normalization viewpoint, Pat is absolutely right. If a table doesn't do what you want, don't denormalize the table. Make a query instead. Queries are versatile and take up very little space in any database. Reports and forms can be run to use queries, though in Forms, it is also possible to have DLookups to allow you to display what you need. (Not necessarily preferred, but possible.) With reports that can't change anything, CERTAINLY use queries with JOINs for the lookups.