Using Access 2007.
Cafeteria database.
Trying to show employees how much money is in their account by having a
running balance of how much money they put in ONE table compared to how
much
money they spend each time they get a meal. From reading other posts, it
looks like I needed to make 2 queries, then make a 3rd query to do the
math,
but I'm stuck.
I have a table that tracks employee's balances for cafeteria meals (if
they
enter $50 today, then $15 tomorrow, etc)...then a table that tracks each
time
they buy a meal in our cafeteria (ranging in price from .50 to $2.75 to
$5).
Now I want to have a running balance for each employee. Ideally when they
scan their card, I'd like an if statement or something with validation to
show how much they have on screen (like a pop up box - I'd also like a pop
up
box to ask the employee if they plan to eat the next meal so we can plan
for
food costs, but that's another question for another post (unless you know
the
answer here?)).
First I made a query on employeeBalance table, in datasheet view I went to
totals and showed the SUM for balance, then did the same in the other
table
to make a query for cost on employeeScannedMeals table. At the bottom of
both tables in DATASHEET VIEW it shows me the total, but I don't know how
to
reference that line of totals in a third query. I tried in SQL, but I'm
not
sure how to do those queries very well and I haven't seen a way to access
the
SUM/TOTAL line from the query in any part of Access.
How do you build that 3rd query and access that totals field?
I tried from in the design view to use the SUM option of the query and
make
a new column, but that never did the math on my column, it just repeated
the
balanceAddition column that had the amount the employee entered, but no
total.
Basically I need a way to take all the money and employee puts in their
account and all the times they scan their card for a meal, add all of them
up
and subtract them from their balance to show a total. Either on a re****t,
but PREFERABLY in a drop down or somewhere we can show them how much money
they have. It'd be nice to have it automatically alert them when they had
less than $5 in their account.
Thanks!
Tony
|
