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. 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.**
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.
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
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.
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.
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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |