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

Question

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.

Answer 1

If you want a Relational Database, and that is what Access offers, your "preference" has no import on proper design. You should really read up on the rules of Normalization. You will learn that tblWorkingHours cannot have FN, LN, since they are already provided in tblUsers. If Michael Sandoval marries Sally Yates and takes her last name, you want to make that change in ONE place.

If you can't yet write join statements, use the query designer to create them graphically, and then switch to SQL view to see what that produced. Many developers learn SQL basics that way.

Additionally it is a best practice, though not a requirement for Normalization, to use numeric ID values for primary keys. Why? Because it avoids cascade updates that would otherwise need to be in place as username conventions may change from time to time: what is mike.sandoval now, may be MSandoval tomorrow. Numeric keys are also smaller and faster.
So tblUsers.UserID is an Autonumber PK, and it links to a Long Integer UserID in tblWorkingHours. tblUsers.UserName can be another field in that table, and it can have a unique index on it.

Also: "Date" is a Reserved Word and as such should not be used for object names.