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!