Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2007
    Posts
    40

    Question Unanswered: Calculate a Running Total OR Run a SUM on values BEFORE dd/mm/yyyy

    I have a table called ACCTRAN which is a list of all transactions made by customers on their accounts. Every sale, payment, settlement discount etc. is detailed in Acctran. However I don't have a running Balance, only a list of individual totals. What I need is a way to have either a running balance or to run sums on the totals between certain dates.

    If I generate an Invoice Report say between the 01/01/2008 and 31/03/2008 - a quaterly invoice - I'd like to display a Starting Balance which is the sum of every figure in the totals column before 01/01/2008 and a Final Balance which is all enteries in Totals column before 31/03/2008.

    What's the best way to achieve this?
    I have two ideas but no real method of implementing them. First I could set up a parameter query where I enter the customer's account code and it calculates a "running total" column. Then I can simply use a DLOOKUP to diplay the entry of "Running total" that match the start and end date. Most of this I can do but I've no idea how to even approach then running total part in a query, nor how to lookup if Acctran contains multiple entires on the same date.

    The other option is to have the report or query run a SUM on the [Total] column WHERE the date < dd/mm/yyyy if such an expression is even feasible.

    What do people think? Are these methods the way to approach it or is there and easier method? Either way I need some help!

    Amused

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My advice is NOT to use query parameters.

    Try DSum. That should get you going in the right direction.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I'm not sure what you mean by the parameter query. Are you sure you don't mean an action query? After all, choosing which customer account to display and calculating the running balance are two completely different operations.

    To only show the account for one customer, the best way, IMHO, is to use a subreport or subform. You'd create a larger form that had a field "customerId" or whatever and then use the subform tool to embed a form that linked to your query. Access will automatically see that it should just show the rows corresponding to the current value customerId... that much works pretty well. No annoying pop ups that you get with a parameter query, and no memorizing customerIds.

    To calculate the running balance, I assume that there's always an initial deposit or debit recorded. ST is right, you can use DSum, but Access really can't optimize the D functions. (Think about it, you're putting the table name in a string. You could have your DSum pull data from any table in the database by using an Iif function!) It's better to use a subquery, because Access will turn that into a simple join.

    If you set up TRANACC in query design view, this is what you need in one of your fields:

    Code:
    RunningBalance: (SELECT SUM(Value) FROM TRANACC x WHERE TRANACC.CustomerId = x.CustomerId AND TRANACC.Tdate < x.
    Tdate)
    The key in this code is that TRANACC is called TRANACC in the main query and 'x' (or whatever you like) in the subquery, thanks to "FROM TRANACC x".

    If you want to do the initial balance or final balance, you'd have your main account able in your query, and you'd have something like:

    Code:
    InitialBalance: (SELECT SUM(Value) FROM TRANACC WHERE ACCOUNTS.CustomerId = TRANACC.CustomerId AND TRANACC.Tdate < PeriodStartDate)

  4. #4
    Join Date
    Dec 2007
    Posts
    40
    Thanks for the replies.
    I like the idea of using a subreport to specify the customer from ACCTRAN, as you say it nicely cuts out all the pop ups. Both ACCTRAN and the report reference the same CustomerID.

    I'm struggling with the query though. This is my first project with Access so I'm very much learning as I go.

    Quote Originally Posted by sco08y
    RunningBalance: (SELECT SUM(Value) FROM TRANACC x WHERE TRANACC.CustomerId = x.CustomerId AND TRANACC.Tdate < x.
    Tdate)
    I don't get how this works, or even quite what it does. Can you explain how it works do you think? Currently I've just got one query based on the ACCTRAN table. How does the 'x' (sub)query tie in and what does it contain? How does the comparison between TRANACC and 'x' date produce a running balance? Access, it seems, doesn't come naturally to me!

    If I can get the running balance working then I can use a DLOOKUP to display the value from RunningBalance for the StartDate and EndDate the report is based on. At least initially. If theres a better way to do it I've love to know but initially it want to get my head around this RunningBalance.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Amused
    I don't get how this works, or even quite what it does. Can you explain how it works do you think? Currently I've just got one query based on the ACCTRAN table. How does the 'x' (sub)query tie in and what does it contain? How does the comparison between TRANACC and 'x' date produce a running balance? Access, it seems, doesn't come naturally to me!
    Oops; I must have not read closely and wrote TRANACC where I meant ACCTRAN.

    Anyway, put ACCTRAN aside for a second, I'll explain how subqueries work by making up some SQL and try to step you through it.

    Here's a query with a join:

    SELECT A.Whatever
    FROM Apples A INNER JOIN Oranges O ON A.ID = O.ID

    Now, you understand what INNER JOIN does pretty well, right? It finds rows where A.ID and O.ID match. (What all these columns actually mean is irrelevant.) It's the same thing as when you have a line between two tables in Access's query design view.

    In this query we're not actually using any columns from Oranges, it's just there to limit the rows being used from Apples. The A and the O are aliases, so A.Whatever is *almost* the same as saying Apples.Whatever. More on that later.

    Here's the same query, but rewritten with a subquery:

    SELECT A.Whatever
    FROM Apples A WHERE A.ID IN (SELECT O.ID FROM Oranges O)

    The IN operator just asks "is this single value A.ID IN the list of values ..."

    So the SELECT O.ID FROM Oranges O gets the list of values for ID from the Oranges table and checks that A.ID is in it. Which, when you think about it, achieves the same thing as the inner join version. The difference between these two versions is that the inner join version would allow us to put columns from Oranges into our SELECT clause.

    Next example: suppose we have a table that looks like this:

    CREATE TABLE Persons (
    PersonName VARCHAR(32) PRIMARY KEY,
    ParentName VARCHAR(32) REFERENCES Persons(PersonName)
    );

    The upshot of this is that you have a list of people and who their parents are. Suppose I wanted to get a list of grandparents:

    SELECT P.PersonName, Q.ParentName
    FROM Persons P INNER JOIN Persons Q on P.ParentName = Q.PersonName

    Earlier I said that A.Whatever was almost the same as Apples.Whatever. It's not exactly the same because as you see here, a table can be listed twice if it is aliased. That is, we're joining a table to itself, matching the field Parent to the field Person. (It might help to get out some paper and write out a few rows and run through this manually to see how it works.)

    That brings us to the code I showed you earlier.

    I'm assuming ACCTRAN has columns like so:

    Value: Dollar value of transaction, positive or negative
    CustomerID: the account
    TDate: Transaction Date

    The running balance for any given CustomerID is the SUM of the Value of all transactions up to and including the current Transaction Date.

    To select all rows from ACCTRAN, you'd say:

    SELECT CustomerId, Value, TDate FROM ACCTRAN

    To add a row, you just add it to the select clause like so:

    SELECT CustomerId, Value, TDate, ??? AS RunningBalance FROM ACCTRAN

    In this case, the ??? is the (SELECT SUM(Value) ...) I showed earlier. The 'x' allows us to use ACCTRAN twice in one query.

  6. #6
    Join Date
    Dec 2007
    Posts
    40
    sco08y, Thanks a million for the walk through.

    It took me a few read throughs but I've got the theory sort in my head now. SQL seems to be so much more powerful than using design wizards. I dare say for simple queries, having the wizard sort out the SQL for you is a boon, but the flexibilty and scope of SQL is staggering. I think I'll have to get a book on it or something. I'm already thinking ahead to other instances I can use aliases... but I'm getting ahead of my self but cause I'm still stuck on this running balance.

    Namely the said balance is nonsense. For example: on the day one account was opened with real balance of 0 it shows 333.56 as the RunningBalance and later that same day, after they've purchase something for 0.87 the balance... is still 333.56! This example might explain the problem, that more than one entry can be recorded on the same date. Because the original code only applied the sum to valves before the transaction date I thought maybe those instance when multiple entries had been made on the same date was screwing it up. I therefore made a intermediate query which took the Date_on and Time_on columns from ACCTRAN and combined then into an General Date formated field called TDATE. The theory is that the Balance query then works it's magic on thei Acctranquery. Same problem though. EXACTLY the same 333.56 recorded twice despite 87p diff etc. Any idea what might be causeing the anomaly.

    Here's the code I'm using in Balance query

    RunningBalance: (SELECT SUM([TOTAL]) FROM Acctranquery x WHERE ACCTRANquery.ACC_CODE = x.ACC_CODE AND Acctranquery.TDATE < x.TDATE)

    Thanks again for all the assistance given. It feels less of a monstrous struggle with a board of such helpful knowledgeable people.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Great post Sco08y

    Amused - type "sql tutorial" into google and you'll almost certainly find the w3 schools web site- that's a great intro. Sadly Access puts up a barrier between you and the SQL -a great shame - you would be doing very well to get proficient in SQL using just Access.

    Now - if I understand your problem correctly - if you had a single time & date column (which is typically accepted practice but not a rule - many people split them up like you too) then you shouldn't have this problem. You could use DATEADD() to add the date and time together so the date and time are in a single column. That way your less than operator should be correct. Otherwise, if you have another "sequential" column, such as an autonumber, that absolutely and in all cases corrolates with the chronology of the record then you could use that instead.
    Last edited by pootle flump; 02-04-08 at 17:29.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Amused
    I therefore made a intermediate query which took the Date_on and Time_on columns from ACCTRAN and combined then into an General Date formated field called TDATE. The theory is that the Balance query then works it's magic on thei Acctranquery. Same problem though. EXACTLY the same 333.56 recorded twice despite 87p diff etc. Any idea what might be causeing the anomaly.
    Damn - only caught this on the second read through

    Did you combine the date and time in both sides of the expression?
    Code:
     ..... AND Acctranquery.DateAndTimeCombinedColumn < x.DateAndTimeCombinedColumn)

  9. #9
    Join Date
    Dec 2007
    Posts
    40
    Hi Pootle flump

    Thanks for the thoughts. Yeah I used TDATE on both sides of the < sign.

    My current thinking is that, in theory because ACCTRAN is feed buy three separate tills, it's conceivable to have two entries made at EXACTLY the same date and time. I've not idea how often this may have happened, nor how it would cause such wildly inaccurate balances BUT my plan is to have the intermediate query - acctranquery - sort accending on the TDATE column and then put an autonumber column in. If I can get that to work, then I'll replace TDATE with SequentialNumber (or whatever) either side of the < operator.

    I'm not sure it will help at all but it's worth a try. If anyone has any other thoughts or ideas, I'd love to hear em!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah - don't start bodging things until you know what is going on.
    Code:
    SELECT TOP 5 ACC_CODE, TDATE, COUNT(*) AS rec_count
    FROM Acctranquery
    GROUP BY ACC_CODE, TDATE
    HAVING COUNT(*) > 1
    What does this return?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - something has occurred to me. Could you post your Acctranquery SQL too please?

  12. #12
    Join Date
    Dec 2007
    Posts
    40
    Quote Originally Posted by pootle flump
    Code:
    SELECT TOP 5 ACC_CODE, TDATE, COUNT(*) AS rec_count
    FROM Acctranquery
    GROUP BY ACC_CODE, TDATE
    HAVING COUNT(*) > 1
    What does this return?
    AAC 31/01/2003 11:11:00 am rec_count 2
    AAC 06/05/2005 8:40:00 am rec_count 2
    AAC 09/06/2005 3:57:00 pm rec_count 2
    AAC 13/06/2005 9:47:00 am Rec_count 2
    AAC 14/06/2005 3:50:00 pm rec_count 2

    Not sure what the code does but this is what it returns.

    Also the code for Acctranquery is
    Code:
    SELECT ACCTRAN.ACC_CODE, ACCTRAN.DATE_ON, ACCTRAN.TYPE, ACCTRAN.RECEIPT_NO, IIf([TYPE]=1,[AMOUNT],(IIf([TYPE]=3,[AMOUNT],(IIf([TYPE]=9,0,-[AMOUNT]))))) AS TOTAL, ACCTRAN.TILL_ID, ACCTRAN.TIME_ON, [DATE_ON]+[TIME_ON] AS TDATE
    FROM ACCTRAN
    ORDER BY [DATE_ON]+[TIME_ON];
    Date_on + Time_on maybe not be the right way of doing things, but it seams to work - I end us with a TDATE col which Access understand is a General Date. The TOTAL column is a little involved - basically Acctrans tends to record positive number regardless and then the Type coloumn details whether it's a sale, payment of account, payment on account etc. and so positive or negative) also Type=9 is the customer's credit limit which doesn't interest me for invocing purposes and only messed up the balance. I can't change Acctran 'cause it's imported each time from the DBASE file the Till's use.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It tells us that you were correct- there are dupes in there. For AAC there were two entries between 11:11:00 and 11:11:59 on the 31st of Jan 2003 for example. Is that the finest degree of accuracy possible? You say this comes from DBASE - is the time column defined there and only to the accuracy of one minute?

  14. #14
    Join Date
    Dec 2007
    Posts
    40
    Yeah, unfortunately the TIME_ON field is hh:mm so I can't get any more accurate.

    If I Order Acctranquery by date_on, then time_on though that will list all the entries in chronological order (or as near a damn it). What's to stop me adding an Autonumber field to assign each row a unique "TranactionNumber" say and then tell Balance Query to SUM([TOTAL] WHERE the ACC_CODE Matches AND TRAN_NUMBER < TRAN_NUMBER. Then it wouldn't matter it two entries were made at the same point in time because on would be xxxx1 and the next, made at the same point in time would be xxxx2?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - but your balances will be out. Your autonumber is arbitary. You will be producing false balances since you don't actually know the chronological order of the transactions. I would be temped to sum the transactions per minute and report on those. You won't then be reporting false information.

    It depends on your needs though.

    Ultimately I would suggest that the original designer did not account for sufficient precision when designing the system and if possible you could do with making a tweak in the original design to at least account for the order (if not the precise time) of transactions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •