Queries :: Cumulative Reduction Of Stock
Aug 20, 2013queries cumulative reduction, basically i have 2 tables, with the follow data
stock table
idProduct
QuantityStock
[Code]....
ADVERTISEMENT
Stock Reduction
Apr 30, 2007Hi
Can anyone help.
I have a table with units in stock eg. beans 27 tins. An order is placed for 6 tins of beans. How can I reduce the amount of stock in my stock table.
Hope someone can help.
Thanks:confused:
Automatic Stock Reduction
Nov 27, 2007Is there anyway to automatically reduce the quantity of stock. I have set up a table, with a field for the quantity of items in stock. I want to be able to automatically reduce this value at the click of a button, a possible macro???
Q. How is this done?
A. A sales person selects his name from a combo box, then he selects the customers name. Following this he then enters the book(s) ordered by this customer, then the quantity of each needed, and finally the discount. Ideally what I would like is for when the sales person enters the quantity of each book, for the amount in stock (Tbl_books) to be reduced by the amount entered in the qunatity field on the order form. If there are not enough copies of the book available for the order to be completed, then a small message box appears telling the sales person that their are not enough copies left in stock (a possible validation rule). Now this is where it gets tricky. If the sales person enters a incorect value for the quantity of books ordered, i.e. 2 instead of 1, then when he corrects the value, the number of books in stock (Tbl_Books) is to increase by 1.
I have absolutly no idea as to how to approach this, I have attached a printscreen from the relationships page, as well as a printscreen from the order form. Would you like a printscreen from any other table or form?
Relationships:
http://img28.picoodle.com/img/img28/5/11/27/f_Untitled2m_602830c.jpg
Stock Form:
http://img28.picoodle.com/img/img28/5/11/27/f_Untitled1m_0ecc314.jpg
Thanking you in advanced
CJM
Queries :: Sum Cumulative By Id?
Aug 22, 2013how can i do this in a query
Id---Q---Cu
1---10---10
1---10---20
1---10---30
2---10---10
2---10---20
2---10---30
Queries :: Dataset - Cumulative Sum For A Month
Nov 27, 2013I have a dataset like this
ABCDEFG
1.AccDateStartIntWLMV
2.SSAAB15028815/02/88Month1.25101000
3.SSAAB18028818/02/88Month020980
4.SSAAB25028825/02/88Month1.2510970
5.SSAAB29028829/02/88Month010960
6.SSXXB15028815/02/88Start 1.25101000
7.SSXXB18028818/02/88Month020980
8.SSXXB25028825/02/88Month2.2510970
9.SSXXB29028829/02/88Month010960
I used to to apply this logic in XL to do my job A-F = XL Columns & 1-9 XL Rows
ifA2=A1, E2+E1, E2
This is to have a cumulative figure for a month. Just bcoz the data is growing in large numbers, we were advised to use access for which I don't know how to perform this calculation.
Queries :: Cumulative Total On Multiple Records?
Mar 28, 2014I am looking for a way to get a progressive cumulative total from daily entries on Odometer records from multiple vehicles. (My current SQL query is not working) I will eventually run a report from these between two dates. I found the thread here, but am unsure of how to implement this - if this is indeed what I need in lieu of my current code..?
[URL]
My current problem is that, not all my previous entries are correct; Im not sure what is happening to the numbers about halfway through...
For clarification, here is my current code and an example of what is needed. The issue is highlighted in yellow. Excel shows the correct calculation
Code:
SELECT qry_ODO_Table.ID AS OdomAlias, qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, Nz(DFirst("Odometer","qry_ODO_Table"),0) AS StartOD, Nz(DLast("Odometer","qry_ODO_Table","[ID] < " & [OdomAlias]),0) AS Previous, qry_ODO_Table.Odometer, [Odometer]-[Previous] AS Difference
FROM qry_ODO_Table
ORDER BY qry_ODO_Table.ID;
Queries :: If Statement - Find Cumulative Forecast Up Until A Best Before Date
May 22, 2013I want my query to find the cumulative forecast up until a best before date.
This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.
I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.
How would I go about this?
Queries :: Calculate Cumulative Values Stored In Field
Oct 7, 2013I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:
[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25
I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:
CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])
I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:
CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])
Should Data Be Encoded For Db Size Reduction?
Dec 5, 2005I have been creating a personal database (access 2002) to keep help keep track of all the stupid things I own. I was wondering what people think about encoding data to help reduce database size. I know that for my small database it probably doesnt matter, but it it worth it to enocde colors into a couple of characters (2) instead of having a text entry that could fit a color spelled out (15). Ex.
Gray vs. G
Dark Gray vs. DG
Black vs. B
Blue vs. BL
etc.
I have come across a number of times when I can encode data and then write code for my forms to interperet that data back to plain english for me. Am I just wasting time if I do this? Or is there a point where this is recommended? Thanks for your advice.
Reduction Of A Database Size - Implications + Advice
Jul 14, 2006Hi there
I want to know if anyone has taken a large database (e.g 400,000 records) and had to take a subsection of this so to reduce size. (E.g you randomly would like to take 20,000 records). I need to reduce a database size for a test situation but would like to know what I should consider when reducing the size in this way.
Do I take the base database structure – table organisation with fields linkages etc and load data in?
I have some help in this task but would like to know of any advice that could be provided.
Many thanks
Queries :: Stock Database - Calculating Sum In Query?
Apr 17, 2015I have been working on a stock database for my company. It needs to hold stock data, Any adjustments in the storesperson may do (QtyIn or Out). And allow Customer PO's to be raised (just to show prices and quantites sold to customers)
I have 4 Tables:
tblStockList
PartNumber (Primary Key)
Description
Location
StockCheck
StockListUnitPrice
[code]....
I then want to create a query to work out the total stock.
At the moment i have two queries, one to Sum the adjustments and one to sum the PO's. (The stock is not removed from the Customer PO until invidual line is shipped)
I then need to combine both these queries to work out the total stock value.
There will always be a value in the Sum of Adjustments but not in the Customer PO. So when i run the query to =SumofAdjustments - SumofCustomerPO's, if there is no record in the SumOfCustomerPo's, no value will appear.
Queries :: Subtracting Stock Numbers With A Query
Jan 2, 2015I'm creating a sales system that allows customers to place orders using a customer account. when a customer completes an order the number items in the order needs to be subtracted from the stock numbers of each of those items stored in the system.
What I have so far is zipped and attached,
The way my system works should be easy enough to understand, there are four linked tables that store information on customers, products and orders. What I'm really struggling to understand is how to take the values in the records in the Invoice-Product table and take them away from the stock numbers for the products in the products table. But only new orders should take away from the stock number when a new order is placed.
If possible I also need to be able to add stock numbers for example if a delivery comes in, and if it's similar enough could that also be done here?
Queries :: Calculate Stock Quantity With Query
Jan 21, 2014I have a problem with calculating the total qty of two fields.
Code:
QtyMapics: Nz([MapicsQty];0)
QtySubComp: Nz([SubComponentQty];0)
TotalQty: [QtyMapics]+[QtySubComp]
When I calculate the values of the fields it pastes the values after each other.For example: if you have the values 1 and 2 (which should become 3) then the result is a value of 12.I've tried changing the function to SUM() but that didn't work.The reason I am using NZ() is due to the fact that some values have a NULL value in one or both of the fields and if NZ is not included it will not calculate anything.
Queries :: Update Query For Stock Levels
May 12, 2015I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....
I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.
Queries :: Check For Expiry Date And Stock Levels
Apr 23, 2013What I am trying to do is create a stock database and iv imported a blank version of the northwind template and filled it in with my own products which is fine up to a point.
What i also have though is i get weekly reports of the stock of each product with their batch numbers, expiry date and quantity remaining for that individual lot sent to me from another company. (So i might have several lines with the same batch number and expiry date but diffeent quantities) So what i want to ask is:
Is there any way for me to combine the data in the weekly reports with the stock level data in the northwind template?
What i want to be able to have is where somehow i can see the total quantity of eac product with the same batch number and expiry date and then it tells me when that batch has expired and it also automatically deducts the stock from my total stock when it gets to within e.g 9 months of the expiry date as we will not likely sell those.
I need to do this as at the moment all i can get from my database is how much stock has been ordered, sold and what we have in total on hand but if the stock is close to expiring then i wouldnt know and i need to be able to see when to order stock in advance as i want a minimum of 6 months cover and ideally 9 months cover as orders for stock dont come in straight away.
Also, how i can import the data from the weekly reports into excel/access easily as they come as word files presented as a table but the data is put in frames i think and iv tried removing them but then it all looks messed up.
Queries :: How To Calculate Current Stock (Actual Quantity)
Oct 18, 2014I have a query (Inventory Transactions Extended) in which i am trying to caculate current stock.
I have a form (Inventory Transactions Form) where i either add or remove Inventory Items.
I have used this statement to create a new field (Actual Quantity) to calculate current stock based on stock been added and removed:
Code:
Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition";[Inventory Transactions]![Quantity];-([Inventory Transactions]![Quantity]))
for some reason it does not work. It is not calculating current stock. If i remove stock it shows a negative amount based on the amount i removed, and when i add stock it shows a positive amount based on what i added
I have attached a sample.
[ATTACH][/ATTACH]
Sample.accdb
Queries :: Create Stock Query In Access 2013
Nov 3, 2013I have problem to create Stock query in Access 2013..I want to sum Purchasing numbers base ItemName in Purchasing table by query .. but i found error in summing?
View 4 Replies View RelatedCumulative Row Function?
May 23, 2005How to make calculated field that represents cumulative row?
For example – table has fields "transaction_number", "trans_date" and "trans_amount".
In this table I register money transactions on my bank-account. I need calculated field that is going to show how much money is on my account after every transaction.
Thanks
Cumulative Totals
Nov 9, 2005I have two tables, one for budget and the other for actual expenditure. I need a query or queries that will give me a cumulative figure on 3 groups based on the expenditure table. In my expenditure table I have the following fields.
ExpenditureID Autonumber
Period Text
CostCentre Text
CostCode Text
Value Currency
fldDate Short Date
I have managed to fnd a function that will give me a running sum for each individual group, but cannot seem to get it to work for more than 1 group. The function is this.
Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long
If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If
'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function
Is there any way I can see the results in one query or table? Any help appreciated.
Cumulative Totals - Please Help
May 26, 2006Hi,
I'm having trouble trying to create an expression for generating cumulative totals by Month.
I believe this is the function I need to use -
DSum (expression, domain, [criteria] )
I need the layout to be as follows -
Months - Rows
Cumulative Monthly Despatches - Column
I have a field list with
Months
Despatches
Models
All of these are from another Query and 'Despatches' is an expression generated in the other query
Can anyone please Help?!
Cumulative Monthly Totals?
May 31, 2006Hi,
I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867
The expression I’m using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)
The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867
I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response
Selective Cumulative Sums
Sep 24, 2007Good afternoon,
I am trying to figure out how to create a cumulative sum field for a test report I am working on. On a very simple level, I can run queries to get my data into the following format:
Test # | Article # | Test Time | Test Parameters
1 | 1 | 8 | A,B,C
2 | 2 | 5 | A,B,C
3 | 2 | 7 | A,B,C
4 | 2 | 9 | A,B,C
For each test number, I want a separate report page showing the article number, test time, and test parameters. Also on each page, I want to display the cumulative test time for all test numbers up to the displayed test number, but only for the article used in that test.
For this example, page three would have test number 3, article number 2, test time 7, test parameters A,B,C, and cumulative test time on article 2 of 12 (5+7), while page four would have a cumulative test time on article 2 of 21 (5+7+9).
How can I convince Access to do this for me? So far I've only been able to have a cumulative test time for all test articles, not just the test article related to the test number. To make this more complicated, Access needs to be able to have another article added at any time without having to rework the code to create the test reports.
I appreciate any help you can give me that will get me on my way.
Thanks!
Erik
Cumulative Update Field In Table
Aug 4, 2005i am trying to get a table to automaticaly calculate numbers entered into a field, for example field 3 allready has the number 10 inside it, if i then enter the number 15 i want the field to calculate the existing number with the new number and display 25?
currently when i update the field then i will see 15, so i need to tell it to do something else and that is where i am stuck!
please help.
Bev :confused:
Help Needed With Cumulative Totals And Graphs
Jul 31, 2006Hi all,
New user here so apologies if I post in the wrong place.
My colleague and I are trying to put together a database which automates a very time consuming process which is currently being done in Excel.
The purpose of the database is to pull together a load of actuals from SAP and then the forecasts we have put together and then chuck out a whole load of graphs and a summary spreadsheet/ report for the upper echelons;ons of management.
The data is in the database and so far it is all going well - but we have hit a stumbling block. We need Access to calculate some cumulative totals so that we can throw the whole lot into our excel graphs but we can't figure out how to do it.
We are using ye olde Access 97 so our options are a little limited.
Can anyone give us a starter for 10? If you need anything more technical, I can post whatever information you need.
Thanks
Tasha
Generating Movement From Cumulative Values
Jan 9, 2007Hi,
I have a query that brings back data as follows (sample):
1 30/5/2006 £100,000
2 30/6/2006 £150,000
3 30/7/2006 £250,000
The currency values are all cumulative - is there a way to add a calculated column to calculate the movement using the previous record using SQL? i.e.
Item Date cumValue Movement
1 30/5/2006 £100,000 £100,000
2 30/6/2006 £150,000 £50,000
3 30/7/2006 £250,000 £100,000
Any help greatly appreciated.
Regards,
Simon
Keeping Cumulative Values In A Table
Apr 30, 2008Hi I have a table that looks like this
ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------0
itemCode2-------------------0----------------2--------------1
itemCode3-------------------0----------------2--------------1
As you can see in the year 2010 items 2 and 3 go down from qty 2 to 1. What I am trying to do is to keep track of everything that was ever shipped to the customer. So with that in mind the above table is showing that Qty-2 was ordered in 2009 and Qty-1 was ordered in 2010. I want to add these as I go along. So my desired table would look like the following
ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------1
itemCode2-------------------0----------------2--------------3
itemCode3-------------------0----------------2--------------3
in this table 2010 shows Qty-3 which means 2 was present on site in year 2009 and 1 more was added in 2010 to make the qty 3. I want to write a storedProcedure or something similar to convert the first table into the second table. I said storedProcedure because I am used to doing this in SQL Server.