Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1152
  • Last Modified:

Access query - add row values to previous row value

Is this possible in Access (query)? I altimately need to create an "S" chart from the data - but what I need is to do is:
Add value in rows 1 and 2 to row 2, then add values in rows 2 and 3 to row 3, etc.

Example:
Row 1  10
Row 2    2     Would become 12
Row 3    4     Would become 16
Row 4  12     Would become  28
0
mlaurin
Asked:
mlaurin
1 Solution
 
klilleyCommented:
This depends upon how many rows you have and if you have an ID column so your table is

ID   number
1    10
2    2
3    4
4    12

if so your query can contain dsum("[number]","[id]>=" & [id])
0
 
Rey Obrero (Capricorn1)Commented:


SELECT A.*, (select sum([value]) from tablex where tablex.id<= A.id) AS Expr1
FROM TableX AS A;
0
 
mlaurinAuthor Commented:
Hi, thanks for your responses.
klilley -  I  put your formula into a column in the query and get an error.  I have a date field that I put in place of ID.

capricorn1 - sorry I do not know what you mean, have no idea what to do.  What does "A" respresent?  I assume tablex means the table name? I am using a query based on a query based on a query.

I have attached an excel spreadsheet showing the results of my query and what I NEED the results to be.  Hope that helps
Example.xls
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
mlaurinAuthor Commented:
Capricorn1 I tried the following in a guess with what you sent.  It DOES work - however is extremely slow and get not responding (even after it has run but I sort) but eventually does give me the correct numbers.  Any ideas on why it is hanging up?

Expr1: (select sum([Signoff]) from SChartEN8 where SChartEN8.MinDate<=A.MinDate)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How many records do you have:?

Do you have an Index on the date field?

In general, sub queries and domain functions (DSum, DLookup, etc) are going to be slow on large recordsets.

mx
0
 
mlaurinAuthor Commented:
DatabaseMX - the last query only has 128 records.  However it takes several queries to get to that point and the original table has about 20,000.  I do not have an index on the date field.  The initial table is linked to an outside system so I can not make changes to it.  Any helpful hints?
0
 
Rey Obrero (Capricorn1)Commented:
is SChartEN8 a query?

create a tempTable based on your query and use the tempTable to get the running sum.

might improve the speed.
0
 
mlaurinAuthor Commented:
capricorn1 - do you mean a sort of temporary table on the fly - how do I do that??
0
 
Rey Obrero (Capricorn1)Commented:
you said
<it takes several queries to get to that point and the original table >

so,  is SChartEN8 a query?
0
 
mlaurinAuthor Commented:
Yes - it is a query
0
 
mlaurinAuthor Commented:
I will check back in the am on this - thanks for your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now