Reports-Twist to Running Totals - How?

[KashMarsh]

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.

[KashMarsh]

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.**

[KashMarsh]

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.

[Tim Marshall]

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

[KashMarsh]

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.

[MacDermott]

There seem to be asking quite a few questions here; I'll address only one:
You can use the "ColumnHead ers" 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********@hot mail.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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.

[KashMarsh]

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?