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

VAer-4038 766 Reputation points
2021-01-23T17:07:33.7+00:00

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.

59783-access-vlook.jpg

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
882 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Tom van Stiphout 1,696 Reputation points MVP
    2021-01-23T17:46:54.527+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.