Find sum (1 Viewer)

JithuAccess

Member
Local time
Today, 06:39
Joined
Mar 3, 2020
Messages
297
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:

1596639745529.png

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

AWF VIP
Local time
Today, 12:39
Joined
Jul 26, 2013
Messages
10,354
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

Member
Local time
Today, 06:39
Joined
Mar 3, 2020
Messages
297
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:

1596640420601.png


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

Enthusiastic Amateur
Local time
Today, 12:39
Joined
Sep 21, 2011
Messages
14,038
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

AWF VIP
Local time
Today, 12:39
Joined
Jul 26, 2013
Messages
10,354
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

Member
Local time
Today, 06:39
Joined
Mar 3, 2020
Messages
297
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
 

Users who are viewing this thread

Top Bottom