If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Checkbook Register
I am trying to create a checkbook register with running balance in the
query in Access I have spent hours and hours researching this with multiple suggestions, none of which have been successful. File=CheckRegister Fields=ID, Seq, Date, Ck#, CkAmt, Deposit. Desired Result=Balance I have put in sequence rather than depending on the autonumber ID so that if I have something listed out of the desired sequence, I can alter the sequence number and resort without having to re-enter the 2 records. Most of the suggestions have included the function DSum which I have not used before. Any help would be appreciated. I would like to have this functional by 01-01-10 Thanks |
#2
|
|||
|
|||
Checkbook Register
Edd wrote:
I am trying to create a checkbook register with running balance in the query in Access I have spent hours and hours researching this with multiple suggestions, none of which have been successful. File=CheckRegister Fields=ID, Seq, Date Date, being a reserved keyword, is a horrible name for your field. It's also the name of a VBA function, so if you fail to rename the field, someday you will run into the dreaded "unrecognized function" error that will take you days to debug. Change the name now. Call it "TransactionDate" or something. , Ck#, CkAmt, Deposit. Does CkAmt contain positive or negative numbers? I'm going to assume it contains positive numbers and negate them in the calculation. Change the subtraction to addition in my expressions if I've guessed wrong. Desired Result=Balance I have put in sequence rather than depending on the autonumber ID so that if I have something listed out of the desired sequence, I can alter the sequence number and resort without having to re-enter the 2 records. Most of the suggestions have included the function DSum which I have not used before. Any help would be appreciated. I would like to have this functional by 01-01-10 You should do the running balance in a report rather than in a query. Access reports have this functionality built in IIRC. If you absolutely must have this in a query, then yes, DSum is one way to do it, although I prefer using a correlated subquery. I will show both ways: SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit, SELECT Sum(Deposit - CkAmt) FROM CheckRegister as r where r.Seq = c.Seq) as Balance FROM CheckRegister as c SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit, DSum("[Deposit]-[CkAmt]", "[CheckRegister]","[Seq] = " & c.Seq) as Balance FROM CheckRegister as c HTH, Bob Barrows -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
Checkbook Register
On Dec 22, 9:06*pm, "Bob Barrows" wrote:
Edd wrote: I am trying to create a checkbook register with running balance in the query in Access I have spent hours and hours researching this with multiple suggestions, none of which have been successful. File=CheckRegister Fields=ID, Seq, Date Date, being a reserved keyword, is a horrible name for your field. It's also the name of a VBA function, so if you fail to rename the field, someday you will run into the dreaded "unrecognized function" error that will take you days to debug. Change the name now. Call it "TransactionDate" or something. , Ck#, CkAmt, Deposit. Does CkAmt contain positive or negative numbers? *I'm going to assume it contains positive numbers and negate them in the calculation. Change the subtraction to addition in my expressions if I've guessed wrong. Desired Result=Balance I have put in sequence rather than depending on the autonumber ID so that if I have something listed out of the desired sequence, I can alter the sequence number and resort without having to re-enter the 2 records. Most of the suggestions have included the function DSum which I have not used before. Any help would be appreciated. *I would like to have this functional by 01-01-10 You should do the running balance in a report rather than in a query. Access reports have this functionality built in IIRC. If you absolutely must have this in a query, then yes, DSum is one way to do it, although I prefer using a correlated subquery. I will show both ways: SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit, SELECT Sum(Deposit - CkAmt) FROM CheckRegister as r where r.Seq = c.Seq) as Balance FROM CheckRegister as c SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit, DSum("[Deposit]-[CkAmt]", "[CheckRegister]","[Seq] = " & c.Seq) as Balance FROM CheckRegister as c HTH, Bob Barrows -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" I do want this in a query because several different reports can be generated from this one query where the balance function would not be required. Such as year end total payments to a specific payee, check # & amounts in check# sequence, etc. thereby minimizing the number of queries I will be needing to generate the reports I require. I will change Date to TDate. However, ID, Date, and some other fields such as check#, payee, purpose are inconsequential to the purpose of the function I am trying to create. I am used to using the expression builder and have no clue where to enter the recommendation you made. I'm just wanting to be able to get the following results. CkAmt Deposit Balance 0 100.00 100.00 25.00 0 75.00 20.00 0 55.00 0 45.00 100.00 My ignorance of terminology and limited experience with making complex statements in access perhaps confused you for which I apologize. Thank you for your prompt attention to my request. |
#4
|
|||
|
|||
Checkbook Register
On Dec 22, 10:58*pm, Edd wrote:
On Dec 22, 9:06*pm, "Bob Barrows" wrote: Edd wrote: I am trying to create a checkbook register with running balance in the query in Access I have spent hours and hours researching this with multiple suggestions, none of which have been successful. File=CheckRegister Fields=ID, Seq, Date Date, being a reserved keyword, is a horrible name for your field. It's also the name of a VBA function, so if you fail to rename the field, someday you will run into the dreaded "unrecognized function" error that will take you days to debug. Change the name now. Call it "TransactionDate" or something. , Ck#, CkAmt, Deposit. Does CkAmt contain positive or negative numbers? *I'm going to assume it contains positive numbers and negate them in the calculation. Change the subtraction to addition in my expressions if I've guessed wrong. Desired Result=Balance I have put in sequence rather than depending on the autonumber ID so that if I have something listed out of the desired sequence, I can alter the sequence number and resort without having to re-enter the 2 records. Most of the suggestions have included the function DSum which I have not used before. Any help would be appreciated. *I would like to have this functional by 01-01-10 You should do the running balance in a report rather than in a query. Access reports have this functionality built in IIRC. If you absolutely must have this in a query, then yes, DSum is one way to do it, although I prefer using a correlated subquery. I will show both ways: SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit, SELECT Sum(Deposit - CkAmt) FROM CheckRegister as r where r.Seq = c.Seq) as Balance FROM CheckRegister as c SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit, DSum("[Deposit]-[CkAmt]", "[CheckRegister]","[Seq] = " & c.Seq) as Balance FROM CheckRegister as c HTH, Bob Barrows -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" I do want this in a query because several different reports can be generated from this one query where the balance function would not be required. *Such as year end total payments to a specific payee, check # & amounts in check# sequence, etc. thereby minimizing the number of queries I will be needing to generate the reports I require. I will change Date to TDate. However, ID, Date, and some other fields such as check#, payee, purpose are inconsequential to the purpose of the function I am trying to create. I am used to using the expression builder and have no clue where to enter the recommendation you made. I'm just wanting to be able to get the following results. CkAmt * * * * * * *Deposit * * * Balance 0 * * * * * * * 100.00 * * * * 100.00 25.00 * * * * * * * * * * * 0 * * * * * *75.00 20.00 * * * * * * * * * * * 0 * * * * * *55.00 0 * * * * * * * * 45.00 * * * * *100.00 My ignorance of terminology and limited experience with making complex statements in access perhaps confused you for which I apologize. Thank you for your prompt attention to my request.- Hide quoted text - - Show quoted text - I forgot to mention that Seq will be the = criteria, factor or whatever it is called. |
#5
|
|||
|
|||
Checkbook Register
Try something like the following (This query can only be built in SQL view due
the use of a non-equi join SELECT A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit , Sum(B.Deposit) - Sum(B.CkAmt) as Balance FROM CheckRegister as A INNER JOIN CheckRegister as B ON A.SEQ = B.SEQ GROUP BY A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit Alternative that can be built in query design view, but is not as efficient SELECT A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit , Sum(B.Deposit) - Sum(B.CkAmt) as Balance FROM CheckRegister as A, CheckRegister as B WHERE A.Date = B.Date GROUP BY A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit In query design view: == Add the table TWICE == SELECT all the fields you want to see from the first table == SELECT Deposit and CkAmt from the second table == Select View: Totals from the menu == Change Group By to Sum under the second instance of Deposit and ckAmt == Enter the following as criteria under the first Seq field =[CheckRegister_1].[Seq] == If you want to get the balance you need to enter the expression in another field "cell" Balance: Sum([CheckRegister_1].[Deposit]) - Sum([CheckRegister_1].[CkAmt]) == Change GROUP BY to Expression under this calculated field John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Edd wrote: I am trying to create a checkbook register with running balance in the query in Access I have spent hours and hours researching this with multiple suggestions, none of which have been successful. File=CheckRegister Fields=ID, Seq, Date, Ck#, CkAmt, Deposit. Desired Result=Balance I have put in sequence rather than depending on the autonumber ID so that if I have something listed out of the desired sequence, I can alter the sequence number and resort without having to re-enter the 2 records. Most of the suggestions have included the function DSum which I have not used before. Any help would be appreciated. I would like to have this functional by 01-01-10 Thanks |
#6
|
|||
|
|||
Checkbook Register
Edd wrote:
I am used to using the expression builder and have no clue where to enter the recommendation you made. None of this is easily buildable with the expression builder. This is the time you need to bite the bullet and start learning SQL (the language, not the database system). You need to create a query and switch it to SQL View. Then paste in the query I recommended, deleting the fields you are not interested in. Switch to Design View to see how it would have been entered in the Design grid. -- HTH, Bob Barrows |
Thread Tools | |
Display Modes | |
|
|