Tricky Query for alias name

[dynamictiger]

I have a field bound to my table called Type. This lists a general type of object something like Car,Truck, Boat. This is a lookup to another table that contains all types.

I then create an Alias name for some of these types, not all, through another table, called TblAlias. This contains the ID belonging to the other table as a look up field.

So staying with the Car example we could have three cars in one family, Dad's Car, Mums Car, Sons Car, these could be placed as friendly names in the Alias table.

The idea of this query is that when you look up the family, the subform this forms the basis for, will find three cars with Alias names and will populate the column with the Alias not the word Car three times.

So far all I have managed to do is confuse myself. I now have the column for Alias showing 1,2,3, instead of Car, Truck, Boat, and the Alias name as well. I thought I could write an iif statement to fix this, but this is giving an error.

iif(Name1=1,"Car",iif(Name1=2,"Truck"),Name1)

This is not a good example but it is the best explanation I can give.

 

[The_Doc_Man]

This sounds vaguely like something you would normally do with a JOIN of the alias table to the thing you are seeking - if you are doing reports, in which case you do GROUP BY options - or, for forms, you might use a form/subform situation where the details in the subform (from your alias table) are linked by parent/child fields on the generic Type field.

Now, as a side comment.... don't use TYPE as a field name. It is a reserved word. You'll confuse yourself and Access as well if you use a reserved name.