Slightly un-normalised update query (1 Viewer)

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:

Operation must use an updatable query

Click to expand...

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.

Just a couple of questions CBrighton:

1. Why are you needing to store a derived value?
2. Have you incorporated this update into the form that allows users to manipulate data relating to those three tables in the query?
3. Have you thought about putting that in a query and referring to the query in your update query?
4. If you were to update tblTeamFigures, what fields relate it to your current query?

vbaInet said:

Just a couple of questions CBrighton:

1. Why are you needing to store a derived value?

Click to expand...

The only other option I can think of is storing the data in different tables for different team types (personal stats vs. team stats). If I do that it will complicate things like reporting as I would need a set of reports for each table. By storing the sum in the team table I can run all reports off the team table regardless of whether the team records their figures against a staff member or the team as a whole. It's only the reports specific to personal stats which will be done on the other table.

2. Have you incorporated this update into the form that allows users to manipulate data relating to those three tables in the query?

Click to expand...

Currently I have 2 update forms. The first is for the team, it checks if the team is set to accept personal stats via a true/false field in tblTeams and if it's a team with personal stats it disables the controls bound to the fields in tblTeam (except the new work in field) and enableds a command button to take the user to the personal stats form. This second form is bound to tblPersonalFigures, I was going to have an event tied to something like after update which would run the update query.

3. Have you thought about putting that in a query and referring to the query in your update query?

Click to expand...

Not sure what you are refering to here.

4. If you were to update tblTeamFigures, what fields relate it to your current query?

Click to expand...

The select query I provided the SQL for doesn't link on any single field. All 3 of TeamID, TaskID & FigureDate will match.

If I create a new query and show both the query above and tblTeamFigures, linking the two sources on all 3 fields I mention above, then tell it to output TeamFigureID (the PK in tblTeamFigures) and all fields from my query I can see it does correctly link the data to the right records.

But it still gives the same error message if set to an update query.


I am currently making small changes to the structure. I'm moving my NewIn & BroughtForward fields from tblTeamFigures to a new table. This will mean that I can use dleete & append queries instead of an update query as the 2 fields I needed to retain have moved to a new table.

Ah, alright! I now understand your dilemma.

There are a couple of workarounds.

1. Condense your query so that it uses of a DSum(). The SUM() and GROUP BY is causing your query to be unupdateable. So the query will look something like this:
Obviously not the full syntax but it gives an idea of what to do.

2. Keep your query as it is but use a DLookup() to retrieve the corresponding value which you will use in the Update query. So the Update To column will look like:
3. Have a look at this link:

http://www.fmsinc.com/microsoftaccess/query/non-updateable/index.html

Ah.

I use Domain functions so rarely I don't even consider them when building a database. I never even thought of them.

Thanks for the advice, I'll give it a try.

assumimg each person is allocated to a team, you only need to store the person results

so

persons (personid, scores)

teams(teamid, name)

menber(teamid, personid, datejoined etc etc)

then a query to find all the team members, and sum their results is easy-peasy.

The problem is that although all persons are allocated to a team not all persons are allocated figures.

Some of our teams just record work in & performed at a team level rather than per person (generally speaking teams who just have #calls received & # calls answered have team level figures which are collected from our call monitoring system. Teams who deal with more complex or non-telephony pieces of work have figures per person).

Otherwise I would do as you say and have a single table linked to the staff member table for figures.

I have not read the whole thread but I would not use the denormalized strucure. It just makes trouble.

I would use an "UnspecifiedPlayer" record for each team to record results when there are no personal figures.