Find sum

[JithuAccess]

Hello Guys,

In my table I have fields Employee ID, category and Amount. I have more than 5000 records. In the category field, I have values like "Salary", "Commission", and "Bonus" Is it possible to find the sum of these categories by Employee ID. Currently I am doing this in Excel manually like below:

And here I am manually adding the values of "Salary, "commission" and "Bonus".

It will be highly appreciate and save huge amount of time if I can use a query to find the sum

Thanks in Advance

 

[Minty]

How is your data stored - can you show us the table design?

In a grouped query you should be able to use Sum(Amount) if you group by employee

 

[JithuAccess]

How is your data stored - can you show us the table design?

In a grouped query you should be able to use Sum(Amount) if you group by employee

Sure,

This is my Table design:

Please note that I have temporary created a table to create a query to find the sum group by Employee ID. The actual data is in Excel and I felt it will take few days if i do manually in Excel

Thanks

 

[Gasman]

A few simple SUMIFS() would do it in Excel surely?
However why not link to the Excel workbook and then use the query that Minty mentioned.

 

[Minty]

Do what @Gasman said link to your excel table in access. Then the following query should work

SELECT Employee_ID, Sum(Amount) From YourTableName
GROUP BY Employee_ID

You could easily add criteria to only include certain categories, for instance:

WHERE Category IN ( "Sales", "Commission", "Bonus")

 

[JithuAccess]

Do what @Gasman said link to your excel table in access. Then the following query should work

SELECT Employee_ID, Sum(Amount) From YourTableName
GROUP BY Employee_ID

You could easily add criteria to only include certain categories, for instance:

WHERE Category IN ( "Sales", "Commission", "Bonus")

Thanks a lot. It works