I am working on an MI database to hold work receieved & completed across multiple teams.
All work completed is recorded in a table against members of staff, the TeamID of the member of staff links them to another table (via a staff details table to make it a many-one-many relationship) where the work received is recorded against the team.
As a result I have work receved at a team level from one table and a sum of work completed with the criteria set to the same team & task ID (each team has up to 20 tasks).
The problem is, not all work is completed on the day it is received, as such there will be a backlog amount to be aware of.
The way I see it there are two options. Record the volume of unworked work for each date and use that as a brought forward figure or have Access calculate the figure.
The problem with the first day is that any historic changes would then require the brought forward figure to need to be updated for every date in order for it to stay accurate.
The problem with the second is that I don't know how I would get access to calculate a running total based on date, teamID & TaskID over multiple tables.
The relevent part of the table structure:
tblStaffDetails
FileNumberID (PK)
TeamID
tblStaffFigures
StaffFigureID (PK)
FileNumberID (FK)
TaskID (FK)
FigureDate
NumberWorked
tblTeamFigures
TeamFigureID (PK)
TeamID (FK)
FigureDate
NewIn
BroughtForward
tblTeamFigures.BroughtForward can be removed if the backlog is calculated rather than recorded.
Basically, I really don't know how to proceed. I want it to be calculated rather than recorded but don't know where to start.
All work completed is recorded in a table against members of staff, the TeamID of the member of staff links them to another table (via a staff details table to make it a many-one-many relationship) where the work received is recorded against the team.
As a result I have work receved at a team level from one table and a sum of work completed with the criteria set to the same team & task ID (each team has up to 20 tasks).
The problem is, not all work is completed on the day it is received, as such there will be a backlog amount to be aware of.
The way I see it there are two options. Record the volume of unworked work for each date and use that as a brought forward figure or have Access calculate the figure.
The problem with the first day is that any historic changes would then require the brought forward figure to need to be updated for every date in order for it to stay accurate.
The problem with the second is that I don't know how I would get access to calculate a running total based on date, teamID & TaskID over multiple tables.
The relevent part of the table structure:
tblStaffDetails
FileNumberID (PK)
TeamID
tblStaffFigures
StaffFigureID (PK)
FileNumberID (FK)
TaskID (FK)
FigureDate
NumberWorked
tblTeamFigures
TeamFigureID (PK)
TeamID (FK)
FigureDate
NewIn
BroughtForward
tblTeamFigures.BroughtForward can be removed if the backlog is calculated rather than recorded.
Basically, I really don't know how to proceed. I want it to be calculated rather than recorded but don't know where to start.
