Best Query Approach

[Yam84]

Hello:

I wanted some guidance on how I could possibly handle an issue. I
have developed a system which tracks items checked in or out as well
as those that have been purchased. There is a cashbox which will be
used to make change for the purchases. I expect that the amount
which should be in the cash box should be determined by the amount
originally in the box minus the sum of purchases during a given week
or month (also taking into consideration credits, which will be owed
to the cashbox).
I would like insight on how I should handle the purchases. I have a
transaction table that has different types of transactions, check ins
and outs and purchases and credits (a credit is just getting an item
now which you dont have the $ for now, buthave to pay for it one day
later). I wanted to know the most effective way to determine in a
report the amount that should be in the box.

I tried an UPDATE qry, which updated a table, Cashbox w/ entries for
credits and purchases, however I discovered that will not update
automatically, that inorder to update, i'd have to re-run the query
each time. Additionally, if the amount in the box is different each
time, how will i store this information and use it to determine my
cashbox amount?

I appreciate any insight you can provide.

 

[fneily]

First I know nothing of accounting. One of my degrees is in Mathematics and accounting makes no sense.
Second, it seems you're trying to reinvent the wheel. Have you looked at QuickBooks?
However, I'll present something and let others dissect it.
You didn't post your table structures, so I've made my own.
tblItems
ItemID Primary Key
Item fields
Price

tblCustomers
CustID Primary Key
Customer fields

tblTransactions
TransID Primary Key
CustID
ItemID
Quantity
Date
TransType (Purchase, Credit, CheckIn, CheckOut)
Change

tblCashBox
CashID
Amount
DateAdded

tblCashBox is initially filled with one record showing the original amount of money.

Now, after a certain time period, say a month, run a query connecting tblItem and tblTransaction to find the total amount of purchases and the total amount of change given and have a new field called, let's say, Net which is the difference of the two. You can then add this net to the Amount in tblCashbox and update the DateAdded.

Your tblTransactions table would keep all purchase, credit data, not your tblCashbox.