OK Guys! this one is challenging!
We've got three connected tables:
1. tblEmployees (Fields: ID, EmployeeName)
2. tblDays (Fields: ID, EmployeeID, Date)
3. tblTimesheet (Fields: ID, DateID, Project, Hour)
these tables are one-to-many in regard with the next one. In other words, for each employee, there are many Days, and for each Day, there are many Timesheet records.
There is a whole another separate table:
1. tblTimesheetGroup (Fields: ID, EmployeeID, FirstDayofMonth, TotalHours)
the operator should enter the EmployeeID, FirstDayofMonth and Totalhours in the separate table and access shall add N records (N being the quantity of days per each month) in tblDays for each nominated EmployeeID. Subsequently, access shall divide the value of TotalHour in tblTimesheetGroup by N, create a single record in tblTimesheet for the attributing record of tblDays, and put the divided value in Hour field!
Now here is the problem:
I need an Append query which adds a variable number of records (N being equal to 30 or 31 based on month which is selected as FirstDayofMonth) to a table (tblDays), and this append query must allocate sequential dates (field Date in tblDays from FirstDayOfMonth to FirstDayOfMonth+N).
I know that a loop might be handy, but not sure how to code it, any ideas?!
We've got three connected tables:
1. tblEmployees (Fields: ID, EmployeeName)
2. tblDays (Fields: ID, EmployeeID, Date)
3. tblTimesheet (Fields: ID, DateID, Project, Hour)
these tables are one-to-many in regard with the next one. In other words, for each employee, there are many Days, and for each Day, there are many Timesheet records.
There is a whole another separate table:
1. tblTimesheetGroup (Fields: ID, EmployeeID, FirstDayofMonth, TotalHours)
the operator should enter the EmployeeID, FirstDayofMonth and Totalhours in the separate table and access shall add N records (N being the quantity of days per each month) in tblDays for each nominated EmployeeID. Subsequently, access shall divide the value of TotalHour in tblTimesheetGroup by N, create a single record in tblTimesheet for the attributing record of tblDays, and put the divided value in Hour field!
Now here is the problem:
I need an Append query which adds a variable number of records (N being equal to 30 or 31 based on month which is selected as FirstDayofMonth) to a table (tblDays), and this append query must allocate sequential dates (field Date in tblDays from FirstDayOfMonth to FirstDayOfMonth+N).
I know that a loop might be handy, but not sure how to code it, any ideas?!