I am having an issue trying to add 4 columns together to get a total column.
It seems that if there is a blank in any of the 4 columns it will not give me
a total. What is wrong?
Here is my formula;
Cum Actuals: ([2005]+[2006]+[2007]+[2008])
John W. Vinson said:I am having an issue trying to add 4 columns together to get a total column.
It seems that if there is a blank in any of the 4 columns it will not give me
a total. What is wrong?
Here is my formula;
Cum Actuals: ([2005]+[2006]+[2007]+[2008])
Karl's answer is of course correct for solving this immediate problem... but
you have a bigger problem.
What you have is not a database. You have a spreadsheet!
It's normal in Excel to have data in column headers, and to have a "field" for
each year. In a relational database that structure IS WRONG. It leads to
absurdities like needing to change the very structure of your table, your
forms, all your queries, all your reports *every year*.
Data - e.g. years - should NOT be stored in fieldnames. You should instead
have a tall-thin table with a date field, or a SalesYear field (don't use the
fieldname Year, it's a reserved word), and you could then add new data for
2009 or 2023 or whenever, just by adding a new record; and your problem
vanishes, because a very simple Totals query summing ROWS of data will get
your total.
Schwimms said:John, I wouldn't say that it is incorrect because the reason why 2005 2006
2007 are headers are because I created a cross tab query. The output of that
is for my excel spreadsheet. Which leads to my next problem.
I am importing this query in from excel but it will not allow it because of
the formula that Karl gave me. Is there another way to get this in?
John W. Vinson said:I am having an issue trying to add 4 columns together to get a total column.
It seems that if there is a blank in any of the 4 columns it will not give me
a total. What is wrong?
Here is my formula;
Cum Actuals: ([2005]+[2006]+[2007]+[2008])
Karl's answer is of course correct for solving this immediate problem... but
you have a bigger problem.
What you have is not a database. You have a spreadsheet!
It's normal in Excel to have data in column headers, and to have a "field" for
each year. In a relational database that structure IS WRONG. It leads to
absurdities like needing to change the very structure of your table, your
forms, all your queries, all your reports *every year*.
Data - e.g. years - should NOT be stored in fieldnames. You should instead
have a tall-thin table with a date field, or a SalesYear field (don't use the
fieldname Year, it's a reserved word), and you could then add new data for
2009 or 2023 or whenever, just by adding a new record; and your problem
vanishes, because a very simple Totals query summing ROWS of data will get
your total.
John, I wouldn't say that it is incorrect because the reason why 2005 2006
2007 are headers are because I created a cross tab query.
KARL DEWEY said:It sounds like you are doing the addition in Excel from your response. Where
are you doing the summing? What I posted was for use in Access.
--
KARL DEWEY
Build a little - Test a little
Schwimms said:John, I wouldn't say that it is incorrect because the reason why 2005 2006
2007 are headers are because I created a cross tab query. The output of that
is for my excel spreadsheet. Which leads to my next problem.
I am importing this query in from excel but it will not allow it because of
the formula that Karl gave me. Is there another way to get this in?
John W. Vinson said:On Tue, 18 Mar 2008 08:49:02 -0700, Schwimms
I am having an issue trying to add 4 columns together to get a total column.
It seems that if there is a blank in any of the 4 columns it will not give me
a total. What is wrong?
Here is my formula;
Cum Actuals: ([2005]+[2006]+[2007]+[2008])
Karl's answer is of course correct for solving this immediate problem... but
you have a bigger problem.
What you have is not a database. You have a spreadsheet!
It's normal in Excel to have data in column headers, and to have a "field" for
each year. In a relational database that structure IS WRONG. It leads to
absurdities like needing to change the very structure of your table, your
forms, all your queries, all your reports *every year*.
Data - e.g. years - should NOT be stored in fieldnames. You should instead
have a tall-thin table with a date field, or a SalesYear field (don't use the
fieldname Year, it's a reserved word), and you could then add new data for
2009 or 2023 or whenever, just by adding a new record; and your problem
vanishes, because a very simple Totals query summing ROWS of data will get
your total.
Schwimms said:Karl,
I am trying to do the summing in access with the formula you gave me, but I
cannot import that field into excel because excel doesnt recognize the
command NZ.
I am not exporting from access, I am going into excel and then opening
access so that I can have a refreshable file.
KARL DEWEY said:It sounds like you are doing the addition in Excel from your response. Where
are you doing the summing? What I posted was for use in Access.
--
KARL DEWEY
Build a little - Test a little
Schwimms said:John, I wouldn't say that it is incorrect because the reason why 2005 2006
2007 are headers are because I created a cross tab query. The output of that
is for my excel spreadsheet. Which leads to my next problem.
I am importing this query in from excel but it will not allow it because of
the formula that Karl gave me. Is there another way to get this in?
:
On Tue, 18 Mar 2008 08:49:02 -0700, Schwimms
I am having an issue trying to add 4 columns together to get a total column.
It seems that if there is a blank in any of the 4 columns it will not give me
a total. What is wrong?
Here is my formula;
Cum Actuals: ([2005]+[2006]+[2007]+[2008])
Karl's answer is of course correct for solving this immediate problem... but
you have a bigger problem.
What you have is not a database. You have a spreadsheet!
It's normal in Excel to have data in column headers, and to have a "field" for
each year. In a relational database that structure IS WRONG. It leads to
absurdities like needing to change the very structure of your table, your
forms, all your queries, all your reports *every year*.
Data - e.g. years - should NOT be stored in fieldnames. You should instead
have a tall-thin table with a date field, or a SalesYear field (don't use the
fieldname Year, it's a reserved word), and you could then add new data for
2009 or 2023 or whenever, just by adding a new record; and your problem
vanishes, because a very simple Totals query summing ROWS of data will get
your total.