SUm records

G

Guest

I want to sum records from a table. the second row is the sum of the first
and second row. The thirth row is the sum of the first, second and thirth row
etc. Is someone who can help me?

Best regards
Rogier
 
K

KARL DEWEY

Use these two queries for running sum. You could change the criteria so that
it gives a running sum of the last 5 entries like this in second query --
WHERE (((Running_Sum_1_1.Rank) Between [Running_Sum_1].[Rank] And
[Running_Sum_1].[Rank]-4))

Running_Sum_1 ---
SELECT Q.[Date close], Sum(Q.x) AS SumOfx, (SELECT COUNT(*) FROM [Change
Requests] Q1
WHERE Q1.[Date close] < Q.[Date close] )+1 AS Rank
FROM [Change Requests] AS Q
GROUP BY Q.[Date close]
ORDER BY Q.[Date close];

SELECT Running_Sum_1.[Date close], Running_Sum_1.Rank,
Sum(Running_Sum_1_1.SumOfx) AS [Running Sum]
FROM Running_Sum_1, Running_Sum_1 AS Running_Sum_1_1
WHERE (((Running_Sum_1_1.Rank)<=[Running_Sum_1].[Rank]))
GROUP BY Running_Sum_1.[Date close], Running_Sum_1.Rank;
 
K

KARL DEWEY

I looked up your old post and came up with this as you solution.

SELECT Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month],
Sum(Rogier_1.Quantity) AS SumOfQuantity
FROM Rogier, Rogier AS Rogier_1
WHERE (((Rogier_1.Runtime)<=[Rogier].[Runtime]))
GROUP BY Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month];

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Use these two queries for running sum. You could change the criteria so that
it gives a running sum of the last 5 entries like this in second query --
WHERE (((Running_Sum_1_1.Rank) Between [Running_Sum_1].[Rank] And
[Running_Sum_1].[Rank]-4))

Running_Sum_1 ---
SELECT Q.[Date close], Sum(Q.x) AS SumOfx, (SELECT COUNT(*) FROM [Change
Requests] Q1
WHERE Q1.[Date close] < Q.[Date close] )+1 AS Rank
FROM [Change Requests] AS Q
GROUP BY Q.[Date close]
ORDER BY Q.[Date close];

SELECT Running_Sum_1.[Date close], Running_Sum_1.Rank,
Sum(Running_Sum_1_1.SumOfx) AS [Running Sum]
FROM Running_Sum_1, Running_Sum_1 AS Running_Sum_1_1
WHERE (((Running_Sum_1_1.Rank)<=[Running_Sum_1].[Rank]))
GROUP BY Running_Sum_1.[Date close], Running_Sum_1.Rank;

--
KARL DEWEY
Build a little - Test a little


Rogier said:
I want to sum records from a table. the second row is the sum of the first
and second row. The thirth row is the sum of the first, second and thirth row
etc. Is someone who can help me?

Best regards
Rogier
Click to expand...
Click to expand...
 
R

Rogier

Karl,

thank you for your response, but can you write it more understanding for me?
I don't now much about Access so please help me more.

Thanks,
Rogier

KARL DEWEY said:
I looked up your old post and came up with this as you solution.

SELECT Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month],
Sum(Rogier_1.Quantity) AS SumOfQuantity
FROM Rogier, Rogier AS Rogier_1
WHERE (((Rogier_1.Runtime)<=[Rogier].[Runtime]))
GROUP BY Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month];

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Use these two queries for running sum. You could change the criteria so that
it gives a running sum of the last 5 entries like this in second query --
WHERE (((Running_Sum_1_1.Rank) Between [Running_Sum_1].[Rank] And
[Running_Sum_1].[Rank]-4))

Running_Sum_1 ---
SELECT Q.[Date close], Sum(Q.x) AS SumOfx, (SELECT COUNT(*) FROM [Change
Requests] Q1
WHERE Q1.[Date close] < Q.[Date close] )+1 AS Rank
FROM [Change Requests] AS Q
GROUP BY Q.[Date close]
ORDER BY Q.[Date close];

SELECT Running_Sum_1.[Date close], Running_Sum_1.Rank,
Sum(Running_Sum_1_1.SumOfx) AS [Running Sum]
FROM Running_Sum_1, Running_Sum_1 AS Running_Sum_1_1
WHERE (((Running_Sum_1_1.Rank)<=[Running_Sum_1].[Rank]))
GROUP BY Running_Sum_1.[Date close], Running_Sum_1.Rank;

--
KARL DEWEY
Build a little - Test a little


Rogier said:
I want to sum records from a table. the second row is the sum of the first
and second row. The thirth row is the sum of the first, second and thirth row
etc. Is someone who can help me?

Best regards
Rogier
Click to expand...
Click to expand...
Click to expand...
 
R

Rogier

Karl,

thank you for your response, but can you write it more understanding for me?
I don't now much about Access so please help me more.

Thanks,
Rogier

KARL DEWEY said:
I looked up your old post and came up with this as you solution.

SELECT Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month],
Sum(Rogier_1.Quantity) AS SumOfQuantity
FROM Rogier, Rogier AS Rogier_1
WHERE (((Rogier_1.Runtime)<=[Rogier].[Runtime]))
GROUP BY Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month];

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Use these two queries for running sum. You could change the criteria so that
it gives a running sum of the last 5 entries like this in second query --
WHERE (((Running_Sum_1_1.Rank) Between [Running_Sum_1].[Rank] And
[Running_Sum_1].[Rank]-4))

Running_Sum_1 ---
SELECT Q.[Date close], Sum(Q.x) AS SumOfx, (SELECT COUNT(*) FROM [Change
Requests] Q1
WHERE Q1.[Date close] < Q.[Date close] )+1 AS Rank
FROM [Change Requests] AS Q
GROUP BY Q.[Date close]
ORDER BY Q.[Date close];

SELECT Running_Sum_1.[Date close], Running_Sum_1.Rank,
Sum(Running_Sum_1_1.SumOfx) AS [Running Sum]
FROM Running_Sum_1, Running_Sum_1 AS Running_Sum_1_1
WHERE (((Running_Sum_1_1.Rank)<=[Running_Sum_1].[Rank]))
GROUP BY Running_Sum_1.[Date close], Running_Sum_1.Rank;

--
KARL DEWEY
Build a little - Test a little


Rogier said:
I want to sum records from a table. the second row is the sum of the first
and second row. The thirth row is the sum of the first, second and thirth row
etc. Is someone who can help me?

Best regards
Rogier
Click to expand...
Click to expand...
Click to expand...
 
R

Rogier

Karl,

thank you for your response, but can you write it more understanding for me?
I don't now much about Access so please help me more.

Thanks,
Rogier

KARL DEWEY said:
I looked up your old post and came up with this as you solution.

SELECT Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month],
Sum(Rogier_1.Quantity) AS SumOfQuantity
FROM Rogier, Rogier AS Rogier_1
WHERE (((Rogier_1.Runtime)<=[Rogier].[Runtime]))
GROUP BY Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month];

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Use these two queries for running sum. You could change the criteria so that
it gives a running sum of the last 5 entries like this in second query --
WHERE (((Running_Sum_1_1.Rank) Between [Running_Sum_1].[Rank] And
[Running_Sum_1].[Rank]-4))

Running_Sum_1 ---
SELECT Q.[Date close], Sum(Q.x) AS SumOfx, (SELECT COUNT(*) FROM [Change
Requests] Q1
WHERE Q1.[Date close] < Q.[Date close] )+1 AS Rank
FROM [Change Requests] AS Q
GROUP BY Q.[Date close]
ORDER BY Q.[Date close];

SELECT Running_Sum_1.[Date close], Running_Sum_1.Rank,
Sum(Running_Sum_1_1.SumOfx) AS [Running Sum]
FROM Running_Sum_1, Running_Sum_1 AS Running_Sum_1_1
WHERE (((Running_Sum_1_1.Rank)<=[Running_Sum_1].[Rank]))
GROUP BY Running_Sum_1.[Date close], Running_Sum_1.Rank;

--
KARL DEWEY
Build a little - Test a little


Rogier said:
I want to sum records from a table. the second row is the sum of the first
and second row. The thirth row is the sum of the first, second and thirth row
etc. Is someone who can help me?

Best regards
Rogier
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Create a new query but in design view select no tables or queries.
Click on menu VIEW - SQL View. Copy my post into the SQL window. Edit it
to change the table name from Rogier to your table name. Rogier AS Rogier_1
is the second instance of the same table.
Watch for wraps the posting/pasting may add. The only hard returns are
those that preceed FROM, WHERE, & GROUP BY.

--
KARL DEWEY
Build a little - Test a little


Rogier said:
Karl,

thank you for your response, but can you write it more understanding for me?
I don't now much about Access so please help me more.

Thanks,
Rogier

KARL DEWEY said:
I looked up your old post and came up with this as you solution.

SELECT Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month],
Sum(Rogier_1.Quantity) AS SumOfQuantity
FROM Rogier, Rogier AS Rogier_1
WHERE (((Rogier_1.Runtime)<=[Rogier].[Runtime]))
GROUP BY Rogier.Month, Rogier.Runtime, Rogier.Quantity, Rogier.[% month];

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Use these two queries for running sum. You could change the criteria so that
it gives a running sum of the last 5 entries like this in second query --
WHERE (((Running_Sum_1_1.Rank) Between [Running_Sum_1].[Rank] And
[Running_Sum_1].[Rank]-4))

Running_Sum_1 ---
SELECT Q.[Date close], Sum(Q.x) AS SumOfx, (SELECT COUNT(*) FROM [Change
Requests] Q1
WHERE Q1.[Date close] < Q.[Date close] )+1 AS Rank
FROM [Change Requests] AS Q
GROUP BY Q.[Date close]
ORDER BY Q.[Date close];

SELECT Running_Sum_1.[Date close], Running_Sum_1.Rank,
Sum(Running_Sum_1_1.SumOfx) AS [Running Sum]
FROM Running_Sum_1, Running_Sum_1 AS Running_Sum_1_1
WHERE (((Running_Sum_1_1.Rank)<=[Running_Sum_1].[Rank]))
GROUP BY Running_Sum_1.[Date close], Running_Sum_1.Rank;

--
KARL DEWEY
Build a little - Test a little


:

I want to sum records from a table. the second row is the sum of the first
and second row. The thirth row is the sum of the first, second and thirth row
etc. Is someone who can help me?

Best regards
Rogier
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query count previous record 2
Multiple SUM's in a column 15
dependent lists and data presentation 2
Pivot table calculated field Count(field) / sum(field) 0
Re : Excel Data>Sort... Tracking of Cell Addresses Used in Formulae 1
Excel formula, sum the value cell and if empty cell left blank 0
sum 1
Excel Excel 2007, Sum with criteria 1

Top