Query to subtract current stock from previous stock

[aerozeppel]

So I have this table to store quantities after each time a stock check is done and am trying to create a query that for each product would do the calculation (previous stock check quantity) - (latest stock check quantity) my tables are laid out as follows:

StockCheckID StockCheckDate ProductID Quantity
1----------------12/08/17---------1-----------3
2----------------12/08/17---------2-----------2
3----------------19/08/17---------1-----------3
4----------------19/08/17---------2-----------3
5----------------26/08/17---------1-----------2
6----------------26/08/17---------2-----------1

The query should produce the result:

ProductID UsedDate UsedAmount
1----------26/08/17------1-----------(=3-2)
2----------26/08/17------2-----------(=3-1)

Would be very grateful for any assistance.

[Uncle Gizmo]

You may find this useful :-

Allen Browne --- Inventory Control: Quantity on Hand

[MarkK]

Originally Posted by aerozeppel

create a query that for each product would do the calculation (previous stock check quantity) - (latest stock check quantity)

What's the use of that data? That's just the amount of the last change in quantity. How does it help to know that number, but not be certain WHEN?

So if you count 50 in 2015, ship 48 in 2016, and then count 2 in 2017, your query returns 50 - 2 = 48 without a date, and 48 is not the quantity on hand. How does that number help your business process or decision?

hth
Mark

[aerozeppel]

Thanks for your reply.

I won't have any invoice type table as there is no record of stock going out other than comparing the latest stock take with the previous stock take I can do the following calculation: stock used = previous stock - current stock + stock acquired. Which is why I asked if there was a way to calculate the first part of that: previous stock - current stock.

I want to calculate this so I can keep record of weekly stock used and then calculate an avg usage.

Apologies if i'm talking nonsense, I'm a total beginner with access.

[aerozeppel]

The query result should probably have a date field in it as well :/

ProductID UsedDate UsedAmount
1----------26/08/17------1-----------(=3-2)
2----------26/08/17------2-----------(=3-1)

[llkhoutx]

As I understand your question, what you want is the difference between the next to last quantity for each product and the last quantity for each product.

You example assumes that there's always at least 2 rows for each product. In reality, that may not be the case for inventory, that is, inventory for a product may not necessarily change. Numerous products are probably realistic

To answer you question as shown with queries requires multiple queries, i.e. determine the last and next to last row of each product, then query those queries joined for the required difference.

A much simpler method is to use DAO or ADO code to cycle through the stock status table, row by row, keeping a running change in quantity by product and date. Unless you have many hundreds of thousands of rows, this should run very quickly. Complexity of the code might be exacerbated by the actual number of products you have, not the number of rows. The order on the rows should be sequential, as you show.

By your additional posts, you issue is probably more complex than you state.

[MarkK]

Aerozep, I would count the stuff that you move in and out rather than counting the stuff you have left. It's the stuff that moves that you are concerned with, and it's the date on which it moves that matters. The problem you find yourself with now is to try to calculate what stuff moved and when, by doing math on the stuff you have left and when you counted it. That seems backwards.

In a typical inventory system you have Orders (stuff that leaves) and PurchaseOrders (stuff that arrives), and then, since those objects are dated, you can calculate your quantity on hand to any date, even into the future, and the matter of summing what moved and when is trivial.

hth
Mark

[aerozeppel]

Originally Posted by MarkK

Aerozep, I would count the stuff that you move in and out rather than counting the stuff you have left. It's the stuff that moves that you are concerned with, and it's the date on which it moves that matters. The problem you find yourself with now is to try to calculate what stuff moved and when, by doing math on the stuff you have left and when you counted it. That seems backwards.

In a typical inventory system you have Orders (stuff that leaves) and PurchaseOrders (stuff that arrives), and then, since those objects are dated, you can calculate your quantity on hand to any date, even into the future, and the matter of summing what moved and when is trivial.

hth
Mark

If only I could do it that way. I wanna create this database for a small pub that I work in, it has a pretty basic till, there's no way to log sales. I count the stock every week and currently use excel spreadsheets to log these stock checks, work out avg usage over a period and then recommend how much to reorder.

Image of the main worksheet if that helps at all:
ibb.co/fLwQg5

I was just going to use access to make the process more user friendly and in the process learn how to use access. Guess it's going to be more difficult than I thought.

[Uncle Gizmo]

I think my checklist example could be adapted to your needs. --- http://www.niftyaccess.com/add-a-che...cess-database/ --- Look at the first video on this webpage to get an idea of how it functions. You would need to change the checkbox to a text field to store the current stock level. The main form would store the date and possibly time when you did the stock take. Entries made in the subform would then represent the stock for each stock item at that particular moment in time. These entries are all stored in one table which means it is relatively easy to query the data.

[aerozeppel]

Thanks Uncle Gizmo, I use an android inventory app to record stock levels which I can then export to a spreadsheet and then into access. I may be able to automate that part at some other time but it's not important to me right now.
It's a way to do the calculation that I'm stumped with

[Uncle Gizmo]

I can't work out how the results you show relate to the data they are calculated from? It just doesn't seem to make sense! Either I'm misunderstanding your requirement, or there is some mistake.

[MarkK]

I just tried this on a TestData table I have, and it seems to work quite well...

SELECT t1.ID, Max(t2.ID) AS NextLeastID
FROM tTestData As t1 INNER JOIN tTestData As t2 
   ON t1.ID > t2.ID
GROUP BY t1.ID;

What happens in this query is the table is joined to itself on an inequality in the ID field (t1.ID > t2.ID), so for each row in the 'parent' table (t1), the child table shows all values that are less than the t1.ID, but if we GROUP BY the t1.ID and take the Max() of the t2.ID (previously constrained to be < t1.ID), then we get exactly, I think, what you are looking for. When I run that query I get...

ID	NextLeastID
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12

...which looks a lot like the pattern you are trying to generate. This idea should be usable for what you are trying to do, but you will also need to GROUP BY ProductID, your inequality will need to be on the Date (to find next least date, not ID, as I've done). In your case too, you might need to write an additional query to actually do the math. Note that my query here only returns the IDs. If there were values to do math on, as in your case, you might need to pull that data in after doing this step of just finding the previous row's ID.
hth
Mark
ps, If you can't get it, and you post a db with some sample data in a table, I'll see if I can make it work.