how to get sum for different tables?!!

[siamandm]

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

[HansV]

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.

[siamandm]

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 ?

Capture.JPG

[HansV]

1) How is tbl_expenses related to tblEmployee?
2) It'd be easiest to have a sample database.

[siamandm]

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

[HansV]

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.

[siamandm]

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.

thank you for the reply

any suggestion how to work around this please? i mean table employee and expenses
how to make this work please

kind regards

[HansV]

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.

[siamandm]

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

[HansV]

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.

[siamandm]

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

[HansV]

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.

SampleDB.zip