SUm records

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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;
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top