Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Access 2000 - Stock decrease

Posted on 2011-03-09
Medium Priority
416 Views
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
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
• 3
• 2

LVL 120

Expert Comment

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

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

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

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

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

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
###### Suggested Courses
Course of the Month6 days, 2 hours left to enroll