How to write access VLOOKUP formulas as field default value based on another table

[VAer]

For example, I have two Access tables (backend database): TableUser and TableWorkingHours



For TableUser, Username is unique.

I understand that I should not have FirstName and LastName in TableWorkingHours. For some reasons, I don't feel comfortable writing join SQL statement in Visual Studio C# frontend application (not an IT professional). But this is not the question.

My question is: Since I have already had FirstName and LastName in TableWorkingHours, and I prefer not to remove these two fields. So I am looking into Access field default value, and would like to write a VLOOKUP formulas for FirstName and LastName.

How should I write the formulas?

Thanks.

[Micron]

Access does not have a VLookup function (in a properly designed db it wouldn't be necessary). Perhaps explain what it is you are trying to do. If it is to get at the records in the hours table, you don't even need to join that table to the users table because as you've already pointed out, you are repeating data in tables (not proper). If you also need the status values, then you need to join.

If this is about using expressions in a table then there is a very short list of expressions that can be used in a table. This approach is ill advised - users should be interacting with data by using forms/reports - not directly in tables. Also, data calculated in table fields is subject to being orphaned or out of sync when other records are deleted/altered.

[VAer]

Access does not have a VLookup function (in a properly designed db it wouldn't be necessary). Perhaps explain what it is you are trying to do. If it is to get at the records in the hours table, you don't even need to join that table to the users table because as you've already pointed out, you are repeating data in tables (not proper). If you also need the status values, then you need to join.

If this is about using expressions in a table then there is a very short list of expressions that can be used in a table. This approach is ill advised - users should be interacting with data by using forms/reports - not directly in tables. Also, data calculated in table fields is subject to being orphaned or out of sync when other records are deleted/altered.

Ok, I think I do have to completely change front end application query to join statement.

Thanks.

[ssanfu]

Note that "Date" is a reserved word in Access and a built in function. Maybe use "WorkingDate" instead (no spaces).