Solved

Access 2000 - Stock decrease

Posted on 2011-03-09
5
407 Views
Last Modified: 2012-05-11
Dear Experts,

Could you please advise in the attached database with an example which sounds easy but I was not able to solve it in Access.

Briefly there are two tables, one contains orders, one item 10102 is twice so have two orders

Product      Order Date      Order qty
10101      2010-03-07      10
10102      2010-03-09      10
10103      2010-03-10      10
10102      2010-03-07      10

The second table has current stocks, the 10102 is once

Product      Stock qty
10101      100
10102      100
10103      100

How could be done in Query1 a calculation in column "Remaining stock", which would show like below that the first order 10pcs is assigned to date 2010-03-07 so remaining is 90pcs, after comes the second order with later date 2010-03-09 so the remaining is 80pcs

Product      Stock qty      Order qty      Order Date      Remaining stock
10102      100      10      2010-03-09                  80
10102      100      10      2010-03-07                  90

Currently the Query1 calculates 90pcs for both line, but would like to have 80pcs at line with 2010-03-09

thanks,
Decrease-stock.zip
0
Comment
Question by:csehz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35082601
see the function from this link

How to Create a Grouped Running Sum in a Query
http://support.microsoft.com/?kbid=205183
0
 
LVL 1

Author Comment

by:csehz
ID: 35082847
Capricorn thanks very much, in the attached database applied the logic and the query RunSum calcualtes well, but somehow strangly if I apply that field in another query so in Remaining_qty, the values are different.

Do you have idea maybe why?

It is also interesting that if the RunSum query is run to the screen (first attached picture), there the calculated values are different than the exported to excel version (second attached picture)





Decrease-stock-RunSum.zip
AfterQueryRun.jpg
AfterExcelExport.jpg
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35083076
that is weird.. anyway
*do a compact and repair
* select query Runsum from the database window (do not open)
* File > export > select excel 97-2003

* see the resulting Excel file
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35083101
a better way is to convert your query to a Make table query and export the created table.
0
 
LVL 1

Author Comment

by:csehz
ID: 35093283
Capricorn sorry only today had the chance to check, with the make table query it works
Decrease-stock-RunSum.zip
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question