Right,
This is my second attempt at writing this post as my first one just rambled on about the database I am building.
Attached is a screen-print of the database I am working on. It tracks work received and processed on a daily basis across multiple teams. Some teams track figures at a personal level, others track them at a team level. As such I need to be able to record some teams work processed against each member of staff and other teams need to be tracked as a team.
As such I have two tables for storing figures, tblTeamFigures and tblPersonalFigures. The plan is that tblTeamFigures will always be populated. If the team tracks figures at a team level data can be input directly onto it, if not it can be input into the tblPersonalFigures and an append/update (depending if it's new data or updated data) query can sum the work processed by each staff member in the team and save that in the team figures table.
This means the database is slightly un-normalised as the NumberWorked fields in tblPersonalFigures and tblTeamFigures are for the same data.
Anyway...
The problem I am having is with the update query to update an existing record in tblTeamFigures with data from an agregate query grouping by TeamID, TaskID & FigureDate and summing NumberWorked to get the work processed figure for each task that the given team has on the given date.
I have a select query built which gives the results which I want to use:
However I don't know how to then use those results as the source of an update query to update a different tblTeamFigures.
I tried creading an update query showing the source query and the table to be updating them and linking on all 3 fields (TaskID, TeamID & FigureDate) and telling it to update the NumberWorked field with AgregateQueryName!NumberWorked but I get the message:
I should be able to work out a way to do it via VBA if I need to but I would rather do it via a query if poss.
This is my second attempt at writing this post as my first one just rambled on about the database I am building.
Attached is a screen-print of the database I am working on. It tracks work received and processed on a daily basis across multiple teams. Some teams track figures at a personal level, others track them at a team level. As such I need to be able to record some teams work processed against each member of staff and other teams need to be tracked as a team.
As such I have two tables for storing figures, tblTeamFigures and tblPersonalFigures. The plan is that tblTeamFigures will always be populated. If the team tracks figures at a team level data can be input directly onto it, if not it can be input into the tblPersonalFigures and an append/update (depending if it's new data or updated data) query can sum the work processed by each staff member in the team and save that in the team figures table.
This means the database is slightly un-normalised as the NumberWorked fields in tblPersonalFigures and tblTeamFigures are for the same data.
Anyway...
The problem I am having is with the update query to update an existing record in tblTeamFigures with data from an agregate query grouping by TeamID, TaskID & FigureDate and summing NumberWorked to get the work processed figure for each task that the given team has on the given date.
I have a select query built which gives the results which I want to use:
Code:
SELECT tblStaffMembers.TeamID,
tblStaffFigures.TaskID,
tblStaffFigures.FigureDate,
Sum(tblStaffFigures.NumberWorked) AS SumOfNumberWorked
FROM tblTeams
INNER JOIN (tblTasks
INNER JOIN (tblStaffMembers
INNER JOIN tblStaffFigures
ON tblStaffMembers.FileNumberID = tblStaffFigures.FileNumberID)
ON tblTasks.TaskID = tblStaffFigures.TaskID)
ON tblTeams.TeamID = tblStaffMembers.TeamID
GROUP BY tblStaffMembers.TeamID,
tblStaffFigures.TaskID,
tblStaffFigures.FigureDate
HAVING (((tblStaffMembers.TeamID)=[Forms]![frmTeamSelect]![cboTeam])
AND ((tblStaffFigures.FigureDate)=[Forms]![frmTeamSelect]![cboDate]));
However I don't know how to then use those results as the source of an update query to update a different tblTeamFigures.
I tried creading an update query showing the source query and the table to be updating them and linking on all 3 fields (TaskID, TeamID & FigureDate) and telling it to update the NumberWorked field with AgregateQueryName!NumberWorked but I get the message:
Operation must use an updatable query
I should be able to work out a way to do it via VBA if I need to but I would rather do it via a query if poss.