Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Location
    Orlando, FL
    Posts
    1

    Calculating Values

    My table essentially revolves around two columns: Date and Revenue.

    I would like to create a query that in addition to the two above mentioned columns, displays the previous days revenue (the previous record) and in a separate column displays the difference between yesterdays and todays revenue.



    Any help would greatly be appreciated.

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Jahan,

    I am assuming that Date is the primary key, or that at the least there is a unique index on that field. BTW, using Date for a field name is a Bad Idea, as that is also an Access and VBA reserved word.

    Something like this should work:

    1) Create another query, qry1 or some other name, with SQL like this:

    SELECT t1.[Date],
    (SELECT Nz(t2.Revenue, 0)
    FROM YourTable AS t2
    WHERE t2.[Date] = (t1.[Date] - 1)) AS PrevDayRev
    FROM YourTable AS t1

    2) Now, make your main query something like:

    SELECT t1.[Date], t1.Revenue, t2.PrevDayRev, (t1.Revenue - t2.PrevDayRev) AS RevenueDiff
    FROM YourTable AS t1 INNER JOIN
    qry1 AS t2 ON t1.[Date] = t2.[Date]
    ORDER BY t1.[Date]

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating a sum on a report
    By missourijc in forum Reports
    Replies: 0
    Last Post: 10-30-2008, 07:21 PM
  2. calculating due dates in access
    By trixxnixon in forum Forms
    Replies: 0
    Last Post: 09-28-2008, 12:35 PM
  3. Replies: 0
    Last Post: 09-25-2008, 12:19 PM
  4. Form calculating troubles
    By Ufalufa in forum Access
    Replies: 0
    Last Post: 11-27-2007, 10:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums