Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-18-2017, 12:55 PM   #1
aerozeppel
Newly Registered User
 
Join Date: Sep 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
aerozeppel is on a distinguished road
Query to subtract current stock from previous stock

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.


Last edited by aerozeppel; 09-18-2017 at 02:11 PM. Reason: Correct Query Result
aerozeppel is offline   Reply With Quote
Old 09-18-2017, 01:11 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,365
Thanks: 555
Thanked 948 Times in 897 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Query to subtract current stock from previous stock

You may find this useful :-

Allen Browne --- Inventory Control: Quantity on Hand
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 09-18-2017, 01:52 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by aerozeppel View Post
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

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 09-18-2017, 01:55 PM   #4
aerozeppel
Newly Registered User
 
Join Date: Sep 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
aerozeppel is on a distinguished road
Re: Query to subtract current stock from previous stock

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.

Last edited by aerozeppel; 09-18-2017 at 02:01 PM.
aerozeppel is offline   Reply With Quote
Old 09-18-2017, 02:08 PM   #5
aerozeppel
Newly Registered User
 
Join Date: Sep 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
aerozeppel is on a distinguished road
Re: Query to subtract current stock from previous stock

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)
aerozeppel is offline   Reply With Quote
Old 09-18-2017, 06:47 PM   #6
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: Query to subtract current stock from previous stock

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.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 09-18-2017, 07:52 PM   #7
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 09-18-2017, 10:35 PM   #8
aerozeppel
Newly Registered User
 
Join Date: Sep 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
aerozeppel is on a distinguished road
Re: Query to subtract current stock from previous stock

Quote:
Originally Posted by MarkK View Post
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.
aerozeppel is offline   Reply With Quote
Old 09-19-2017, 12:04 AM   #9
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,365
Thanks: 555
Thanked 948 Times in 897 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Query to subtract current stock from previous stock

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.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 09-19-2017, 05:48 AM   #10
aerozeppel
Newly Registered User
 
Join Date: Sep 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
aerozeppel is on a distinguished road
Re: Query to subtract current stock from previous stock

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
aerozeppel is offline   Reply With Quote
Old 09-19-2017, 10:14 AM   #11
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,365
Thanks: 555
Thanked 948 Times in 897 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Query to subtract current stock from previous stock

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.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 09-19-2017 at 10:25 AM. Reason: typo
Uncle Gizmo is offline   Reply With Quote
Old 09-19-2017, 11:44 AM   #12
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
I just tried this on a TestData table I have, and it seems to work quite well...
Code:
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...
Code:
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.

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to get previous days closing stock saudkazia Queries 2 03-17-2017 01:44 AM
Question Stock Balances - Stock Checking ? Minty General 4 12-01-2016 04:52 AM
how to calculate current stock james7705 Queries 2 10-18-2014 05:20 AM
Current stock and Calculating cost queries nicole.skeeters Queries 32 09-06-2013 10:16 AM
Stock management - calculating available stock items garywood84 Queries 5 09-21-2009 12:14 AM




All times are GMT -8. The time now is 12:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World