How to remove duplicated ID but have them add up the values the ID represneted (1 Viewer)

alcorp · Aug 7, 2014

Hi,

In access I queried my results and now I have to do this last step. I basically have an ID column and a tax fee column. I am trying to remove the duplicated ID numbers but if they are duplicated I need it to add up the tax fee column so I can have one ID with all the tax fees added up together instead of several different ones.

I tried doing the equivalent to a pivot table (works perfectly in excel) but when I tried doing it in access, it did not work.

Is there any other way or steps on how to do this??
Thanks

plog · Aug 7, 2014

You would use an aggregate query (click the Sigma/Summation symbol in the ribbon). That will put 'Group By' underneath all your fields listed in the bottom. Under the tax fee field, change it to 'Sum'.

Post back your SQL if you have any other issues.

alcorp · Aug 7, 2014

plog said:
You would use an aggregate query (click the Sigma/Summation symbol in the ribbon). That will put 'Group By' underneath all your fields listed in the bottom. Under the tax fee field, change it to 'Sum'.

Post back your SQL if you have any other issues.

Okay where is it? I clicked on my query results table to display the ID and the Taxfee columns. So I then click the create tab and I don't see Sigma/Summation symbol in the ribbon. (I looked at the other tabs and still nothing)

plog · Aug 7, 2014

In design view of the query it is under the Query Tools option in the menu, under that is one that says 'Design'

alcorp · Aug 7, 2014

plog said:
In design view of the query it is under the Query Tools option in the menu, under that is one that says 'Design'

Okay awesome I found but I get this error when I run it. I left the ID column as Group by and I tried using other methods but still the same error.

"Data type in mismatch in criteria expression."

This is the SQL:
SELECT APN.APN, Sum(APN.TAXFEE) AS SumOfTAXFEE
FROM APN
GROUP BY APN.APN;

plog · Aug 7, 2014

That means TAXFEE isn't a numeric field. You can't sum up text. Why is TAXFEE a text field?

alcorp · Aug 7, 2014

plog said:
That means TAXFEE isn't a numeric field. You can't sum up text. Why is TAXFEE a text field?

Oh whoops. Supposed to be a numeric. Let me try again.

How to remove duplicated ID but have them add up the values the ID represneted (1 Viewer)

alcorp

Registered User.

plog

Banishment Pending

alcorp

Registered User.

plog

Banishment Pending

alcorp

Registered User.

plog

Banishment Pending

alcorp

Registered User.

Similar threads

Users who are viewing this thread