UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Calculating Total Cash Query    
 
   
juju
post Dec 2 2007, 04:24 PM
Post #1

New Member
Posts: 3



This query is simple, but my knowledge is limited.
My total cash is determined by my cash receipts minus cash disbursements. I'm trying to create a query that will allow me to input a range of dates and give me a total cash amount at that point in time.
Can anyone help me with this issue? I would be most appreciative!
Go to the top of the page
 
+
strive4peace
post Dec 2 2007, 04:30 PM
Post #2

UtterAccess Leader
Posts: 20,347



Hi juju (what is your name?)
That is the structure of the table your query will be based on?
Go to the top of the page
 
+
juju
post Dec 2 2007, 10:07 PM
Post #3

New Member
Posts: 3



Hi my name is bob, but it was taken, so I came up with a wierd name out of nowhere (juju).
I'm not sure what you mean by the structure of the table, but it has rows and columns. Cash receipt stable has Receipt ID, Date, Amount columns. Cash Dibursement has Cash Disbursement #, Date, Amount. Is this what you where asking for?
Go to the top of the page
 
+
strive4peace
post Dec 2 2007, 10:28 PM
Post #4

UtterAccess Leader
Posts: 20,347



Hi Bob,
Take a form:
Name --> f_ReportMenu
make a textbox control on that form
Name --> RptDate
now save the form and switch to the form view and fill in a date
leave the form open while you make a query based on your Cash receipt table
Name --> qRptReceipt
SELECT Sum([Amount]) as Received FROM [CashReceiptTablename] WHERE RDate <= cDate(forms!f_ReportMenu!RptDate)
WHERE
CashReceiptTablename is the name of your Cash Receipts table
RDate is the fieldname of the Date
now make another query based on your Disbursements table:
Name --> qRptDisburse
SELECT Sum(Amount) as Disbursed FROM [CashDisburseTablename] WHERE DDate <= cDate(forms!f_ReportMenu!RptDate)
WHERE
CashDisburseTablename is the name of your Cash Disbursements table
DDate is the fieldname of the Date
once you verify that the queries get the right information, save and close them
now make a query that combines the two:
Name --> qRptCash
SELECT Received, Disbursed, Received-Disbursed as Cash FROM qRptReceipt, qRptDisburse
save it and look -- is the number right? close the query
~~~
now, go back to the design view of your ReportMenu form and add a command button:
Name --> cmdCash
Click --> [Event Procedure]
CODE
   DoCmd.OpenQuery "qRptCash"

save the form and switch to form view
fill in a date, then click your command button
~~~~~~~~~~~~~~
do not use "Date" for a fieldname, it is a reserved word
Problem names and reserved words in Access, Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html
also, it might be easier if you made a Transactions table and put Receipts and Disbursements in the same place. If you decide to keep the tables separated, it would be a good idea to qualify the Amount fieldname -- AmtReceive or AmtDisburse
Go to the top of the page
 
+
truittb
post Dec 3 2007, 07:51 AM
Post #5

Retired Moderator
Posts: 13,562
From: Texas (Is there anywhere else?)



You should not have a Cash Receipts table and a Cash Disbursements table. You should have a single Cash Table.
ashTable
--ID (Autonumber) Primary Key
--AccountID (Long) FK to AccountTable
--TransID (Long) FK to TransactionType table
--TransDate (DateTime)
--Amount (Currency)
You then use Aggregate querys (Group By) to return the balance at any given time.
Go to the top of the page
 
+
juju
post Dec 3 2007, 12:40 PM
Post #6

New Member
Posts: 3



I figured it out! Thanks for all of your help! I would be lost without you!
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 14th September 2014 - 09:00 PM