Running balance from two tables

[g2v]

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

 

[Jeff Boyce]

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]

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 DATASHEET 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

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

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

 

[Jeff Boyce]

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

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 DATASHEET 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

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

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

 

[Jeff Boyce]

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

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 DATASHEET 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

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

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