none
Reports and DSum expression. Debtors Balance. RRS feed

  • Question

  • My company use mainly 5 documents. So videos taught me to setup 5 main tables with a sub table each. When I want to setup a report, customer list with balances it seems the DSum expressions I set up are done correct. I have other DSum expressions with no speed issues. When I do this one, my database hangs. There are less than 1000 records in the database so far. It should go fast when I have one million records? What is the easier way of setting up a customer list with balances? I went through it many times, there could be something somewhere, but I dont know yet.
    Wednesday, December 23, 2015 7:36 PM

All replies

  • My company use mainly 5 documents. So videos taught me to setup 5 main tables with a sub table each. When I want to setup a report, customer list with balances it seems the DSum expressions I set up are done correct. I have other DSum expressions with no speed issues. When I do this one, my database hangs. There are less than 1000 records in the database so far. It should go fast when I have one million records? What is the easier way of setting up a customer list with balances? I went through it many times, there could be something somewhere, but I dont know yet.

    Hi Hans,

    Does the database really hang, or is it just very slow?

    If it hangs, then it is probably some kind of programming error. Try to find the spot with the debugger. It also can be that there are to many "handles" in use, because of all DSum's.

    When the database is very slow, you can look at the keys of the tables.
    The equivalent of DSum is:

        ThisSum = CurrentDB.OpenRecordset ("SELECT SUM (Amount) AS Sum_amount FROM Transaction_tbl WHERE Debtor_id = ... AND Year(Transaction_date) = ...")!Sum_amount

    If  Transaction_tbl has many recods, and  Debtor_id or Transaction_date are not key values, then the retrieval can take some time, especially when you this is part of one or more loops.

    Imb.

    Wednesday, December 23, 2015 8:13 PM
  • Hi. My Email address is . I also don't mind if we connect through "teamviewer". I'm proud of what I got done in 100 days, but I still know very few SQL or Macro commands. If I am going to make your suggested expression work I need to be spoonfed exactly. The expression that calculates customer balances must fetch numbers from 5 tables. It seems if I calculate 1/5 table in a new field it still have no speed issue. I calculated the 5 separate and it it went well. When I then add up those 5 together it "hangs"again. It could be very slow, but I waited long before I lost patience and forced the calculation to stop.

    =DSum("[isdebit]","q06InvSal","[CmbEntID]=" & [CusId])-dsum("[brdebit]","q06BnkRct","[CmbEntID]=" & [CusId])+dsum("[bpcredit]","q06BnkPmt","[CmbEntID]="&[CusID])+dsum("[jnldtVat]","q06JnlSub","[CmbEntID]="&[CusID])+dsum("[jnldebit]","q06JnlSub","[CmbEntID]="&[CusID])-dsum("[jnlcredit]","q06JnlSub","[CmbEntID]="&[CusID])-dsum("[jnlcrVat]","q06JnlSub","[CmbEntID]="&[CusID])

    I would like to be able to set up a statement that age the customer balance as well. I believe I may get it done but now the speed issue is a problem. I wonder how to do the aging. Current month, 30-60days, 61-90, and 91-120 and over. I have a simple idea, but would like to learn.


    Thursday, December 24, 2015 10:03 AM
  • =DSum("[isdebit]","q06InvSal","[CmbEntID]=" & [CusId])-dsum("[brdebit]","q06BnkRct","[CmbEntID]=" & [CusId])+dsum("[bpcredit]","q06BnkPmt","[CmbEntID]="&[CusID])+dsum("[jnldtVat]","q06JnlSub","[CmbEntID]="&[CusID])+dsum("[jnldebit]","q06JnlSub","[CmbEntID]="&[CusID])-dsum("[jnlcredit]","q06JnlSub","[CmbEntID]="&[CusID])-dsum("[jnlcrVat]","q06JnlSub","[CmbEntID]="&[CusID])

    Hi Hans,

    It is probably the complexity of the DSum function that does the slowdown.

    You could try a step inbetween by making a table where you can store the separate calculations. In this table you then have a field for CusID, and if you wish also for the period, and fields for the separate sums. That is at the same time the solution for the aging. Strictly spoken this would introduce redundancy, but if you take your measures to update the different values after a change (Current month just before you want to make the report, the older periods only when something has changed), in my opinion that is acceptable.

    It is perhaps a good idea to remove your email-address from your post. The whole can see it and (mis)use it.

    Imb.


    edit:  and use this table for the report.
    • Edited by Imb-hb Thursday, December 24, 2015 11:18 AM edit
    Thursday, December 24, 2015 11:17 AM
  • Where did my reply go? Geseende Kerfsfees Imb. Ek wonder hoeveel jy van my afrikaans verstaan, my voorouers van 300 jaar terug is van Holland? 

    I wonder why, but in a table it allows an easy expression in a calculated field but does not allow a DSum expression. I wondered if you meant a query. My computer is strong enough, and the other similar DSum expressions gives no hassles. I have setup statements for customers successful with the same DSum expression, time delay is really short even if statement is 10 pages or more. It can not be an Access problem, it is something I have to find out. I just tested it in a query and again. Using one field to add up Sales Invoices from t01InvSal it works and fast. So does the other 4. When I create a field adding up those 5 or doing the whole expressionI sent you, it takes very long or it hangs in my opinion.

    Thursday, December 24, 2015 9:20 PM
  • Where did my reply go? Geseende Kerfsfees Imb. Ek wonder hoeveel jy van my afrikaans verstaan, my voorouers van 300 jaar terug is van Holland? 

    I wonder why, but in a table it allows an easy expression in a calculated field but does not allow a DSum expression. I wondered if you meant a query. My computer is strong enough, and the other similar DSum expressions gives no hassles. I have setup statements for customers successful with the same DSum expression, time delay is really short even if statement is 10 pages or more. It can not be an Access problem, it is something I have to find out. I just tested it in a query and again. Using one field to add up Sales Invoices from t01InvSal it works and fast. So does the other 4. When I create a field adding up those 5 or doing the whole expressionI sent you, it takes very long or it hangs in my opinion.

    Hallo Hans,

    Ik wens jou ook prettige Kerstdagen en een gelukkig Nieuwjaar. Je Afrikaans kan ik goed lezen en begrijpen. Maar dat wil niet zeggen dat ik het ook kan spreken. Nog een uur en dan is het Kerstmis. Hopelijk kunnen we de Kerstgedachte wat langer vasthouden dan die ene dag.

    I have looked again at your code with the numerous DSum's. Did this code compile right? What I seen is a couple of times "dsum", whereas in valid compiled code it would be "DSum". Or is this an Expression for a control? Would it help if you add spaces, especially round the "&", for a better (?) interpretation?

    Imb.

    Thursday, December 24, 2015 9:57 PM
  • Ja wanneer ek Nederlands lees of hoor verstaan ek elke woord, maar ek kan dit ook nie praat nie. Kersdag is nie omdat iemand die dag gebore is nie. Dis vir die wat n goeie daad wil doen dit bevestig. Jy se ek moet my eposadres verwyder want seker mense kan dit misbruik. Die wereld se mense is sleg, ek voel nie ek hoort hier nie. Veral ook in SA voel dit of elkeen wat kan van jou sal steel of lieg of haat. Of het die skepper ook die lelike dinge gemaak? Smile.

    I will change DSum everywhere so the capital letters is exact. I will add spaces " & " to test.

    Friday, December 25, 2015 2:35 AM
  • Ja wanneer ek Nederlands lees of hoor verstaan ek elke woord, maar ek kan dit ook nie praat nie. Kersdag is nie omdat iemand die dag gebore is nie. Dis vir die wat n goeie daad wil doen dit bevestig. Jy se ek moet my eposadres verwyder want seker mense kan dit misbruik. Die wereld se mense is sleg, ek voel nie ek hoort hier nie. Veral ook in SA voel dit of elkeen wat kan van jou sal steel of lieg of haat. Of het die skepper ook die lelike dinge gemaak? Smile.

    I will change DSum everywhere so the capital letters is exact. I will add spaces " & " to test.

    Hallo Hans,

    About the email address, it is not really necessary to remove it, but make it unrecognisable for email grabbers, by adding spaces or replace "." by "dot" and "@" by "at".

    The capital letters are not important, it is only a sign that, in VBA, the code was not compiled. But I think you use it as an Expression, and I don't think Expressions are compiled.

    I understood that the individual DSum execute at normal speed. And the question is why the complex expression takes so long to execute.

    You could place the whole expression in a function in VBA, like:

    Function Total() As Currency
    
      Total = DSum("[isdebit]","q06InvSal","[CmbEntID]=" & [CusId])-dsum("[brdebit]","q06BnkRct","[CmbEntID]=" & [CusId])+dsum("[bpcredit]","q06BnkPmt","[CmbEntID]="&[CusID])+dsum("[jnldtVat]","q06JnlSub","[CmbEntID]="&[CusID])+dsum("[jnldebit]","q06JnlSub","[CmbEntID]="&[CusID])-dsum("[jnlcredit]","q06JnlSub","[CmbEntID]="&[CusID])-dsum("[jnlcrVat]","q06JnlSub","[CmbEntID]="&[CusID])
    
    End Function

    and place this function in the Form's (or Reports's) module. You could the step through the different parts with the debugger. But it is just an idea to understand what is going on.

    An other thought that I had is this, but I am not sure as I have not experimented with that. I can imagine an Expression is regularly updated, as parameters in the Expression can change. In this scenario the computer is hard working to update the Expression, and update, and update, leaving no time to do other things.

    Imb.

    Friday, December 25, 2015 7:15 AM
  • What is any system if you can not have a customer list with balances? Even in the VBA it seems I copied your suggestion, maybe I did it wrong? If your client that use your database have 3 bank accounts. Do you put them all into one table, with all the other transactions of their business? As an accountant I strongly believe the 3 bank accounts should have their own receipt and payment number order. By law we should file proof of expenses and that number order of course is very important. Do you number maybe 8 different documents by computing. That will not do it for me. A table can have only one auto number??

    Is my current calculation issue because of a Dsum expression from 5 tables. Do you agree that Access doesn't allow DSum expression in a table. It does allow these expressions on query, forms, and reports. You say here above. Form's or Report's module. Do I think wrong if I have been taught so far that forms are for edit and input. Help me right if forms can help me to a customer list with balances. My intuition tells me it is not possible for Microsoft not to have a solution.

    When your system takes an hour to do the calculation, what is too long? To me 5 minutes is too long. There is not a 1000 transactions in here yet, it should work rather easily with 1 million transactions, right?

    Sunday, December 27, 2015 8:10 PM
  • What is any system if you can not have a customer list with balances? Even in the VBA it seems I copied your suggestion, maybe I did it wrong? If your client that use your database have 3 bank accounts. Do you put them all into one table, with all the other transactions of their business? As an accountant I strongly believe the 3 bank accounts should have their own receipt and payment number order. By law we should file proof of expenses and that number order of course is very important. Do you number maybe 8 different documents by computing. That will not do it for me. A table can have only one auto number??

    Is my current calculation issue because of a Dsum expression from 5 tables. Do you agree that Access doesn't allow DSum expression in a table. It does allow these expressions on query, forms, and reports. You say here above. Form's or Report's module. Do I think wrong if I have been taught so far that forms are for edit and input. Help me right if forms can help me to a customer list with balances. My intuition tells me it is not possible for Microsoft not to have a solution.

    When your system takes an hour to do the calculation, what is too long? To me 5 minutes is too long. There is not a 1000 transactions in here yet, it should work rather easily with 1 million transactions, right?

    Hi Hans,

    Before I started to answer your post, I have read it at least 5 times. It sounds as if you are a little disappointed in the possibilities of Access to do the DSum calculation in a reasonable time. I have tried to understand what the real question are behind your words in order to give a handle towards your problems. Unfortunately, I only use A2003, and I have no experience in any higher version of Access, so connect directly to your database is no option. Another point is, have we the same model in mind when we are discussing about the problems.

    For me 5 seconds is already pretty long, not to ask about minutes or hours. It should make not much difference if you store in 5 different tables, or in one table. In most of my accounting systems I use separate tables for different Bank accounts, but the "important" fields (date, description, account) are copied to a Transaction_tbl (you could compare this to a Union of the 5 different table over a common set of fields). To this Tansaction_tbl are added reference fields for Bookyear_id, Ledger_id, Client_id, Bankaccount_id, Invoice_id, ... This Transaction_tbl then is the basis for het whiole accounting system, with no problems on speed in my case.

    When I spoke about Dsum in a table, I did not mean the DSum expression in a table, but its result. In that respect I see forms as the interaction between user and data, not necessarily only input or editing. Also signaling and analysis covered with forms. Reports I seldom use, and are more for external use.

    In all my applications all records have an autonumber. This is only a unique number to identify a number, is used only inside the database without any meaning in the rest of the database. Don't confuse this with an unique, contiguous number for a bank account, or invoice number. So in that light you can have as many "onw generated auto numbers" as you want.

    Are we still synchronizing the models in our heads?

    Imb.

    Sunday, December 27, 2015 10:56 PM
  • I am the rookie and you the veteran. I have Access 2013, but I don't think it makes the difference here. If you connect to my computer through "Teamviewer" you will work on Access 2013 of course. I don't mind if you have the file, it can not harm me, but I don't know if you have it on your system whether 2003 will fail.

    As you say 5 seconds is too long. In the beginning Ken Sheridan called it a "Type and sub type table". I learnt something from that and from you. I thought here above in your last reply that is what you refer to. As you say there is a transaction table with all the transactions in a company, with all the similar fields that the documents keep. Then the sub table which has a foreign key to the transaction table to join. The auto numbering takes place in the "sub type Table". But yes I just checked again, only one auto number per table.

    I owned a second company in 2008 to 2012 and many people are still owing me money. I am doing the old work already on the new dbase I developed, and it work well. It gives a nice account of a customer even if it adds up to 10 or 20 pages. I am using the running balance since I always want to print from the first transaction the customer did. I still would like to solve the "opening balance" and "hide Zero" issue as discussed before. While I don't need that, I will leave it for later. I have two boys and two girls. Girls 28,26. Boys 23,18. Ruben goes to programming college in Jan, and will try to find me answers at the college. Werner 23 is a technician an have many friends in programming, but we haven't found somebody that really knows Access.

    But not to have a customer list with balances is an issue for me. I duplicate and waste time to keep this list in Excell at the moment. On the ribbon or menu in Access 2013 I clicked on database tools and then Visual Basic and copied your suggested expression. There was already code. Just the Docmd.Maximize, I pasted it below that in a REPORT as I understood from you, but it doesn't work for me.

    You say synchronize. Yes please, maybe I am behind in understanding. If you can help by really spoon feeding like saying click on VBA and copy the exact formula or what must I look out for? Surely we synchronize on the small difference between a ledger account and a statement for a customer. Same transactions, maybe a statement can have aging on it. But again, I have to have a customer list with balances. I created the list, and have a few other Dsum expressions but this one still "hangs" when I add up all the tables. I will put in more thought in my inexperience what it can be in the way it is set up. The same info though gives me the account successful.

    Sorry for the hassle.

    Monday, December 28, 2015 12:16 AM
  • Hi Hans,

    Based on your description, it seems your speed issue was related with your DSum statement. I suggest you try suggestion from Imb to make table where you can store the separate calculations. Or, I suggest you split it into many simple queries for a try.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, December 29, 2015 5:34 AM
  • Edward. Thank you. The other 4 forums I tried, never even got a reply. So far on my 8 threads I haven't got answers that worked, but a reply each time helped. I have set up a table as suggested. The message is "The expression =DSum("[isdebit]","q06InvSal","[CmbEntID]=" & [CusId]) can not  be used in a calculated column". If I do this same expression in a query or report it works without speed problems. If I do 5 fields from 5 tables separately it works. But when I do them all together, it hangs. If I had the 5 separate ones that worked, it would have worked if I create a field with an expression to add them together. But no it doesnt hang on the 5 separately, but it does on the total one????
    Tuesday, December 29, 2015 7:23 PM
  • Edward. I clicked on Here for I am happy to use time for your survey. What I saw was not a survey. I just tested the expression again. Lets say 5 parts. I added the parts one by one. Up to the fourth one, even 3 seconds will be OK. When I add a 4th part it hangs. I swapped it around thinking the hassle can be in the 5th one. But no, when the 5 one was now part of 4 it worked, when I add 5th part it hanged.
    Tuesday, December 29, 2015 8:44 PM
  • I just tested the expression again. Lets say 5 parts. I added the parts one by one. Up to the fourth one, even 3 seconds will be OK. When I add a 4th part it hangs. I swapped it around thinking the hassle can be in the 5th one. But no, when the 5 one was now part of 4 it worked, when I add 5th part it hanged.

    Hi Hans,

    What happens if you run the next function:

    Function Sum_it(cur_id As Long) As Currency
    
      Sum_it = DSum("isdebit", "q06InvSal", "CmbEntID = " & cur_id) _
             - DSum("brdebit", "q06BnkRct", "CmbEntID = " & cur_id) _
             + DSum("bpcredit", "q06BnkPmt", "CmbEntID = " & cur_id) _
             + DSum("jnldtVat", "q06JnlSub", "CmbEntID = " & cur_id) _
             + DSum("jnldebit", "q06JnlSub", "CmbEntID = " & cur_id) _
             - DSum("jnlcredit", "q06JnlSub", "CmbEntID = " & cur_id) _
             - DSum("jnlcrVat", "q06JnlSub", "CmbEntID = " & cur_id)
    End Function
    

    Place this function in a standard module. Then you call the function with

        result = Sum_it (CusId)

    Does it hang then also? If yes, you can step through the function with the debugger and see which part gives the problem.

    You can leave of the square brackets if the name of table, field or control does not contain "forbidden" characters.

    Imb.

    Tuesday, December 29, 2015 11:03 PM
  • As I learned from you also. I have 7 categories or I called them Combined entities. I believe like you would suggest. I set up one table keeping at least 2 fields of similar fields for the 7. (Agents, Customers, Employees, Creditors, Suppliers, Vehicles and Other Subledger Accs). This main table is called Type table by Ken and its SUB TYPE TABLES keeps the other fields including autonumber. I just made sure that each query do have that CmbEntID field and it has. CusID is the ID field of my Customer table and query. In the 120 days since I opened Access for the first time this will be the first module I try.  I clicked on create and then module and I copied the expression exactly like you suggest. I replaced cur_id with CusID.

    I then gone back to the query and added a field. In the field I put Balance: =Sum_it(CusID). When I try to view it gives an error. "Syntax Error (missing operator) in query expression 'CmbEntID ='.

    Wednesday, December 30, 2015 2:26 AM
  • I replaced cur_id with CusID.

    I then gone back to the query and added a field. In the field I put Balance: =Sum_it(CusID). When I try to view it gives an error. "Syntax Error (missing operator) in query expression 'CmbEntID ='.

    Hi Hans,

    I cannot see what you have done, but replacing 'cur_id' by 'CusID' within the function is not necessary. 'cur_id' in this context is just the local CusID, thast is passed as parameter to the function.

    If you want to use 'CusID' as the local variable, then you must change the name also in the function declaration, as in

            Function Sum_it(CusID As Long) As Currency

    Personaly I prefer to use a local name for the local variables, tp prevent confusion between what varuable is used in what context.

    The field 'CmbEntID' must be an existing field.

    Imb.

     

    Wednesday, December 30, 2015 7:28 AM
  • Sorry for the hassle. I copied it again, not changing cur-id or anything. In the query in the added field I copied to the expression = Sum_it (CusId). Error is ' "Invalid use of Null"
    Thursday, December 31, 2015 3:39 AM
  • Sorry for the hassle. I copied it again, not changing cur-id or anything. In the query in the added field I copied to the expression = Sum_it (CusId). Error is ' "Invalid use of Null"

    Hi Hans,

    You get this error because CusId has a null value. Is CusId a control on the form? Then you could use  Me!CusId.

    Else look for a variable or control that has the value of CusId, and use that as parameter for Sum_it.

    Imb.

    Thursday, December 31, 2015 2:03 PM
  • Voorspoedige nuwe jaar vir jou.

    CusId is the primary key field, (not autonumber) in my customer table. As I said, to set up the customer list was easy. But to add a field with an expression that gives the balance is the issue. This CusId field is also the foreign key to Join CmbEntID in the Combined entity table as I described above. Combined Entity the TYPE table and Customers one of the SUB TYPE tables. It sounds this is the way you do things.

    Sorry, maybe I don't understand if you say CusID has a null value. I checked the field, sorted it, none of the records have a null value. My intuition is trying to tell me if we solve this error it is possibly going to work, I wish.

    CusId is not a control on a form. I have not worked at all with a form here, just a query, for the expression is not allowed in the table. If you feel a form is the way please tell me. As I said, I tested the expression on a report too, same results than in the query. When I do one part at a time, it goes well on a report. When all of them together, it hangs. Am I correct wanting to have the answer in a query?

    You wrote. "Else look for a variable or control that has the value of CusId, and use that as parameter for Sum_it. Sorry too much of a Rookie, I don't know what your saying.

    Thursday, December 31, 2015 7:19 PM
  • Voorspoedige nuwe jaar vir jou.

    CusId is the primary key field, (not autonumber) in my customer table. As I said, to set up the customer list was easy. But to add a field with an expression that gives the balance is the issue. This CusId field is also the foreign key to Join CmbEntID in the Combined entity table as I described above. Combined Entity the TYPE table and Customers one of the SUB TYPE tables. It sounds this is the way you do things.

    Sorry, maybe I don't understand if you say CusID has a null value. I checked the field, sorted it, none of the records have a null value. My intuition is trying to tell me if we solve this error it is possibly going to work, I wish.

    CusId is not a control on a form. I have not worked at all with a form here, just a query, for the expression is not allowed in the table. If you feel a form is the way please tell me. As I said, I tested the expression on a report too, same results than in the query. When I do one part at a time, it goes well on a report. When all of them together, it hangs. Am I correct wanting to have the answer in a query?

    You wrote. "Else look for a variable or control that has the value of CusId, and use that as parameter for Sum_it. Sorry too much of a Rookie, I don't know what your saying.

    Hallo Hans,

    I think I begin to understand what you want, and your context.

    What happens if you run this query:

        "SELECT CusId, Sum_it(CusId) AS Balance FROM Customer_tbl WHERE CusId = ..."

    Replace Customer_tbl by the real name of the customer table, and ... by a real value. Can you see then a value for Balance, or hangs the program?

    Still 1,5 hour to go before NewYear... In ieder geval een succesvol 2016!

    Imb.

    Thursday, December 31, 2015 9:35 PM
  • I thought you were east of us. 10 minutes to go here. This time I'm only with a couple of friends, they are tired. Crackers already going of in Benoni JHB. My children by the sea 600km away. Wife in heaven. So me and my best friend the computer. 

    I copied exactly what you said to a field in my query. t01customers replacing. What do you mean by a real value? 3 min to go. Next chat in 2016.

    Thursday, December 31, 2015 9:58 PM
  • I thought you were east of us. 10 minutes to go here. This time I'm only with a couple of friends, they are tired. Crackers already going of in Benoni JHB. My children by the sea 600km away. Wife in heaven. So me and my best friend the computer. 

    I copied exactly what you said to a field in my query. t01customers replacing. What do you mean by a real value? 3 min to go. Next chat in 2016.

    Hi Hans, Een gelukkig Nieuwjaar!

    With 'real value' I mean an existing value, a real life value.

    Imb.

    Thursday, December 31, 2015 11:45 PM
  • By now you may understand t01CmbEnt is my TYPE TABLE which keeps all categories, and t01Customers is one of the SUB TYPE TABLES joined through foreign key CusId(also primary key but not autonumber) with CmbEntId(Primary key and auto number) in t01CmbEnt. In t01Cutomers I have also a field CusNo which is the auto number just for customers, but not the primary key. CusId is the primary key as well as the foreign key as suggested by Ken. We only wrote a couple of times a month ago.

    If I put Balance: "SELECT CusId, Sum_it(CusId) AS Balance FROM t01Customers WHERE CusId = 456" this in a field in a query because of the quotation marks it results in showing whatever I type between the quotation marks. I tested adding the equal sign or tested without quotation marks, but it comes up with a long error message. I need you to say exactly what I should do.

    At this moment I have the module copied exactly as your suggestion 29Dec15 11:03pm. In your same suggestion you say call the function with; result = Sum_it (CusId). Tell me exactly for example. In a query field must I type the word result. For I have tested = Sum_it(CusId). If I have to do anything on a form say so please, because you mentioned that. Are we synchronizing, do you understand that I try to do this in a query? Is that OK?

    I opened a query opened tables t01CmbEnt and t01Customers. I pulled down CusId and CmbEnt and EntityNme. Those are the 3 fields in my CustomerListBalance query. The 4th field is what we are struggling for. The Balance, then I have what I need.

    Later you wrote 

    What happens if you run this query:

        "SELECT CusId, Sum_it(CusId) AS Balance FROM Customer_tbl WHERE CusId = ..."  I put in the query field

    "SELECT CusId, Sum_it(CusId) AS Balance FROM t01Customers WHERE CusId = CmbEntID"

    Sorry if I miss you on writing "run this query" That is what I understand. Am I correct? Help here if I miss you. Where exactly must I put this expression, and still the last part real value, should I type something else than CmbEntId? I have tried 456. Tell me if I should Type these in VBA or a macro, or is the field in the query the right place?

    Friday, January 1, 2016 2:48 AM
  • Amsterdam seems to be an hour west of Johannesburg, and London an hour west of Amsterdam.
    Friday, January 1, 2016 2:50 AM
  • If I put Balance: "SELECT CusId, Sum_it(CusId) AS Balance FROM t01Customers WHERE CusId = 456" this in a field in a query because of the quotation marks it results in showing whatever I type between the quotation marks. I tested adding the equal sign or tested without quotation marks, but it comes up with a long error message. I need you to say exactly what I should do.

    Hi Hans,

    A couple of things are now going on at the same time, and I think that is a little confusing.

    In my opinion the first problem is to understand why your program hangs when you want to make up the Balance of a customer. This Balance is the sum of a couple of DSum functions, which individually run as expected. To have more control, the Balance calculation is put in a function Sum_it.

    And now test it: Make a QueryDef, and in the SQL mode add add the string to QueryDef.SQL:

            SELECT CusId, Sum_it(CusId) AS Balance FROM t01Customers WHERE CusId = 456

    and now execute (run) the Query. Does the program hang, or have you a meaningfull result within a few seconds?

    If the program hangs (takes far too much time), than you can execute the query again, but now with a Stop in the program of Sum_it, so that you can step through to code line by line, and see where the problem comes for.

    If there is no hanging, than you could take off the WHERE part of the QueryDef to generate an overview for all CusId.

    Part of the confusing ius probably the difference in the way of working. I never use the QueryDefs. Further my interaction with the tables is always through forms.

    Let me know if this is clear, and what your results are.

    Imb.

    Friday, January 1, 2016 4:17 PM
  • I haven't tested it yet, but will find quality time. There are up to  a 100 reports that I want to create in Jan and Feb, and this expression thing is a challenge. Since 1990 I taught myself using Lotus123, and dbaseIV and all other Microsoft products, never becoming really good but I could use it. Surely there is no chance to fail. There must be an expression that works, it must be the most common need worldwide. I trained myself through more than 200 videos on Access in the past 4 months, even though I haven't given good attention to VBA or SQL yet.

    The videos led me to create a few tables for transactions and not one. Those guys surely don't have it wrong all the time. The videos taught me the DSum expression and it works on the few I have done so far. I need more time to test and try.

    You say your interaction with tables is always through forms. Most reports I do need a query according to my training, with no form involved. I have asked you a few times, in this challenge we have. How do you see a form involved in getting this Customers balance? I will test and try more and let you know.

    Saturday, January 2, 2016 5:37 PM
  • You say your interaction with tables is always through forms. Most reports I do need a query according to my training, with no form involved. I have asked you a few times, in this challenge we have. How do you see a form involved in getting this Customers balance? I will test and try more and let you know.

    Hi Hans,

    That is a good point, I have to sharpen my statements.

    All data in the tables are accessed through queries, that retrieve datasets form the tables. These queries can be the RecordSource of a form, or the RecordSource of a report.

    For interactive purposes I use the forms, for more or less static overviews I use reports. These reports are just to print, or send away to an other person. In this light reports are not so important for me, but forms are. A little off-discussion is that I do not use Access reports, instead I directly generate RTF- or HTML-code.

    If you want to have an overview of all your Customers, you can make a Customers report. Almost in the same way you can make a Customers form, showing the same details. But there is far more power in it, because you have quite a lot of Events that can be triggered depending on what is happening. And, very important, forms are used for data input and correction.

    The guys who made the videos are right, that is _a_ way to make information in the database visible. But there are more ways.

    That does not mean that you have to switch to forms immediately. Your goal is to produce the Balance overview of the Customers, and that can also be done using a report. Tpically, the sending of a Balance overview to the Customer is the use of a report. When you have some more time later on, you can start with the forms.

    But before the report is ready, you still have the problem of the performance (the hanging) of the query.

    Can you post the RecordSource of your report? This is the same as the SQL-string of the QueryDef that you use for the report.

    I hope I do not confuse you too much. My "thinking of applications" is more form-oriented than report-oriented, and that makes that we sometimes speak different languages. As with the difference between Zuid-Afrikaans and Dutch: you can feel what the other means, but we can not use it.

    Imb.


    • Edited by Imb-hb Saturday, January 2, 2016 7:34 PM to the Customer
    Saturday, January 2, 2016 7:33 PM