Re: Running balance from two tables

A couple observations (see in-line below)...

"g2v" <g2v@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A75FF33E-7D58-492D-A337-817FF03C8C49@xxxxxxxxxxxxxxxx
qryEmployeeBalance (query that filters for an employee's card from a drop
down)

id - number (autonumber)
employee - number (card code number of the employee's id card)
balanceAddition - currency (the currency amount added)
dateAdded - date field set to now() (the date the money was added)

Actually, using Now() inserts the date AND TIME ... and makes looking for a
particular date a bit harder. If you only need to know what date, use
Date() instead.


qryEmployeeCardSwipeHistory (query that comes from 2 other queries and a
table to show)

cardCodeSwipped - number (card code number that the scanner read)
dateAdded - date field set to now() (to show the date it was swipped)
typeOfMeal - 2 digit number (to designate how much it cost and to show
on reports)
description - text (verbal description of the meal - breakfast, lunch,
dinner)
cost - currency (cost of that meal)

If you already have a meal type ("2 digit number...") to determine the meal,
why are you also using a text field to describe it? This seems like a prime
opportunity for folks to make mistakes (e.g., "b'fast", "breakfast",
"brekfast", ...) which would make reporting more difficult. And since
you've described the query (but not the underlying tables), I still don't
have any idea how your database is storing the data.



I'm able to put both these queries in DATA*** VIEW on Access 2007, go to
the TOTALS button and show a totals line in the query, but I do not know
how
to access that and how to reference that so I can have a running balance
for
my total additions and my total costs and see how much money is in my
account.

I'd like to be able to reference it in drop downs. I'd also like to show
it
on reports.

I'm not clear to what you are referring when you say "...reference it ..."
or "... show it ...". Reference or show what? A total? Is the total the
sum of the deposits less the sum of the meal costs?


What I'd love to do with it is have something pop up when the balance is
less than $5 (or some number) so they are alerted to how much money is in
their account.

You can do that with a bit of code (you have to check the 'balance' to
decide whether to trigger the message).


Also then to have it now let them swipe their card if there isn't enough
money in their account.

I don't understand. Are they supposed to swipe their card no matter what?


Thanks for your help, time and trouble, hopefully there is a quick answer
for this.

Define "quick"<g>! If you are under a serious time crunch, this volunteer
newsgroup might not be able to respond quickly enough. If you are looking
for an 'easy' answer, that depends on your skills & experience.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



Tony

"Jeff Boyce" wrote:

It all starts with the data...

You've described your situation and your preliminary requirements, but
what
data are you capturing? I can guess that you are capturing [Amount].
Are
you also capturing [DateOfAmount]? [EmployeeID]? ...?

"How" depends on what data, and how it is structured (i.e., table
structure).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"g2v" <g2v@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:208BE3E9-C746-4252-A1F2-B5617185BCA0@xxxxxxxxxxxxxxxx
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 data*** 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 DATA*** 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
report,
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





.