Query that Calculates?

[galvinjaf]

Good Morning,

I've created a database to keep track of many things, one of which is the amount of money a Supervisor can earn (incentives) for completing certain things on their work shifts. They can earn a dollar amount on a specific date, and they can use a dollar amount that they have earned on a specific date. The dates, amounts earned, and amounts used are kept in one table(pic attached). I have two separate sub-forms however for each. (pic attached).

I can't seem to figure out how to run a query successfully that will take the total dollars a supervisor has earned, less the total amount used and give just one total per supervisor.

I'd then like to run a report to find out how many dollars each supervisor has left to use on whatever they'd like.

Any suggestions?

 

[plog]

I gave you the answer in your previous post:

http://www.access-programmers.co.uk/forums/showthread.php?t=284227

Your table structure still isn't correct to accomplish this.

 

[galvinjaf]

I did see your response, and must have been confused on what you were advising. When you say that my table strcuture still isn't correct, what do you mean? Simply moving columns to match your previous post?

** Edit - Perhaps I wasn't thinking outside the box, I now know what you're talking about. Instead of having a separate 'Used' and 'Earned' Amount, it'll be a simple 'Amount' field in which I'll enter either a positive or negative number in which we can total later?

 

[plog]

Correct. All amounts in one field. Then its a simple aggregate query.

 

[galvinjaf]

I've finally had some time to make the changes, and it worked flawlessly. Thanks again!