Queries using multiple sources

[BringTheR41n]

Im creating a query to help keep my bills in order and so i have a table set up with all the information in it. what i need to do is figure every time i input an expense, it will subtract that bill from my remaining total and display what is left, ie: i have $2000 and i spend $200. i need the new column to read $1800. i know there is probably an easy way to figure this but i cant figure out how. is there an easy function i can plug into the query design to accomplish this? any help is appreciated.

I need the values to be kept the same throughout the column just the value for that new balance will change. i dont know how to set up a sum function in order to do this. i need it to be the newest balance - current bill = current balance.

[June7]

Conventional approach is to add up all receipts, add up all payments, subtract total payments from total receipts to determine balance.

Would need to know more about your data structure to offer more specific guidance.

I expect a report will be best vehicle for accomplishing this data manipulation.

[BringTheR41n]

what i really need to do is grab data from an excel spread sheet. there is a formula that i used to easily input any time i had a bill and it would take my previous balance(column 1) plus any deposits (column 2) minus the bill (column 3) and place the new total in column 1 but in the same line as the bill and it would still have the balance you had before. i would like for the numbers to appear as tho im looking at a bank statement with every purchase and the balance for each line after each payment. i have thought about doing what you said above but i am wanting to go more in the every purchase instead of the overall. i appreciate the help.

[BringTheR41n]

i have been looking online and ran into running sums? i dont understand how to use them or how they work but i think that's what im going for?

[June7]

A running sum in form or query can be done with DSum() function but domain aggregate functions can be slow performers. A running balance in form or query is complicated. Nether is advisable, especially if users are allowed to sort records.

Running balance should be done in a report, but even then it is tricky. Textbox in report has a RunningSum property not available in form. Need a starting balance in report header - determining this value is the hard part. Then in the Detail section a textbox that does RunningSum calculation (this can be hidden if you want). Then another textbox in the Detail section has expression like: [startbalance - running] to give the balance with each transaction.

However, a bank statement (and check register) has deposits as well as payments. Where are your deposits in this scenario?

[BringTheR41n]

my deposits are in a different column off to the side. i manually plug in information into that column like date it was deposited and amount. thats how i have the previous balance plus deposits minus bills. moreso to keep everything in day-by-day records.

[BringTheR41n]

would it just be better to keep the excel table as it is and not worry about transfering everything to access? or is accomplishing this beyond the skill of a beginner? because what you explained above seems super complicated..beyond the skill of someone who has been using access for 2 weeks now.

[June7]

If all you care about is current balance and not summarizing by expense or other data manipulation, Excel might be better for your needs, for now. It's possible could outgrow. Very large Excel files can be slow.

Review: http://office.microsoft.com/en-us/te...010356098.aspx

[BringTheR41n]

I have the bills broken up by month so it doesnt get too large. But access is mainly used for grouping and manipulating information such as where i am spending money whether it's utilities or payroll moreso than just seeing if i have money?

[June7]

Yes, Access just might be overkill for your needs at this time. If you learn more about Access and become more comfortable with it, you might want to take advantage of its data manipulation functionality.

[BringTheR41n]

alright thank you for the help. i appreciate it