Running total

[spoole]

Ahhhh this is doing my nugget in!!! I have a simple table with 4 fields
ID (unique number)
DATE (date)
CAPACITY (number of SKU we can hold)
ORDERS (number of SKU on order)
the data looks like this

ID DATE CAPACITY ORDERS
1 01/01/2007 250000 250000
2 02/01/2007 250000 300000
3 03/01/2007 250000 300000
4 04/01/2007 250000 300000

So looking at the above table we can see that we have more orders than capacity in our factory, however they require to see this in graph form, so what I need is for each ID a running total of the CAPACITY and ORDERS so over a given date range i would produce a graph to find the "pinch points" where we could see if the capacity is less than the orders we have over time.

so my new table would be:


ID DATE CAPACITY ORDERS CAPRUN ORDRUN
1 01/01/2007 250000 250000 250000 250000
2 02/01/2007 250000 300000 500000 550000
3 03/01/2007 250000 300000 750000 850000
4 04/01/2007 250000 300000 1000000 1150000

etc. which i would create my graph from. Ive looked at Dsum and some other methods but cant get my head around it so any help will be much appreciated.
Thanks Steve.

 

[KenHigg]

You may be able to do this once the data gets to a report using 'Running Sum' options in a text boxes properties...

 

[spoole]

hi Ken thanks for the reply. However I need this in a graph format that I will insert into a form so it needs to be done in query. Is it possible to base a graph on a report?

 

[KenHigg]

I think you can use ansi sql statements to do something akin to running sums but I've never tried it - Maybe somebody else can jump in and offer a suggestion -

 

[marklane21]

--------------------------------------------------------------------------------try this: make a select query with the wizard and use this table with all those fields that you mentioned. in the query where it says criteria type between [beginning date] and [ending date] and this will filter your records to these dates. on this query click on a blank field and type =datepart("ww",[date],2,0) this will give you the week and then in another field type =datepart("yyyy",[date],2,0) and this will give you the year. if you want to sum by week or month you can change the "ww" to "m" for month. make a query from the query you just made and select all fields except the date and make sure you week and year fields are there. by the way you should name them before making this query and to do that you just click on the work expr1 and highlight it and name it week, do the same for the year and name it year. i hope this helps and if you have any questions just e-mail me at marklane21@yahoo.commarklane21@yahoo.com
Ahhhh this is doing my nugget in!!! I have a simple table with 4 fields
ID (unique number)
DATE (date)
CAPACITY (number of SKU we can hold)
ORDERS (number of SKU on order)
the data looks like this

ID DATE CAPACITY ORDERS
1 01/01/2007 250000 250000
2 02/01/2007 250000 300000
3 03/01/2007 250000 300000
4 04/01/2007 250000 300000

 

[Mike375]

It seems your main issue is to get a running total into a field and of course for each record.

This is how I do it. Crude and Ken will jump all over me

I do it macros but obviously VB will do it.

From what I can see your field Orders is the amount for each date and ORDRUN will be the addition of Orders and with the data in Orders being the total from date abc to date xyz

The first thing my macro does is go to the control that has the ID number and then it sorts in ascending order and then does GoToRecord....>First

It then does SetValue which would be the equivalent of you having [Orders]=[ORDRUN]

It then goes to the control that would be [ORDRUN] and does Copy. It then goes to the next record and again the control [ORDRUN] It then does Paste.

It then does SetValue as [Orders]+[ORDRUN]=[ORDRUN]

It then does a RunMacro action which is basicallya repeat of the last section of the above and the macro stops when [xyz or whatever] Is Not Null.

One of things a couple of use it for is diet/calories control. So an entry is made every couple of hours or whatever. The from normally opens with a query that is for the current date. Thus if went back to the table you would see the runninng total starting again for each date. However, I could slect the records on any basis and the run the above macro and get a new set of running totals.

I guess the other way to do it would be to send the table across to Excel but that might be very messy, especially the bit coming back to Access

If decide to do the above make sure you have Echo=Yes, lots of records and it just whizzes down the screen

Mike

 

[Mike375]

I forgot. Here are the two macros.

Number is an autonumber. Calories would equate to your ORDERS and Total would be ORDRUN. Macro2 is quite small as it has a StopMacro early on

 

[bakerratface]

I am trying to create a query that will show the cumulative total of samples that a predefined person has processed (agential I want to graph this in a report).

I have tried to use the DSum function however I only get the total of all entries in each row e.g. they all say the same
The expression that I used is;
RunTot: Format (DSum("[Number of Samples]","sample prep"))

Can anyone tell me what I have done wrong or suggest a better way of doing it.

Tank-you,
Alexander

 

[Cosmos75]

Maybe this might be helpful.
- Running Sum / Total / Count / Average in a query using a correlated subquery

 

[bakerratface]

Thanks
Is there anyway way to avoid the SQL statements because I do not understand how they work? If not how the best way to find out hoe they works

Alexander

 

[Cosmos75]

bakerratface,

You can use DSum but it is slower that using a subquery statement.

See these two threads:
- Running Sum (using DSum) Query to create Graph / Chart (where I learned how to do running totals)
- Really Urgent DSUM Problem (in particular, read Pat Hartman's post on using DSUM in this thread)

 

[bakerratface]

Thank-you,
I have managed to get the Dsum to work

My finished expression looks like this
Expr1: DSum("number","table1","[id]<=" & [id] & "")

Can you tell me what the last part of the expression means because at the moment it will provide me with a running total of everything even if I filter out some of the “number” what I ultimately want to create is a query that will select all of the number of samples that an individual possessed then auto number them.

Hope you have a happy new year,
Alexander

 

[Cosmos75]

bakerratface,

Your formula DSum("number","table1","[id]<=" & [id] & "") does the following:
- Sum the field 'number' for ALL records in 'table1' where the field 'id' is less than or equal to the value for [id] in the current returned row in this query.

See these two articles, they might be helpful.
- DSum Function
- How to create a running totals query in Microsoft Access

 

[bakerratface]

Thanks

If I use DSum("number","table1","[name]= 'a') the formula totals everything that where the name column = ‘a’

How do I combine this with the previous formula so that I get the running sum of this?

I have tried the following but it just comes up if #error in the expr1 column.
DSum("number","table1","[name]= 'a' " & "[id]<=" & [id] & "")


thank-you
Alexander

 

[Mike375]

Alexander

I have been OK with DSum except for the following problem, which I solve with a work around and a Macro/Set Value/GoToNextRecord etc but it would be neater etc.....

I made a little calorie counter and with progressive total during the day. The query/form opens for the current date. With DSum as in

DSum("number","table1","[id]<=" & [id] & "")

the progressive total is for all records.

What I do at the moment looks like below and it would be good to get DSum to do the same.

Table values over a period of time look like below, except of course a lot more records per day.

30.............30
120............150
100............250
60.............310
350............660
50..............50
120............170
60..............230
280.............510

If I "show all days" then the macro will give progressive for the full record set. To return the macro is rerun for the records for each day. In either case the records a sorted on a number.

 

[Cosmos75]

How do I combine this with the previous formula so that I get the running sum of this?

I have tried the following but it just comes up if #error in the expr1 column.
DSum("number","table1","[name]= 'a' " & "[id]<=" & [id] & "")

Try DSum("number","table1","[name]= 'a' AND " & "[id]<=" & [id])

Keep in mind this will only work for the case where [name]=a. You can use the field value in the query.
DSum("number","table1","[name]= '" & [name] & "' AND " & "[id]<=" & [id])

A word of caution, it's not a good idea to use reserved words like Name when naming your fields.

 

[bakerratface]

tank -you. Thank-you about warning me about the naming things and ‘name’ when I know how do everything that I need to do I will go back and look at the conventions like that.

Thank-you so much,
Alexander

 

[bakerratface]

Is there a way that I can alter which person I want the running total to work for every time the query is run by being prompted to enter a name.

Thanks, Alexander

 

[spoole]

Not sure but I would get round it by running a query before that to select the person you want the runnnig total for, and then join the result to the running sum query.

 

[bakerratface]

I have tried it but for some reason if I enter “ab” in the criteria everything works perfectly. But if I enter [name] and when the enter parameter value appears I enter ab. Allow the correct fields are returned I get an error in the column that should have the running total. Am I entering somthing wrong

Thank-you,
Alexander