Results 1 to 7 of 7
  1. #1
    KattDM is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Location
    Connecticut
    Posts
    3

    Post Interval Between Dates in Table

    For years I have tracked heating oil usage in a spreadsheet. I made a simple Access database to replace it. It is intended to calculate the interval between delivery dates and daily usage (gallons). These subquery instructions from Microsoft's website were very helpful and I was able to replicate the result in the example in a copy of the Northwind database, but I cannot make it work in my own database. My database consists of one table, Deliveries. I have a query named qry Deliveries. Per the example from Microsoft's website, I made another query containing the columns DELIVERY ID (sorted ascending) and DELIVERY DATE (sorted descending), then the subquery column with the following:



    Prior Date: (SELECT MAX ([DELIVERY DATE])
    FROM [Deliveries] AS [Old Orders]
    WHERE [Old Orders].[DELIVERY DATE] < [Deliveries].[DELIVERY DATE]
    AND [Old Orders].[DELIVERY ID] = [Deliveries].[DELIVERY ID])

    Next column calculates the interval: [DELIVERY DATE]-[Prior Date]

    When the query is executed, the two new columns for the subquery and interval are blank. It's driving me nuts.

    Although I have looked through this forum and found similar questions, the answers don't quite match up to this simple format from Microsoft's support page. Any thoughts about what has gone wrong? Thank you!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    time between dates (in days ;d) :

    =DateDiff("d",[date1],[date2])

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might have to post some actual data for a solution. Your sql doesn't make sense to me if in my head I replace Max(DELIVERY DATE) with a date value. You could test this by copying the sql to play with it and substituting a date for the Max function but I don't think that will help. Usually the nested SELECT statement returns a value that is used as part of a WHERE clause. You're not doing that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    KattDM is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Location
    Connecticut
    Posts
    3
    There is only one date column.

  5. #5
    KattDM is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Location
    Connecticut
    Posts
    3
    I'll attach an Excel spreadsheet which I exported from Apple Mac, my original working file. I don't see an attach button. I'll insert a section.

    DELIVERY DATE GALLONS HOUSE GALLONS BARN PER GALLON $ TOTAL $ DAYS AVG GALLONS PER DAY ANNUAL GALLONS ANNUAL COST
    12/28/2006 85.100
    $2.179 $185.43



    02/02/2007 97.900
    $2.329 $228.01 36d 2.72

    03/07/2007 119.300
    $2.399 $286.20 33d 3.62

    05/14/2007 108.300
    $2.479 $268.48 68d 1.59

    10/16/2007 11.200
    $2.729 $30.56 155d 0.07

    12/22/2007 141.000
    $3.379 $476.44 67d 2.10 562.8 $1,475.12

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't see a deliveries ID field in your example data. If this is the id for the row, (i.e. does not represent a location or some other entity) then you do not need to include it in your subquery criteria


    Always a bad idea to have spaces in field and table names

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

Similar Threads

  1. Find which interval a record is in
    By Vasha in forum Queries
    Replies: 3
    Last Post: 02-12-2019, 10:30 AM
  2. Replies: 5
    Last Post: 07-12-2014, 02:55 PM
  3. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  4. Time interval calculation
    By gar in forum Queries
    Replies: 3
    Last Post: 04-09-2013, 02:05 PM
  5. Interval Reporting
    By mlopez in forum Queries
    Replies: 3
    Last Post: 07-16-2012, 05:48 PM

Tags for this Thread

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