Results 1 to 3 of 3
  1. #1
    jonathanjilla is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    5

    Comparing records using forms, reports or queries

    Hi,

    New user here and I have tried researching my problem but cant seem to get an answer.

    Background:
    I have a tank of gasoline which I need to monitor the sales from. I need a report at the end of the month showing how much I am short or over in gallons on a daily basis. I would also need the total over/short for the whole month (ie the summation of the daily over/short).

    I have a table called Canoga. It has the following columns:
    Date Volume Sales
    10/1/11 600 80
    10/2/11 540 70


    10/3/11 500 25
    and so on

    The volume is the closing volume at the end of the day.
    So beginning with 10/2/11 I need a report showing the above data plus an over/short column.
    For 10/2/11 it would say -10 (=600-540-70)
    For 10/3/11 it would say +15 (=540-500-25)

    So far the approach I tried is using dlookup in a textbox and pulling the previous days volume by matching the dates minus 1

    Please help, I've gone all over the internet looking for an answer but had no luck.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,622
    You want to calculate consecutive records. This means a self-join of Canoga table. Try:

    SELECT Canoga.[Date], Canoga.Volume, Canoga_1.Volume, Canoga_1.Sales, [Canoga].[Volume]-[Canoga_1].[Volume]-[Canoga_1].[Sales] AS OverShort
    FROM (Select [Date]-1 As PrevDate, Volume, Sales FROM Canoga) AS Canoga_1 INNER JOIN Canoga ON Canoga_1.PrevDate = Canoga.[Date];

    This requires no gaps in date. If gaps are possible, need another unique ID that can be relied on for no gaps to use as join keys.

    BTW, Date is a reserved in VBA and Access. Should not use reserved words as names. Better would be DateRead. Also, no space, punctuation (underscore is exception), or special characters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jonathanjilla is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    5
    Thanks for your help, working like a charm!

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

Similar Threads

  1. Using Public variables between forms/queries/reports
    By dcrake in forum Sample Databases
    Replies: 2
    Last Post: 12-25-2015, 05:44 PM
  2. reports to forms to queries
    By aaa1 in forum Forms
    Replies: 9
    Last Post: 08-10-2011, 07:29 PM
  3. 2010 and 2007 Access Queries, Forms & Reports
    By rpaldridge in forum Import/Export Data
    Replies: 3
    Last Post: 02-11-2011, 07:37 AM
  4. Splitting DB; Keeping the Forms, Queries, Reports in BE
    By evander in forum Database Design
    Replies: 2
    Last Post: 07-07-2010, 04:59 PM
  5. Queries, Forms, Reports..
    By groundhog in forum Access
    Replies: 3
    Last Post: 07-07-2010, 12:30 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 - Senior Forums