I have not used Access for years, not quite familiar with Access either.
I want to design three tables: Employees, EmployeeSelfEntryNote, SummaryView
Table Employees: I will manually update this table periodically.
Table EmployeeSelfEntryNote: Every employee can enter one note for each day, via a Form. If the employee previously submitted note for the same date, then the new note will overwrite the previous note. I may need to write code for the feature of overwriting.
- How to check if the note already exists for that date. If yes, overwrite the note; if not, add the new note.
Table SummaryView: My main questions are related to this table. Let us say, now the table has already fields: EmployeeUserID, FirstName, LastName, 12/13/2019 Fri, 12/14/2019 Sat, 12/15/2019 Sun, 12/17/2019 Tue, 12/18/2019 Wed. The first three fields(EmployeeUserID, FirstName, LastName) will always remain. EmployeeSelfEntryNote will be shown in SummaryView table, that is the whole purpose of project.
If I want to the fields to cover date from 12/12/2019 to 10/15/2020, then how to write code to add so many fields? I actually can break my questions into Five parts:
- How to delete any fields(an associated data) except EmployeeUserID, FirstName, LastName, this will delete 12/13/2019 Fri, 12/14/2019 Sat, 12/15/2019 Sun, 12/17/2019 Tue, 12/18/2019 Wed
- Refresh SummaryView table with Employees table. At this point, SummaryView table has only three fields, and I want the SummaryView records to be same as Employees table. Not sure if I can do this in Table relationship or programming.
- Add fields(data type: short text) to SummaryView: 12/12/2019 Fri, 12/13/2019 Sat, 12/14/2019 Sun, ...... , 10/13/2020 Tue, 10/14/2020 Wed, 10/15/2020 Thu
- How to write code to loop through EmployeeSelfEntryNote to pull Note into SummaryView table. Employees are not required to submit Note for each day.
- In the future, if I want to delete any fields before 3/18/2020 Wed, how should I write the code?
Thanks.