Access query question (1 Viewer)

[JefJFitz]

I have an Access table that shows inventory for a set of storage tanks by day. Each tank has it's own record for every day. What I would like to do is write a query (that will feed into a report) that will calculate the daily inventory delta by tank. I have no clue how to do that. Can anyone help out? Thanks!

 

[June7]

Possibly use RunningSum property of textbox on report as well as report Sorting & Grouping. Could you provide db for analysis? Follow instructions at bottom of my post.

 

[JefJFitz]

I have attached a simplified version of the database that I am working with no proprietary information. It should give you a basic understanding of what I am attempting to do. Table INVENTORY DATA contains the daily inventory information for a set of five different storage tanks from the period of April 1st until today. Queries CURRENT INVENTORY

 

[JefJFitz]

I have attached a simplified version of the database named QueryTesting that I am working with no proprietary information. It should give you a basic understanding of what I am attempting to do. Table INVENTORY DATA contains the daily inventory information for a set of five different storage tanks from the period of April 1st until today. Queries CURRENT INVENTORY and PREVIOUS INVENTORY show the daily inventory for the five tanks based upon the parameter that is entered. What I am attempting to do is design a report that will show the inventory for the five tanks as separate line items and also show the inventory delta from the prior day (based on what the user enters). Thank you so much for your help!

 

[June7]

You are not recording transactions documenting in and out movement of inventory. You are recording a daily balance. Calculating the difference between sequential records is not simple in Access. Review this tutorial http://allenbrowne.com/subquery-01.html#AnotherRecord.

In your case, query would be like:

SELECT [INVENTORY DATA].[Inventory Date], [INVENTORY DATA].[Tank Number], [INVENTORY DATA].Inventory,
       (SELECT TOP 1 [INVENTORY] FROM [INVENTORY DATA] AS Dupe
       WHERE Dupe.[Tank Number] = [INVENTORY DATA].[Tank Number] AND Dupe.[Inventory Date]<[INVENTORY DATA].[Inventory Date]
       ORDER BY Dupe.[Inventory Date] DESC) AS PrevInv
FROM [INVENTORY DATA]
ORDER BY [Tank Number], [Inventory Date];

Two things could improve db design: 1) don't use spaces in naming convention, nor punctuation/special characters; 2) don't use all UPPER CASE, this is harder to read.

 

[JefJFitz]

I will try that. Thank you so much!

 

[June7]

An alternative query would require there be a record every day for every tank, no gaps. Can you rely on that?

 

[JefJFitz]

That is the norm. All inventory tanks are measured every day and a record exists for each tank. I used the formula that you gave me and it worked to perfection. Gave me exactly what I was trying to accomplish. Also took your advice on naming convention and redid the entire db. Thank you for your help...really appreciated!

 

[June7]

Correlated subqueries can perform slowly with large dataset.

Alternative that might be faster would be to build a query that calculates a date field that subtracts 1 from InventoryDate. Then join that query to table with compound linking on date and tank number.

Otherwise, might have to involve VBA function.

 

[CJ_London]

Alternative that might be faster would be to build a query that calculates a date field that subtracts 1 from InventoryDate.

You can use a non standard join

From tbl1 A inner join tbl1 B on A.idate=B.idate+1

 

[June7]

Yes, I always forget that one. And still need link on tank number as well.
However, INNER JOIN will result in dropping last record. I tested LEFT and RIGHT joins with non-standard join and nested subquery - all fail. So if don't care about last record, either is option.
Caution: don't try to open non-standard join in query Design view, only SQL view.

 

[CJ_London]

Well first record wont have one before and the last one after so nothing to compare to anyway