Calculation

  • Thread starter Thread starter MB via AccessMonster.com
  • Start date Start date
M

MB via AccessMonster.com

How do I create a query that will:

Add a previous day gas total with todays totals. I need to start with the
total gas value on 10/31/2000, and have a running total. I need to base the
running totals from each date.

Example:

Yesterdays gas volume + todays injected gas volume - todays withdrawl volume.


Thanking in advance!

MB
 
Thank you so much Chris...you all are awesome!

MB
How do I create a query that will:
Click to expand...
[quoted text clipped - 9 lines]
MB
Click to expand...

MB via AccessMonster.com,

This is a basic example of accumulating a running total.

You will need to adapt it to your table and column names (whatever
they are). You will also need to fit your mathematical equation
into, as well.

Tables:

Note: The Orders and Items tables that are implied to exist by some
of the column names have been omitted for brevity (along with their
foreign key references).

CREATE TABLE OrderDetails
(DetailID AUTOINCREMENT
,OrderID INTEGER
,ItemID INTEGER
,Qty INTEGER
,CONSTAINT pk_OrderDetails
PRIMARY KEY (DetailID)
)

Sample Data:

DetailID, OrderID, ItemId, QTY
1, 1, 1, 5
2, 1, 2, 5
3, 1, 3, 1
4, 1, 4, 1
5, 2, 2, 20
6, 2, 3, 10

Query (Find_OrderDetails_RunSum_Qty):

SELECT (SELECT SUM(OD01.Qty) AS Qty
FROM OrderDetails AS OD01
WHERE OD01.DetailID <= OD1.DetailID)
FROM OrderDetails AS OD1

Results:

Qty
5
10
11
12
32
42

A review of the sample data will show that this is correct.

Sincerely,

Chris O.
Click to expand...
 
MB via AccessMonster.com said:
How do I create a query that will:

Add a previous day gas total with todays totals. I need to start with the
total gas value on 10/31/2000, and have a running total. I need to base the
running totals from each date.

Example:

Yesterdays gas volume + todays injected gas volume - todays withdrawl volume.


Thanking in advance!

MB
Click to expand...

MB via AccessMonster.com,

This is a basic example of accumulating a running total.

You will need to adapt it to your table and column names (whatever
they are). You will also need to fit your mathematical equation
into, as well.

Tables:

Note: The Orders and Items tables that are implied to exist by some
of the column names have been omitted for brevity (along with their
foreign key references).

CREATE TABLE OrderDetails
(DetailID AUTOINCREMENT
,OrderID INTEGER
,ItemID INTEGER
,Qty INTEGER
,CONSTAINT pk_OrderDetails
PRIMARY KEY (DetailID)
)


Sample Data:

DetailID, OrderID, ItemId, QTY
1, 1, 1, 5
2, 1, 2, 5
3, 1, 3, 1
4, 1, 4, 1
5, 2, 2, 20
6, 2, 3, 10


Query (Find_OrderDetails_RunSum_Qty):


SELECT (SELECT SUM(OD01.Qty) AS Qty
FROM OrderDetails AS OD01
WHERE OD01.DetailID <= OD1.DetailID)
FROM OrderDetails AS OD1

Results:

Qty
5
10
11
12
32
42

A review of the sample data will show that this is correct.


Sincerely,

Chris O.
 
Back
Top