Re: Running balance from two tables

Hmmm? Just another thought...

If the deposits made are $$ coming in and the scans are $$ going out, why
bother using two tables?

A single table could hold something like:

tblTransaction
TransactionID
PersonID (a foreign key, pointing at the tblPerson record/ID)
Amount
CRDR (credit or debit) (this could be a Yes/No field, like
[Credit?])

I'd probably set it up with that last field being something instead like:

TransactionType (a foreign key, pointing to a tlkpTransactionType)

because I might have "deposit by customer", "direct deposit from payroll",
"scan", "refund", and any number of other possible transaction types (but
that's just me...).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"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)

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)

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.

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.

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

Thanks for your help, time and trouble, hopefully there is a quick answer
for this.
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





.