I am building a Database and here is my question/issue:
I have table1 called [Personnel]. This table holds "EmployeeID", "Name", "ReturnDate", ETC
I have table2 called [Awards]. This table holds: "EmployeeID", "Name", "ReturnDate", "Recommender","TypeOfAward" "Award1SubmissionDate", "Award2SubmissionDate", and "Award3SubmissionDate"
Data will be entered into table1 that is then updated to table2 using an appendQuery. The data that is appended is ("EmployeeID", "Name", "ReturnDate") YES I know the data is redundant but I need it that way.
Both table1 and table2 will have data entered via septerate forms. Table2 ("EmployeeID", "Name", "ReturnDate") will not be allow updates from table2Form bc i have it locked and it is being pulled from table1.
I have table3 called [Settings]. This table holds "Title" and "Key" (that need to be referenced via I am thinking DLookup?) Table3 looks like this:
Title Column has Award1, award2, award3. the Key Column has the number of days that need to be subtracted from the ReturnDate in table2 that has been appended from table1 so award1 would be 100, award2 would be 50 and award3 would be 30
What I am looking to do is I need "Award1SubmissionDate", "Award2SubmissionDate", and "Award3SubmissionDate" in table2 to be auto populated based on Return Date MINUS the number in the key column for that type of award from table3.
I think this is how is supposed to be but I dont know were to place it and it doesnt work:
Award1="Return date"-DLookup("Key","Settings","Title"=Award1)
This is not working for me. I really need help. the supplied DB is a quick build to get what I need. then I will implement it into my working database