Sum of Horizonatl Values in Query

[Guest]

hopefully someone will be able to help me on this:

this is what my query looks like

last name Direct Training
Smith $1000 $2000
Black $2000 $3000

what I want to do is add up all the values for "Smith" and then All the
values for "Black"..it would look like this:

last name Direct Training TOTAL
Smith $1000 $2000 $3000
Black $2000 $3000 $5000

I am having a hard time creating a valid expression, any ideas?
Thank!

 

[Amy Blankenship]

hopefully someone will be able to help me on this:

this is what my query looks like

last name Direct Training
Smith $1000 $2000
Black $2000 $3000

what I want to do is add up all the values for "Smith" and then All the
values for "Black"..it would look like this:

last name Direct Training TOTAL
Smith $1000 $2000 $3000
Black $2000 $3000 $5000

SELECT LastName, Direct, Training, Direct + Training AS Total From YourTable

HTH;

Amy

 

[Guest]

If any of your fields could be NULL, you will need to use:

SELECT LastName, Direct, Training,

NZ(Direct,0) + NZ(Training,0) AS Total From YourTable

Dale

 

[Guest]

I am getting a syntax error.

My expression looks like this:
Total: [Last Name], [Direct], [Training], NZ(Direct,0) + NZ(Training,0)

I am sure that I have to use the NZ function because some of the values in
the field are blank. Is there something that I am missing?

 

[Guest]

Yes,

Your Total column should only include the numeric values:
Total: NZ(Direct,0) + NZ(Training, 0)

BTW, The fact that you have these columns in your table implies that your
database is probably not normalized properly, and that you are using it
similiar to a spreadsheet. I assume that these are costs associated with a
variety of categories. What happens when you want to add a new category?

You would be much better off with at data structure that looks something like:

LastName, FirstName, CostCategory, Cost
Smith, ???, Direct, 600
Smith, ???, Direct, 400
Smith, ???, Training, 1500
Smith, ???, Training, 500

With this structure, you could add a cost category and would not have to
rewrite all of your queries.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

I am getting a syntax error.

My expression looks like this:
Total: [Last Name], [Direct], [Training], NZ(Direct,0) + NZ(Training,0)

I am sure that I have to use the NZ function because some of the values in
the field are blank. Is there something that I am missing?

If any of your fields could be NULL, you will need to use:

NZ(Direct,0) + NZ(Training,0) AS Total From YourTable

Dale

 

[Guest]

I tried this expression as well and still blanks in my TOTAL column
Total: NZ(Direct,0) + NZ(Training, 0)

The structure that you proposed would work great if I wasn't so far
involved. The "Direct" and "Training" categories are actually a rollup for
each individual. So the $1000 for Smith is brought from the Tracking
Table($300 in April, $200 in May, $500 in June). Then I want to be able to
see on one report(from a table and therefore from a query) what each
individual sums up in each category and then all the categories.

Yes,

Your Total column should only include the numeric values:
Total: NZ(Direct,0) + NZ(Training, 0)

BTW, The fact that you have these columns in your table implies that your
database is probably not normalized properly, and that you are using it
similiar to a spreadsheet. I assume that these are costs associated with a
variety of categories. What happens when you want to add a new category?

You would be much better off with at data structure that looks something like:

LastName, FirstName, CostCategory, Cost
Smith, ???, Direct, 600
Smith, ???, Direct, 400
Smith, ???, Training, 1500
Smith, ???, Training, 500

With this structure, you could add a cost category and would not have to
rewrite all of your queries.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

I am getting a syntax error.

My expression looks like this:
Total: [Last Name], [Direct], [Training], NZ(Direct,0) + NZ(Training,0)

I am sure that I have to use the NZ function because some of the values in
the field are blank. Is there something that I am missing?

If any of your fields could be NULL, you will need to use:

SELECT LastName, Direct, Training,
NZ(Direct,0) + NZ(Training,0) AS Total From YourTable

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:


hopefully someone will be able to help me on this:

this is what my query looks like

last name Direct Training
Smith $1000 $2000
Black $2000 $3000

what I want to do is add up all the values for "Smith" and then All the
values for "Black"..it would look like this:

last name Direct Training TOTAL
Smith $1000 $2000 $3000
Black $2000 $3000 $5000


SELECT LastName, Direct, Training, Direct + Training AS Total From YourTable

HTH;

Amy

 

[Guest]

Stacy,

Post your entire SQL string so I can try to figure out what is going wrong.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

I tried this expression as well and still blanks in my TOTAL column
Total: NZ(Direct,0) + NZ(Training, 0)

The structure that you proposed would work great if I wasn't so far
involved. The "Direct" and "Training" categories are actually a rollup for
each individual. So the $1000 for Smith is brought from the Tracking
Table($300 in April, $200 in May, $500 in June). Then I want to be able to
see on one report(from a table and therefore from a query) what each
individual sums up in each category and then all the categories.

Yes,

Your Total column should only include the numeric values:
Total: NZ(Direct,0) + NZ(Training, 0)

BTW, The fact that you have these columns in your table implies that your
database is probably not normalized properly, and that you are using it
similiar to a spreadsheet. I assume that these are costs associated with a
variety of categories. What happens when you want to add a new category?

You would be much better off with at data structure that looks something like:

LastName, FirstName, CostCategory, Cost
Smith, ???, Direct, 600
Smith, ???, Direct, 400
Smith, ???, Training, 1500
Smith, ???, Training, 500

With this structure, you could add a cost category and would not have to
rewrite all of your queries.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

I am getting a syntax error.

My expression looks like this:
Total: [Last Name], [Direct], [Training], NZ(Direct,0) + NZ(Training,0)

I am sure that I have to use the NZ function because some of the values in
the field are blank. Is there something that I am missing?

:

If any of your fields could be NULL, you will need to use:

SELECT LastName, Direct, Training,
NZ(Direct,0) + NZ(Training,0) AS Total From YourTable

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:


hopefully someone will be able to help me on this:

this is what my query looks like

last name Direct Training
Smith $1000 $2000
Black $2000 $3000

what I want to do is add up all the values for "Smith" and then All the
values for "Black"..it would look like this:

last name Direct Training TOTAL
Smith $1000 $2000 $3000
Black $2000 $3000 $5000


SELECT LastName, Direct, Training, Direct + Training AS Total From YourTable

HTH;

Amy

 

[Guest]

Dale-
It is now working...I had to Group by Sum. Thanks for the help

Stacy,

Post your entire SQL string so I can try to figure out what is going wrong.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

I tried this expression as well and still blanks in my TOTAL column
Total: NZ(Direct,0) + NZ(Training, 0)

The structure that you proposed would work great if I wasn't so far
involved. The "Direct" and "Training" categories are actually a rollup for
each individual. So the $1000 for Smith is brought from the Tracking
Table($300 in April, $200 in May, $500 in June). Then I want to be able to
see on one report(from a table and therefore from a query) what each
individual sums up in each category and then all the categories.

Yes,

Your Total column should only include the numeric values:
Total: NZ(Direct,0) + NZ(Training, 0)

BTW, The fact that you have these columns in your table implies that your
database is probably not normalized properly, and that you are using it
similiar to a spreadsheet. I assume that these are costs associated with a
variety of categories. What happens when you want to add a new category?

You would be much better off with at data structure that looks something like:

LastName, FirstName, CostCategory, Cost
Smith, ???, Direct, 600
Smith, ???, Direct, 400
Smith, ???, Training, 1500
Smith, ???, Training, 500

With this structure, you could add a cost category and would not have to
rewrite all of your queries.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I am getting a syntax error.

My expression looks like this:
Total: [Last Name], [Direct], [Training], NZ(Direct,0) + NZ(Training,0)

I am sure that I have to use the NZ function because some of the values in
the field are blank. Is there something that I am missing?

:

If any of your fields could be NULL, you will need to use:

SELECT LastName, Direct, Training,
NZ(Direct,0) + NZ(Training,0) AS Total From YourTable

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:


hopefully someone will be able to help me on this:

this is what my query looks like

last name Direct Training
Smith $1000 $2000
Black $2000 $3000

what I want to do is add up all the values for "Smith" and then All the
values for "Black"..it would look like this:

last name Direct Training TOTAL
Smith $1000 $2000 $3000
Black $2000 $3000 $5000


SELECT LastName, Direct, Training, Direct + Training AS Total From YourTable

HTH;

Amy