hello all,
if i have several type of expenses for example , i have a table for salary of employee and another table many spent for fuel and another table for other expenses
each table have a sum , hot to get the sum for all tables ( the money spent) per month
i hope you got my point
Regards
how to get sum for different tables?!!
-
- BronzeLounger
- Posts: 1278
- Joined: 01 May 2016, 09:58
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to get sum for different tables?!!
I'd store all the expenses in one table, with an extra field to specify the type of expense. You can then simply create a totals query that sums the amounts.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1278
- Joined: 01 May 2016, 09:58
Re: how to get sum for different tables?!!
thanks a lot for your reply,
i have created something like below, 1- now how i mix the monthly salary with expenses table
2- if i have a table for budget , how to calculate the balance each month ?
i have created something like below, 1- now how i mix the monthly salary with expenses table
2- if i have a table for budget , how to calculate the balance each month ?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to get sum for different tables?!!
1) How is tbl_expenses related to tblEmployee?
2) It'd be easiest to have a sample database.
2) It'd be easiest to have a sample database.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1278
- Joined: 01 May 2016, 09:58
Re: how to get sum for different tables?!!
thanks for the quick reply,
we need the salary for the employee to be counted with the monthly expenses, and then deduct it from the budget and see how much money left in safe fro the next month
here is the db link https://my.pcloud.com/publink/show?code ... FqNjKLVjek
we need the salary for the employee to be counted with the monthly expenses, and then deduct it from the budget and see how much money left in safe fro the next month
here is the db link https://my.pcloud.com/publink/show?code ... FqNjKLVjek
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to get sum for different tables?!!
Hi Siyamand,
There is no budget table in your sample database, and there is no way to link the employee table with the expenses table.
There is no budget table in your sample database, and there is no way to link the employee table with the expenses table.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1278
- Joined: 01 May 2016, 09:58
Re: how to get sum for different tables?!!
thank you for the replyHansV wrote:Hi Siyamand,
There is no budget table in your sample database, and there is no way to link the employee table with the expenses table.
any suggestion how to work around this please? i mean table employee and expenses
how to make this work please
kind regards
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to get sum for different tables?!!
If each expense can be attributed to an employee, tbl_expenses should contain a required field EmployeeID (of type Number, field size Long Integer). This would allow you to link expenses to employees.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1278
- Joined: 01 May 2016, 09:58
Re: how to get sum for different tables?!!
no the expenses is different from the salary of the employee...
can we make another table for salary and get the total of salary distributed in a month then deducted from the budge table ? if yes the deduction how it is done?
Regards
can we make another table for salary and get the total of salary distributed in a month then deducted from the budge table ? if yes the deduction how it is done?
Regards
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to get sum for different tables?!!
Create a table with fields EmployeeID (linked to tblEmployee), SalaryDate and SalaryAmount. Add a record for each employee for each month they receive a salary.
Create a totals query based on this table.
Group by Year(SalaryDate) and Month(SalaryDate) and sum SalaryAmount. This query gives you the total salary paid each month.
Create another totals query based on the budget table.
Group by Year(BudgetDate) and Month(BudgetDate), and sum BudgetAmount. This query gives you the total budget for each month.
Finally, create a new query. Add the two totals queries, and join them on the year and month fields.
Add the year and month fields from one of the two tables to the query grid, and create a calculated column that returns SumOfBudgetAmount-SumOfSalaryAmount.
Create a totals query based on this table.
Group by Year(SalaryDate) and Month(SalaryDate) and sum SalaryAmount. This query gives you the total salary paid each month.
Create another totals query based on the budget table.
Group by Year(BudgetDate) and Month(BudgetDate), and sum BudgetAmount. This query gives you the total budget for each month.
Finally, create a new query. Add the two totals queries, and join them on the year and month fields.
Add the year and month fields from one of the two tables to the query grid, and create a calculated column that returns SumOfBudgetAmount-SumOfSalaryAmount.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1278
- Joined: 01 May 2016, 09:58
Re: how to get sum for different tables?!!
thanks a lot for the reply , i did what you advised + i add another table and query to calculate the expenses, so in the last query instead of adding two tables i have add three queries
the issue is here,
for example i have got budget for 1/11/2019 : 200$ and for 1/12/2019 300$ which means for year 2019= 500$
and i have expense 24/12/2019 : 100 $ and salary for 30/12/2019 300$ --- so total spent is 400, and remaining should be 100,
but because of the link created between month query it only calculate Dec
so basically i have three queries budget , expenses and salary ... in all queries i have to have same months in order to get the correct amount.
i hope i was able to clear what is in my head...
here is the link of the modified database
https://my.pcloud.com/publink/show?code ... yprX0sAqXV
the issue is here,
for example i have got budget for 1/11/2019 : 200$ and for 1/12/2019 300$ which means for year 2019= 500$
and i have expense 24/12/2019 : 100 $ and salary for 30/12/2019 300$ --- so total spent is 400, and remaining should be 100,
but because of the link created between month query it only calculate Dec
so basically i have three queries budget , expenses and salary ... in all queries i have to have same months in order to get the correct amount.
i hope i was able to clear what is in my head...
here is the link of the modified database
https://my.pcloud.com/publink/show?code ... yprX0sAqXV
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to get sum for different tables?!!
Here is a modified version. It's rather complicated - it uses a union query to return all year/month combinations, and a query with non-standard joins to calculate the cumulative amounts.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans