?
Solved

Access 2000 - Stock decrease

Posted on 2011-03-09
5
Medium Priority
?
424 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

612 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