L
Lisa R
Can I find the sum of values of consecutive columns in a row to be displayed
at the end of the row?
at the end of the row?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Sheridan
Add the values in a computed column in a query, calling the NZ function to
cater for any which are NULL e.g.
SELECT Column1, Column2, Column3
NZ(Column1,0) + NZ(Column2,0) + NZ(Column3,0)
AS SumOfColumns
FROM YourTable;
Whether the columns are 'consecutive' or not is irrelevant.
Ken Sheridan
Stafford, England
cater for any which are NULL e.g.
SELECT Column1, Column2, Column3
NZ(Column1,0) + NZ(Column2,0) + NZ(Column3,0)
AS SumOfColumns
FROM YourTable;
Whether the columns are 'consecutive' or not is irrelevant.
Ken Sheridan
Stafford, England
P
Penny
I'm attempting to take your advice on this, but keep getting syntax errors
(missing operator) in qtdearnded03.
Here's my query:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03
NZ( qtdearnded00,0)+NZ( qtdearnded01,0),+NZ( qtdearnded02,0), +NZ(
qtdearnded03,0)
I've never attempted this in access before, but I couldn't figure out how to
accomplish it in SQL Server. I'm more of an Oracle Database person.
(missing operator) in qtdearnded03.
Here's my query:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03
NZ( qtdearnded00,0)+NZ( qtdearnded01,0),+NZ( qtdearnded02,0), +NZ(
qtdearnded03,0)
I've never attempted this in access before, but I couldn't figure out how to
accomplish it in SQL Server. I'm more of an Oracle Database person.
Ken Sheridan said:Add the values in a computed column in a query, calling the NZ function to
cater for any which are NULL e.g.
SELECT Column1, Column2, Column3
NZ(Column1,0) + NZ(Column2,0) + NZ(Column3,0)
AS SumOfColumns
FROM YourTable;
Whether the columns are 'consecutive' or not is irrelevant.
Ken Sheridan
Stafford, England
J
John W. Vinson
I'm attempting to take your advice on this, but keep getting syntax errors
(missing operator) in qtdearnded03.
Looks like an extra comma slipped in: try
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03
NZ( qtdearnded00,0)+NZ( qtdearnded01,0),+NZ( qtdearnded02,0)+NZ(
qtdearnded03,0) AS sumofqts FROM yourtable;
This assumes that the table indeed has fields named qtdearnded00,
qtdearnded01, qtdearnded02, and qtdearnded03 and that you're trying to
calculate their sum.
P
Penny
I copied the query you had below and got the same identical error. When you
mentioned the extra comma, there was in fact one that I believe doesn't
belong:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) + NZ(qtdearnded02,0) +
NZ(qtdearnded03,0)
AS sumofqts FROM Census2007
When I tried copying and pasting your query and ran it, I got the same
error, then I noticed the comma that doesn't belong right after NZ(
qtdearnded01,0), and took it out, I'm still getting the same error it reads
with more verbeage:
Syntax error (missing operator) in query expression 'qtdearnded03
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) +
NZ(qtdearnded02,0) + NZ(qtdearnded03,0)
And of course help isn't very helpful about what it's complaining about. It
is highlighting the NZ like it doesn't like it. Is it possible that I need
to have something else installed like an add-in or something? I'm wondering
if that's what the real issue is.
At any rate, I'm new to this company and was passed this assignment on a
very short notice. Someone else was supposed to provide this information to
the state, and they no longer work for the company, so it was thrown in my
lap at the last minute. As easy as it would sound, I've not been able to
cleanly be able to do this, and it's turning out to be a royal pain in the
butt.
I have a lot of experience with database, and in Oracle I can do this very
easily, we don't have Oracle at this location, so it's not been easy for me
to do this on what is available to me. Please, can you help me figure out
why this is being such a pain?
mentioned the extra comma, there was in fact one that I believe doesn't
belong:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) + NZ(qtdearnded02,0) +
NZ(qtdearnded03,0)
AS sumofqts FROM Census2007
When I tried copying and pasting your query and ran it, I got the same
error, then I noticed the comma that doesn't belong right after NZ(
qtdearnded01,0), and took it out, I'm still getting the same error it reads
with more verbeage:
Syntax error (missing operator) in query expression 'qtdearnded03
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) +
NZ(qtdearnded02,0) + NZ(qtdearnded03,0)
And of course help isn't very helpful about what it's complaining about. It
is highlighting the NZ like it doesn't like it. Is it possible that I need
to have something else installed like an add-in or something? I'm wondering
if that's what the real issue is.
At any rate, I'm new to this company and was passed this assignment on a
very short notice. Someone else was supposed to provide this information to
the state, and they no longer work for the company, so it was thrown in my
lap at the last minute. As easy as it would sound, I've not been able to
cleanly be able to do this, and it's turning out to be a royal pain in the
butt.
I have a lot of experience with database, and in Oracle I can do this very
easily, we don't have Oracle at this location, so it's not been easy for me
to do this on what is available to me. Please, can you help me figure out
why this is being such a pain?
J
John W. Vinson
I copied the query you had below and got the same identical error. When you
mentioned the extra comma, there was in fact one that I believe doesn't
belong:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) + NZ(qtdearnded02,0) +
NZ(qtdearnded03,0)
AS sumofqts FROM Census2007
When I tried copying and pasting your query and ran it, I got the same
error, then I noticed the comma that doesn't belong right after NZ(
qtdearnded01,0), and took it out, I'm still getting the same error it reads
with more verbeage:
Syntax error (missing operator) in query expression 'qtdearnded03
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) +
NZ(qtdearnded02,0) + NZ(qtdearnded03,0)
Apologies... it wasn't an extra comma it was a misplaced comma. The error
message does in fact explain: there is no operator between the fieldname
qtdearnded03 and the calculated expression. There needs to be a comma:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03,
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) + NZ(qtdearnded02,0) +
NZ(qtdearnded03,0)
AS sumofqts FROM Census2007
P
Penny
Actually I was able to do it with out the NZ function,
SELECT Census2007.name,
(qtdearnded00)+(qtdearnded01)+(qtdearnded02)+(qtdearnded03) AS sumofqts INTO
[Sum of 401K (ordered by name)]
FROM Census2007
ORDER BY Census2007.name;
I'm going to give it a lookthrough, but it appears to be working.
I wouldn't EVER peacemeal this the way I am, but I've been given a task that
I really don't have a better way to do right now because I don't have the
luxury of time to learn a reporting program. I've always been the DBA
writing the queries to extract the data, then pass it off to the developers
who make all this magic happen.
This was a task handed to me with a tight deadline, moving forward, if this
is something that's going to be expected of me on an anual basis, I've got to
make it a priority to do this in a more elegant way. Do you have any
suggestions for how I can go about doing this? Initially this came out of a
SQL Server database, surely there's a better way to do this, maybe through
SQL Server reporting services or something?
The challange is the program doesn't total up the earnings and deductions,
each item has it's own code, and the data is seperated by quarters. In order
to get all the types of earnings and deductions the queries bring back
several rows per employee. It's a real mess to contend with. This program
should have a canned report to take care of messes like this, but it doesn't.
Somehow I've got to educate myself to be able to take data like this and
write a report that does all the calculations... Whether it be in SQL Server
or Access, I don't care, but it's got to be less labor intensive than how I'm
doing it now due to lack of knowing how to do it "The right way".
SELECT Census2007.name,
(qtdearnded00)+(qtdearnded01)+(qtdearnded02)+(qtdearnded03) AS sumofqts INTO
[Sum of 401K (ordered by name)]
FROM Census2007
ORDER BY Census2007.name;
I'm going to give it a lookthrough, but it appears to be working.
I wouldn't EVER peacemeal this the way I am, but I've been given a task that
I really don't have a better way to do right now because I don't have the
luxury of time to learn a reporting program. I've always been the DBA
writing the queries to extract the data, then pass it off to the developers
who make all this magic happen.
This was a task handed to me with a tight deadline, moving forward, if this
is something that's going to be expected of me on an anual basis, I've got to
make it a priority to do this in a more elegant way. Do you have any
suggestions for how I can go about doing this? Initially this came out of a
SQL Server database, surely there's a better way to do this, maybe through
SQL Server reporting services or something?
The challange is the program doesn't total up the earnings and deductions,
each item has it's own code, and the data is seperated by quarters. In order
to get all the types of earnings and deductions the queries bring back
several rows per employee. It's a real mess to contend with. This program
should have a canned report to take care of messes like this, but it doesn't.
Somehow I've got to educate myself to be able to take data like this and
write a report that does all the calculations... Whether it be in SQL Server
or Access, I don't care, but it's got to be less labor intensive than how I'm
doing it now due to lack of knowing how to do it "The right way".
J
John W. Vinson
Actually I was able to do it with out the NZ function,
SELECT Census2007.name,
(qtdearnded00)+(qtdearnded01)+(qtdearnded02)+(qtdearnded03) AS sumofqts INTO
[Sum of 401K (ordered by name)]
FROM Census2007
ORDER BY Census2007.name;
I'm going to give it a lookthrough, but it appears to be working.
It will work *if every single record in the table contains data for all four
fields*.
If any of the qtdearndedxx fields is NULL, the sum will be NULL.
If the fields are Required, or if you're absolutely 100% confidant that
they'll never be empty, then leave out the NZ. I'm not usually so trusting.
I wouldn't EVER peacemeal this the way I am, but I've been given a task that
I really don't have a better way to do right now because I don't have the
luxury of time to learn a reporting program. I've always been the DBA
writing the queries to extract the data, then pass it off to the developers
who make all this magic happen.
This was a task handed to me with a tight deadline, moving forward, if this
is something that's going to be expected of me on an anual basis, I've got to
make it a priority to do this in a more elegant way. Do you have any
suggestions for how I can go about doing this? Initially this came out of a
SQL Server database, surely there's a better way to do this, maybe through
SQL Server reporting services or something?
Well, so far I've seen only the challenge of summing four fields. I have no
idea what the entire project might be, or anything about the nature of the
data, the calculations you want to do, etc. Not much I can do to help based
on what you've posted in this thread!
The challange is the program doesn't total up the earnings and deductions,
each item has it's own code, and the data is seperated by quarters. In order
to get all the types of earnings and deductions the queries bring back
several rows per employee. It's a real mess to contend with. This program
should have a canned report to take care of messes like this, but it doesn't.
Again... you can see your table structure. I can't.
Somehow I've got to educate myself to be able to take data like this and
write a report that does all the calculations... Whether it be in SQL Server
or Access, I don't care, but it's got to be less labor intensive than how I'm
doing it now due to lack of knowing how to do it "The right way".
Is the data currently stored in an Access JET .mdb, or in SQL/Server (not that
it matters all that much)? The queries can be done in SQL using T/SQL or
stored procedures; or they can be done in Access queries (using either JET or
SQL as the data storage medium); some queries can be done directly in Report
controls. It's tough just because there *are* so many options, and no clearly
obvious "the right way".
If you post specific problems, we'll be glad to help, and most of us will
(usually, we all get tired and hasty) try to explain the principles not just
the solution.
P
Penny
maybe I'm making this more complicated than it needs to be. Is there a way
to group all the different values for each employee and roll it up into just
one row?
to group all the different values for each employee and roll it up into just
one row?
Penny said:Actually I was able to do it with out the NZ function,
SELECT Census2007.name,
(qtdearnded00)+(qtdearnded01)+(qtdearnded02)+(qtdearnded03) AS sumofqts INTO
[Sum of 401K (ordered by name)]
FROM Census2007
ORDER BY Census2007.name;
I'm going to give it a lookthrough, but it appears to be working.
I wouldn't EVER peacemeal this the way I am, but I've been given a task that
I really don't have a better way to do right now because I don't have the
luxury of time to learn a reporting program. I've always been the DBA
writing the queries to extract the data, then pass it off to the developers
who make all this magic happen.
This was a task handed to me with a tight deadline, moving forward, if this
is something that's going to be expected of me on an anual basis, I've got to
make it a priority to do this in a more elegant way. Do you have any
suggestions for how I can go about doing this? Initially this came out of a
SQL Server database, surely there's a better way to do this, maybe through
SQL Server reporting services or something?
The challange is the program doesn't total up the earnings and deductions,
each item has it's own code, and the data is seperated by quarters. In order
to get all the types of earnings and deductions the queries bring back
several rows per employee. It's a real mess to contend with. This program
should have a canned report to take care of messes like this, but it doesn't.
Somehow I've got to educate myself to be able to take data like this and
write a report that does all the calculations... Whether it be in SQL Server
or Access, I don't care, but it's got to be less labor intensive than how I'm
doing it now due to lack of knowing how to do it "The right way".
John W. Vinson said:Apologies... it wasn't an extra comma it was a misplaced comma. The error
message does in fact explain: there is no operator between the fieldname
qtdearnded03 and the calculated expression. There needs to be a comma:
select qtdearnded00, qtdearnded01, qtdearnded02, qtdearnded03,
NZ(qtdearnded00,0) + NZ(qtdearnded01,0) + NZ(qtdearnded02,0) +
NZ(qtdearnded03,0)
AS sumofqts FROM Census2007
J
John W. Vinson
maybe I'm making this more complicated than it needs to be. Is there a way
to group all the different values for each employee and roll it up into just
one row?
Probably... but remember: YOU know what "all the different values" are and how
they're stored. We don't.
Please post some information about the structure of your tables and what
you're trying to accomplish. We're glad to help, but we can't work in the
dark!
P
Penny
In all honesty I'm not sure what about the structure you're asking for.
We're only dealing with 2 tables here, the employee and earnings table.
Unfortunately the system doesn't store the earnings/deductions in a column
that's already summed up, it only stores the data on a quarterly basis. The
idea is to get a single row back per employee.
To make this even more aggravating is the fact that I'm also dealing with
several variations of deductions that are stored as codes which brings back
several rows per employee based on 401K, 401K1, 401KD, 401KDR, 125D, 125I,
and 125M.
Although I can select the data, I don't know how to sum and roll all these
different deductions up into a single row per employee.
Does this give you enough info?
We're only dealing with 2 tables here, the employee and earnings table.
Unfortunately the system doesn't store the earnings/deductions in a column
that's already summed up, it only stores the data on a quarterly basis. The
idea is to get a single row back per employee.
To make this even more aggravating is the fact that I'm also dealing with
several variations of deductions that are stored as codes which brings back
several rows per employee based on 401K, 401K1, 401KD, 401KDR, 125D, 125I,
and 125M.
Although I can select the data, I don't know how to sum and roll all these
different deductions up into a single row per employee.
Does this give you enough info?
J
John W. Vinson
In all honesty I'm not sure what about the structure you're asking for.
We're only dealing with 2 tables here, the employee and earnings table.
Unfortunately the system doesn't store the earnings/deductions in a column
that's already summed up, it only stores the data on a quarterly basis. The
idea is to get a single row back per employee.
To make this even more aggravating is the fact that I'm also dealing with
several variations of deductions that are stored as codes which brings back
several rows per employee based on 401K, 401K1, 401KD, 401KDR, 125D, 125I,
and 125M.
Although I can select the data, I don't know how to sum and roll all these
different deductions up into a single row per employee.
Does this give you enough info?
No, sorry, but it doesn't.
Please post the structure of your tables in the format
Tablename
Fieldname Datatype <Primary Key>
Fieldname Datatype
Fieldname Datatype
If it's not obvious from the fieldname, please explain the meaning of the
field. YOU know what 125D means... I have no taste for accounting and I have
no idea; if it's necessary to figure out how to do the calculations, it would
help me to know.
If you could also explain what you want to sum over what ranges of records it
would be very helpful.