Sum fields based on another field

[John P]

Is it possible to sum a field in an Access DB based on another field? I have a field called user and a few other fields that I need to sum by user (like you would in a pivot table). I need a calculation that will take this...

Sum ( [Completed File Extract]![Sum CRP] )

and do something like this....

SubSum ( [Completed File Extract]![UserName] ,[Completed File Extract]![Sum CRP] )

It may be easier to write it...

UserName CRP
Joe 1
Joe 1
Betty 2
Mike 1

The end result with be
UserName QRP
Joe 2
Betty 2
Mike 1

Any information is appreciated.

 

[Dugantrain]

In a query, click the Sigma icon in the toolbar (the icon that looks like a letter 'E'). Pull in your UserName field. By default, the totals field will say 'Group By', which is what you want for this field. Next, pull in the fields that you want to Sum. Change 'Group By' to 'Sum' and you should have your desired results.

 

[John P]

I'm creating an update query and the sigma is not available for an update query.

 

[John P]

I'll try to explain this a little better...

I have a field for credits that resides in one table and I need to bring in the sum of these credits for each employee into another table based upon an employee match. The end result should reflect the number of credits for the employee on each line that the employee appears on. So, if the final table has the employee 30 times and he has 23 credits. Each line will reflect 23.

My difficulty is summing the credits by employee on the source table, so I can pull the result into the final table. Any help is greatly appreciated.

 

[Dugantrain]

ok, build a Select query using my previous example. Then, build an Update Query by joining this Select query in with the table that you want Updated (join them together on the UserName field). Now pull down the field that you want to Update and in the Update To field, type in the name of the Sum field from the Select Query that you want this field Updated To. You will have one problem here: Because queries that perform calculations are not Updateable, then this Update Query will initially fail because the table that you're trying to Update is joined to a Calculations Query. To overcome this, you'll need to go to the Query's properties (In Design View, Right-Click anywhere on the grey part) and change the query's Recordset Type to 'Dynaset-Inconsistent Updates'. I believe that this will give you your needed results.

 

[John P]

I created the queries as you defined and received the "Operation must use updateable query" prompt that you predicted. I am trying to change the properties Recordset Type for the query, but it is not an available option. The Access help says this type can only be changed on forms. Is there a way around this?

 

[John P]

Update Query sourcing Select Query Results

OK..I opened the Select Query on its own and was able to change the Recordset Type, and then tried to run the Update Query and received the same error prompt.

I'm almost there...any other suggestions?

 

[Dugantrain]

Well, I've toyed with this problem and it turns out that my advice is wrong; it doesn't look like you can use Calculations queries in Updateable recordsets. Another approach you could take would be to take your calculations query and turn it into a Make-Table query. You can then join this new Table to the table that you want updated and perform your Update from there.