Queries :: Dataset - Cumulative Sum For A Month
Nov 27, 2013
I 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.
View Replies
ADVERTISEMENT
Jan 28, 2013
I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
View 12 Replies
View Related
Aug 22, 2013
how 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
View 2 Replies
View Related
Jan 15, 2015
I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.
Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.
View 4 Replies
View Related
Aug 20, 2013
queries cumulative reduction, basically i have 2 tables, with the follow data
stock table
idProduct
QuantityStock
[Code]....
View 4 Replies
View Related
May 7, 2013
In Access 2010, I'm writing VBA code that assigns an ADO dataset as the recordsource for a form. The query itself varies and is constructed from a search term typed in a textbox by the user. The code below works perfectly until the dataset is empty, when no records satisfied the WHERE criteria. In that case, there is a very long pause (presumably a timeout?) and I eventually get the error message "ODBC call failed." I tried to get around this by testing the number of records in the dataset (see toward the end) before assigning it, but it makes no difference in the behavior, so I'm thinking the actual assignment isn't the issue ... something else is.
If it matters, the "FROM qry_beneficial_owners" in this case is predefined query local to my Access db. That query is based on a linked view from a SQL server.
Code:
Public Sub RunSearch(SearchTerm As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim cols As String
cols = "[acct], [acctname], [planid]"
[Code] ....
View 1 Replies
View Related
Jun 25, 2015
I have a simple form that has a subform with its datasource being a query that is a multi-table select (complex joins)... the subform is set to allow dataentry, edits, etc. I have a checkbox that displays a value from the query (0, -1). Is there a way I can enable this checkbox to write back to one of the tables? Temp table to hold the contents of the query?
View 8 Replies
View Related
Mar 28, 2014
I 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;
View 12 Replies
View Related
May 22, 2013
I 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?
View 4 Replies
View Related
Oct 7, 2013
I 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])
View 3 Replies
View Related
Mar 30, 2015
I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.
I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format
The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)
So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.
How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)
The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.
View 3 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 21, 2013
I have a table into which users insert records through a user form, I need to track the updates made in a record in a data set and i have a user log in function, it needs to tell me who updated last and what did they update.
View 1 Replies
View Related
Dec 12, 2011
I have data that I want to combine. However the separate data sets have some duplicates.
Let's say it is Customer Num field.
What I am saying is I am trying to combine the data sets to make a table with Customer Num and Customer Name.
I want to eliminate the duplicates. What is the best way to do this? Is there a feature in Access for this?
View 5 Replies
View Related
Apr 9, 2014
I have a table set up like:
CUSTOMER ID / CUSTOMER NAME / PAYMENT ID / PAYMENT DATE
Only want to show the last of each payment per month in form based on query.
So if they have a payment in April of $900 on 4/15 and then a payment in May 5/2 $500 and May 15 $1000
It will show two rows...one for April $900 payment and one for May $1000 payment.
I know the logic is something like
If more than one payment row for month, show last, otherwise only one payment for that month show the one payment.
I tired just setting hte "last" paramater but it only shows the very last payment, not taking month into account. So it only shows the $1000 may payment. thx
View 3 Replies
View Related
Jan 18, 2008
Using ODBC, is it possible to import only part of an Oracle dataset into Access 2007? If so, how would I do that? I have an Oracle table with 6 1/2 million records, but I onoly want to see the first 500,000 records.
If anyone can advise on how to do this, I would sure appreciate it.
Thank you!
View 3 Replies
View Related
Aug 4, 2013
I have an edit button in my form which activates VBA, collects all data from the fields and edit the dataset. Everything work fine except the case that next to the edit access adds a new record as well with exactly the same data.
This is my code :
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tbllocations where locationid = " & cdkey)
[code]...
I checked every parameter and it seems everything is OK which surely is the base for the correct edit of the dataset. locationid is the key of the table.
View 3 Replies
View Related
Jul 31, 2014
I have a dataset showing sales by salesperson for each product. I'd like to summarize this data in a table that looks like this:
Note that some salespeople don't sell certain products and that these records show up with "0". My problem is that, in my actual dataset, if a salesperson doesn't sell a product, that records doesn't exist at all. Is there any way to append empty "0" records to my dataset? See attached for an Access file.
DatasetProblem.accdb
View 5 Replies
View Related
May 23, 2005
How 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
View 8 Replies
View Related
Nov 9, 2005
I 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.
View 6 Replies
View Related
May 26, 2006
Hi,
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?!
View 1 Replies
View Related
Apr 5, 2006
I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:
Format([date],"mmm - yy")
And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.
How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???
:confused: :confused:
View 1 Replies
View Related
Sep 27, 2013
I am using the following code to get results from next month,
Code:
Month([ptdate])=Month(Now())+1
which works great to get next months results. I try to get what is left of this month by adding the code:
Code:
Between Now() And Month([ptdate])=Month(Now())+1
This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.
View 2 Replies
View Related
Jul 16, 2014
I've got a form with a combo box where I choose an employee and according to that employee's attributes are shown. Some of the attributes are stored in the same table with employee names, some of it is connected by foreign key to other tables. This form is not editable, information only. I've got a button "Edit employee" which opens another form intended to edit the information. I put text fields there by dragging the fields from tables. And so, for some reason, the only editable fields in that form are those connected by foreign key to the main table, but the main table fields are all not editable and error "not in dataset" comes up.
View 5 Replies
View Related
Jun 25, 2013
I pulled a report from this website my company uses and it has around 7,000 orders and a corresponding employee to each record. Since each employee has around 20-50 orders, I was wondering if there was a VBA code or different Access tool to randomly select only 2 orders from each employee, thus reducing the data set from 7,000 to a more manageable number?
View 5 Replies
View Related
May 24, 2012
I want to display the ID of a dataset in a form but I don't want the user to be able to edit it. Therefore I want to display the id in a label and not a textbox or combo etc.
I can't see a way of how to do this though. Is this possible, or am I going about it the wrong way?
View 3 Replies
View Related
