Solved

Access 2000 - Stock decrease

Posted on 2011-03-09
5
373 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
  • 3
  • 2
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now