473,322 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Reports-Twist to Running Totals - How?

Trying to show running totals on a report, except it needs to show one
total amount and values being subtracted from it. For example,
the report shows a Total Inventory amount (TotInvAmt). And then
amounts for each month for the orders like JanAmt, FebAmt, etc. I want
to show under each month column the following:

(TotInvAmt)-(JanAmt)=B ----result shows under the Jan column
B-(FebAmt)=C ---------result shows under the Feb column
C-(MarAmt)=D ---------result shows under the Mar column
D-(AprAmt)=E ---------result shows under the Apr column
**etc**
Tried running totals but it only calculated the values for Jan-Dec and
I dont know how to make it start subtracting from a set amount.

Thanks in advance for any help.

Nov 13 '05 #1
6 2920
OK well I just did this, but I thought maybe there was a better way.
for jan:
=(NZ([totInvamt])-NZ([Jan]))
for feb:
=(NZ([TonsAvail])-NZ([Jan])-nz([feb]))
for mar:
=(NZ([TonsAvail])-NZ([Jan])-nz([feb])-nz([mar]))

**etc.**

Nov 13 '05 #2
Now I'm running into a problem with years. So if the crosstab report
is splitting into years, then it is not calculating properly unless I
can find a way to break the JAN value to MMMYYYY which I tried.
Access cannot do this. It will only take the MMMYYYY in the result
which does not do well for a created report if the values change.

Do I need to create a Dynamic Crosstab report? I'm not very advanced
so can anyone direct me to a "simple" way to do this? Will search the
forums in the meantime.

Nov 13 '05 #3
KashMarsh wrote:
OK well I just did this, but I thought maybe there was a better way.


Without knowing exactly how your app works, the _INFINITELY_ better way
is most likely to simply NOT have columns/fields for each month. It
seems to me the data entity here is DATE (whether that be transaction
date or whatever). Then you can use functions such as Month(TransDate)
to extract the numeric month.

Note 8) 8) If the above statement makes you angry, as it sometimes does
database newbies who've put a lot of time into a not-adviseable design,
then that's a sure sign you've gone down the wrong route that many
people, more used to working with spreadsheets, often take when they
first start working with databases.... 8) 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4
Tim, doesn't make me angry and I understand you assuming all noobs to
Access come from a spreadsheet environment (like my boss for example!
:)) I really do not use Excel and have made simple Access programs
since 2.0. My tables are normalized and am proud to say that I do a
pretty good job at it. I just have trouble when I need to do something
much harder than what Access has provided. For example the crosstab
report I described - I'm certainly not an expert but would like to be
someday :). Here's more detail (and let me know if too much!):

I need to show projected number of items ordered in 2005 and 2006 per
month by customer-I can't user Month(transdate) because I need to show
ALL the 12 months and the year. I also need to show what those items
are and how much are in current inventory at each warehouse. As the
months will show projected items sold in the future, it needs to also
show diminishing inventory totals-- like a running subtraction starting
from current total inventory.

Using a crosstab qry and report, I can now show total current inventory
per item per warehouse. I can also show items ordered in the months
columns. I can even show each month subtracting from the starting
current inv total. What I can't show is at the end of 2005, take the
balance of the inventory, then roll that number over to 2006 and start
the rolling subtraction per month for 2006.

This is kinda what I need (I can do everything with a crosstab except
the running subtractions):
Example (assume all 12 months are on the report-needs to show all 12
months):

2005 items on order
Customer1 --------------
WarehouseA ..Nov Dec
Item #2 TotCurrInv 2,000 20 50
InvOnHand Bal for Item#2 1,980 1,930 <---1,930 is rolled to
2006

Item #3 TotCurrInv 500 300 400
InvOnHand Bal for Item#3 200 -100 <--- -100 is rolled to
2006

2006
Customer1
WarehouseA Jan Feb
Item #2 TotCurrInv 1,930 900 30
InvOnHand Bal for Item#2 1,030 1,000

Item #3 TotCurrInv -100 10 100
InvOnHand Bal for Item#3 -110 -210
This report shows that there will be a shortage of inventory Dec 2005
for item#3 at WarehouseA so better order more for that warehouse or
move inv there from another warehouse. They need to see all months
because if there is nothing ordered during that month, then they will
need to see that.
Should I look into studying about dynamic crosstabs? using pivot
tables? Or is there a simpler way? I appreciate any real help anyone
can give on this on ways to handle this.

Thanks in advance.

Nov 13 '05 #5
There seem to be asking quite a few questions here; I'll address only one:
You can use the "ColumnHeaders" property of your crosstab query to
define the columns you want to see. For example, you can put in the names
of all the months, separated by semicolons. That way you'll get a column
for each month, even if there's no data for it.

HTH

"KashMarsh" <ka********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Tim, doesn't make me angry and I understand you assuming all noobs to
Access come from a spreadsheet environment (like my boss for example!
:)) I really do not use Excel and have made simple Access programs
since 2.0. My tables are normalized and am proud to say that I do a
pretty good job at it. I just have trouble when I need to do something
much harder than what Access has provided. For example the crosstab
report I described - I'm certainly not an expert but would like to be
someday :). Here's more detail (and let me know if too much!):

I need to show projected number of items ordered in 2005 and 2006 per
month by customer-I can't user Month(transdate) because I need to show
ALL the 12 months and the year. I also need to show what those items
are and how much are in current inventory at each warehouse. As the
months will show projected items sold in the future, it needs to also
show diminishing inventory totals-- like a running subtraction starting
from current total inventory.

Using a crosstab qry and report, I can now show total current inventory
per item per warehouse. I can also show items ordered in the months
columns. I can even show each month subtracting from the starting
current inv total. What I can't show is at the end of 2005, take the
balance of the inventory, then roll that number over to 2006 and start
the rolling subtraction per month for 2006.

This is kinda what I need (I can do everything with a crosstab except
the running subtractions):
Example (assume all 12 months are on the report-needs to show all 12
months):

2005 items on order
Customer1 --------------
WarehouseA ..Nov Dec
Item #2 TotCurrInv 2,000 20 50
InvOnHand Bal for Item#2 1,980 1,930 <---1,930 is rolled to
2006

Item #3 TotCurrInv 500 300 400
InvOnHand Bal for Item#3 200 -100 <--- -100 is rolled to
2006

2006
Customer1
WarehouseA Jan Feb
Item #2 TotCurrInv 1,930 900 30
InvOnHand Bal for Item#2 1,030 1,000

Item #3 TotCurrInv -100 10 100
InvOnHand Bal for Item#3 -110 -210
This report shows that there will be a shortage of inventory Dec 2005
for item#3 at WarehouseA so better order more for that warehouse or
move inv there from another warehouse. They need to see all months
because if there is nothing ordered during that month, then they will
need to see that.
Should I look into studying about dynamic crosstabs? using pivot
tables? Or is there a simpler way? I appreciate any real help anyone
can give on this on ways to handle this.

Thanks in advance.

Nov 13 '05 #6
Thanks for the reply, MacDermott. The headers showing a month name
like "MMM" is possible and also built into Access's crosstab report
creation, however this didn't work with the running totals concept I
need to project on the report on multiple years :(. It just cannot do
the running totals, then carry over the ending balance for 2005 to 2006
and then start the running totals again for 2006.

Seems to me that a dynamic header creation is what I'm looking for, not
sure though, but going to start looking into this for a solution. I'm
thinking to make the running totals work in ACCESS, you cannot separate
the month values into years. You have to just have all the values run
across one row without grouping them out into years. It seems as if my
example earlier is not possible for running totals in an ACCESS
crosstab report. Maybe I should just look into Excel now (LOL).
*cringe*

Anyone else have any suggestions?

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: BStorm | last post by:
I have a transaction log file where the DataSet table's Description column is actually delimited into "subcolumns" based upon the transaction id. I would like to parse these into separate fields...
2
by: Andrew | last post by:
Hi there: I can successfully control a report's GroupLevel ControlSource property by using: ..Reports!rptEESTMT_A.GroupLevel(i).ControlSource = "CorpName" where rptEESTMT_A is the actual...
0
by: Ian | last post by:
(Sorry if I have repeated this, it did not appear the first time) I have the following code on a button. The idea is that when this button is clicked it prints several reports automatically then...
1
by: KEVIN97810 | last post by:
Hello to all, I am trying to fill all my reports in a listbox but I may not need to show other reports. How do you modify this function to do that. I have losts of reports but don't want to...
2
by: B.Newman | last post by:
I've got some VB.NET code that *should* get a list of reports from an Access MDB and populate a list box with them. It doesn't detect any of the reports at all. oAccess.Reports.Count comes up as...
3
by: VMI | last post by:
I know this may not be the best NG for this, but I feel you guys know more about this than any of the other NGs. I need to build several simple reports (over 50 of them and they get their data...
12
by: Tony Ciconte | last post by:
We are evaluating the prospect of integrating and/or using Crystal Reports with some of our current products. Some of these are still in Access 97 and are running well. Since we cannot include the...
16
by: JoeW | last post by:
I'm utilizing a database that I created within MS Access within a program I've created in VB.NET. I am using the VB front end to navigate the information, but want to be able to print a report,...
3
by: joelpollock | last post by:
I'm having trouble continuously page numbering a large report in MS Access. The report is made up of three separate Access reports which I join together at the end. In the past I have opened the...
7
by: google | last post by:
I am trying to automate the way reports are printed in an Access 2003 database - I have ~200 records (people) who require between 5 and 10 customized reports (depending on values within certain...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.