Solved

Access 2000 - Stock decrease

Posted on 2011-03-09
5
391 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 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