Microsoft Access -- Updating a Field with "Cumulative" Data 1

[BizDev]

Hey everybody, first time poster here.

Here's my situation. I have a table that basically looks like this...

Field Names:

Item
Date
Qty1
Qty2
Net
Cumulative

There is accurate data in all of the fields, except for cumulative. I have the data sorted ascending by Item, then ascending by date.

What I am trying to do is basically this:

If [Item] != (prior record)[Item], [Net], [Net]+(prior record)[Net]

Does that make sense? Basically, I want to plot out what the net qtys will be, over a course of time. Should I use visual basic for this? Write a query? I'm by no means a programmer and was wondering if anyone could help. I would greatly appreciate it.

 

[dhookom]

Welcome to Tek-Tips. It would really help if you typed in about 5-10 records with the expected calculated value.

Generally you should not be storing a calculated value. Also Qty1 and Qty2 suggest un-normalized tables.

You can use Running Sum in a report to display cumulated totals.

Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

Here's an example...

http://spreadsheets.google.com/ccc?key=0AoPAogi68vEUdGpmcmdrdk9Nb2dDaUozek9ScnpxY1E&hl=en

By item, I want the cumulative to represent, by date, the running sum.

 

[dhookom]

I doubt anyone has permission to view your google doc. Let's try again

It would really help if you typed in about 5-10 records with the expected calculated value.

Generally you should not be storing a calculated value. Also Qty1 and Qty2 suggest un-normalized tables.

You can use Running Sum in a report to display cumulated totals.

Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

Appreciate you taking the time to look at this.
The formatting below isn't great, but hopefully not too difficult to extrapolate out the logic of what I'm trying to do.



Item Date Qty1 Qty2 Net Cume
Shoe 1/1/2009 5 2 7 7
Shoe 1/2/2009 -3 2 -1 6
Shoe 1/3/2009 0 0 0 6
Shoe 1/4/2009 2 2 4 10
Hat 1/1/2009 -55 35 -20 -20
Hat 1/2/2009 -10 35 25 5
Hat 1/3/2009 45 -20 25 30
Hat 1/4/2009 -20 -20 -40 -10

 

[SkipVought]

What is the definition of Qty1 & Qty2, as to why they are stored as two separate columns?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BizDev]

Definition really isn't important. You could call it supply and demand, or debit and credit. What's important is the two values are netted, and a running sum is added by date, for each unique item.

 

[SkipVought]

Great. Then Duane already gave you the answer for best and accepted practices, when he stated, "You can use Running Sum in a report to display cumulated totals."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

BizDev
Any reply to

Generally you should not be storing a calculated value.
You can use Running Sum in a report to display cumulated totals.

or am I wasting my time attempting to provide some help?


Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

Is there a way to do a running sum as a calculated field? Forgive me if I sounded terse in my reply. There's more I want to do with this table (adding more fields, contingent on the cumulative value), and a report isn't what I'm after at this poin in time.

 

[dhookom]

Try something like:

SELECT Item, DateField, Qty1, Qty2, Qty1+Qty2 As Net, 
(SELECT Sum(Qty1+Qty2) FROM tblNoNameGiven NNG WHERE NNG.Item = tblNoNameGive.Item AND NNG.DateField <=tblNoNameGiven.DateField) as Cume
FROM tblNoNameGiven;

Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

Awesome, I'll give it a try once the dbase finishes updating some tables. Really appreciate it!

 

[BizDev]

Strange. I only get this value when running that: 324357.154000002 for the cume column.

 

[dhookom]

BizDev,
You have to work with us. We can't see your computer screen. If you want help, you must post the SQL view of your query.

Duane
Hook'D on Access
MS Access MVP

 

[dallen43]

Maybe I'm missing something, but why can't you just use the formula in F2 and copied down the column?

=IF($A2=$A1,$F1+$E2,$E2)

 

[dhookom]

dallen43,
I believe this is Access, not Excel. However, I could be wrong since neither the word Access or Excel has been used to this point. There have been words like Field, Query, dbase, Tables, etc that seem to point to Access.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Just noticed the subject line after I posted.

Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

This is in Access.
The point is that this is a very easy exercise in Excel, as Dallen pointed out. However, there is not, to my knowledge, an easy way to functionally or logically have Access check the prior row, on a unique "key"-- in this case, item number, or in Excel column A, and perform an if/then output.

I could probably do this in visual basic, but thought there might be an elegant way to do it if there was, in SQL or in Access, a simple function that points to "prior row".

 

[BizDev]

Duane-- here is the SQL:

SELECT [tblAYB].ITEM_NUMBER, [tblAYB].FISCAL_YEAR, [tblAYB].FISCAL_WEEK_NUMBER, (SELECT Sum([GGG_DEM]+[DO_DEM]) FROM [tblAYB] WHERE [tblAYB].ITEM_NUMBER = [tblAYB].[ITEM_NUMBER] AND [tblAYB].PERIOD <=[tblAYB].PERIOD) AS Cume, [tblAYB].FISCAL_YEAR, [tblAYB].FISCAL_WEEK_NUMBER, [tblAYB].PERIOD AS Expr1, [tblAYB].DO_OH, [tblAYB].GGG_OH, [tblAYB].DO_OO, [tblAYB].GGG_OO, [tblAYB].DO_DEM, [tblAYB].GGG_DEM, [tblAYB].GGG_NET, [tblAYB].DO_NET, [tblAYB].CORP_NET, [tblAYB].GGG_CUME_NET, [tblAYB].DO_CUME_NET, [tblAYB].CORP_CUME_NET, [tblAYB].CAPACITY
FROM [tblAYB];

 

[dhookom]

It doesn't work because you didn't alias the table name in the subquery. Review my reply to see where I used:

FROM tblNoNameGiven NNG

If you don't give the table an alias, you will return the same high value for every record.

SELECT [tblAYB].ITEM_NUMBER, [tblAYB].FISCAL_YEAR, 
[tblAYB].FISCAL_WEEK_NUMBER, 
(SELECT Sum([GGG_DEM]+[DO_DEM])
 FROM [tblAYB] A
 WHERE A.ITEM_NUMBER = [tblAYB].[ITEM_NUMBER] AND
 A.PERIOD <=[tblAYB].PERIOD) AS Cume, 
[tblAYB].FISCAL_YEAR, [tblAYB].FISCAL_WEEK_NUMBER, 
[tblAYB].PERIOD AS Expr1, [tblAYB].DO_OH, [tblAYB].GGG_OH, 
[tblAYB].DO_OO, [tblAYB].GGG_OO, [tblAYB].DO_DEM, 
[tblAYB].GGG_DEM, [tblAYB].GGG_NET, [tblAYB].DO_NET, 
[tblAYB].CORP_NET, [tblAYB].GGG_CUME_NET, 
[tblAYB].DO_CUME_NET, [tblAYB].CORP_CUME_NET, [tblAYB].CAPACITY
FROM [tblAYB];

Most of your field names seem to suggest an un-normalized table structure. I could be wrong.

Duane
Hook'D on Access
MS Access MVP