Calculated Fields in a Table??

[galvinjaf]

Good Morning,

I'm not sure if this is a true TABLE questions or not, but here goes. I have two separate tables that are subforms of an Employee Form. I keep track of certain dates where employees can 'earn' money for doing certain things. I have one table that keeps track of the date and money earned, and another table that keeps track of money spent and the date.

What I cannot seem to figure out is how to run a simple query that says Employee X has X amount of Money to spend. I want to be able to calculate the total earned, and total spent and get a total. Attached is a screen grab of my form with the two tables. Any ideas on what I can run? I've tried a query, but can't get that to run what I'm looking for.

Thanks in advance.

 

[plog]

Tables is the correct subforum for this. The answer to your issue is going to involve both tables and queries though.

First, this data shouldn't be in seperate tables. It should be all in one, that way you can create a simple query and get balance, total credits, total debits, etc. You can still have seperate subforms, but they need to be based off the same table.

In that table, to distinguish debits/credits you can either make a new field to hold that, or you can just let negative values be debits.

To get your Total lines on those forms, you would do a DSUM on that table adding just credits/debits for each subform.

 

[galvinjaf]

Thanks for getting back. Okay, I've combined those tables into, and ran a query with the data.

I have entered data for myself, which shows up 3 times as I've earned money on 3 separate dates. I want to take the total earned money and subtract the total Used amount from it and get a total of what is left, and I'd like to do this by Employee. There is only one employee in there now, but eventually there will be more.

Is this possible??

 

[plog]

What I meant is that the values should be in the same field. That means your table should look like this (using the values in your last attachment):

Banked_ID, ID_Employee, Banked_Date, Banked_Amount
1, 1, 2/1/16, 25
2, 1, 2/6/2016, 10
3, 1, 2/16/16, -5
4, 1, 2/18/2016, -10
5, 1, 3/2/16, 40

Then to get to get the balance by employee, you would use this query:

SELECT ID_Employee, SUM(Banked_Amount) AS Balance
FROM YourTableNameHere
GROUP BY ID_Employee;