Adding two fields within in query together to create a new field

[Lori Burton]

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries, forms or
reports for these types of actions.

Thanks for your help.
-- Lori

 

[Ron Hinds]

Use a query to return the data. Forms and Reports are for displaying the
data. I'm not sure what calculation you want to perform, but let's say it is
figuring a percentage. To do what you want in a query, the SQL would look
like this (guessing at the column names):

SELECT [TotalProposals], [TotalJobs], [TotalJobs]/[TotalProposals] AS
Percentage FROM your_table_name

To achieve the same result in the Query Builder, you would select the first
two columns, then in the thrid column type:

Percentage:[TotalJobs]/[TotalProposals]

 

[Roger Carlson]

You can use either queries, forms or reports, depending on what you want to
do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so I'll have
to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Lori Burton]

The results I am looking for is merely the Total Proposals + Total Awards.
There is only one table in my query "Marketing Spreadsheet"

The fields are Week (Group By), Total Week Proposals: Weeks (Count), Total
Proposal Value: PValue (Sum) and Total Awarded Value: AValue (Sum). I want
to add a third colum that will total the results of PValue and AValue.
--
Lori

Use a query to return the data. Forms and Reports are for displaying the
data. I'm not sure what calculation you want to perform, but let's say it is
figuring a percentage. To do what you want in a query, the SQL would look
like this (guessing at the column names):

SELECT [TotalProposals], [TotalJobs], [TotalJobs]/[TotalProposals] AS
Percentage FROM your_table_name

To achieve the same result in the Query Builder, you would select the first
two columns, then in the thrid column type:

Percentage:[TotalJobs]/[TotalProposals]

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries, forms or
reports for these types of actions.

Thanks for your help.
-- Lori

 

[Lori Burton]

Actually, I just played with it some more using the query builder and it
worked perfectly.

Thank you
--
Lori

Use a query to return the data. Forms and Reports are for displaying the
data. I'm not sure what calculation you want to perform, but let's say it is
figuring a percentage. To do what you want in a query, the SQL would look
like this (guessing at the column names):

SELECT [TotalProposals], [TotalJobs], [TotalJobs]/[TotalProposals] AS
Percentage FROM your_table_name

To achieve the same result in the Query Builder, you would select the first
two columns, then in the thrid column type:

Percentage:[TotalJobs]/[TotalProposals]

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries, forms or
reports for these types of actions.

Thanks for your help.
-- Lori

 

[Lori Burton]

One more snag,

The expression I used to get the total is

Expr1: [Total Proposal Value]+[Total Awarded Value]

However, where one of the two fields has an empty value the calculation does
not occur.
--
Lori

You can use either queries, forms or reports, depending on what you want to
do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so I'll have
to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries, forms or
reports for these types of actions.

Thanks for your help.
-- Lori

 

[Roger Carlson]

Ah, that's because you can't use NULL in an expression. We don't know what
the value of NULL is, so we can't use it in a calculation. To make this
work, use the Nz function (NulltoZero) to convert the Null value to zero.

Expr1: Nz([Total Proposal Value])+Nz([Total Awarded Value])

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

One more snag,

The expression I used to get the total is

Expr1: [Total Proposal Value]+[Total Awarded Value]

However, where one of the two fields has an empty value the calculation does
not occur.
--
Lori

You can use either queries, forms or reports, depending on what you want to
do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so I'll have
to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries,

forms
or

reports for these types of actions.

Thanks for your help.
-- Lori

 

[Ron Hinds]

Change it to this:

Expr1: IIf(IsNull([Total Proposal Value]), 0, [Total Proposal
Value])+IIf(IsNull([Total Awarded Value]), 0, [Total Awarded Value])

One more snag,

The expression I used to get the total is

Expr1: [Total Proposal Value]+[Total Awarded Value]

However, where one of the two fields has an empty value the calculation does
not occur.
--
Lori

You can use either queries, forms or reports, depending on what you want to
do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so I'll have
to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries,

forms
or

reports for these types of actions.

Thanks for your help.
-- Lori

 

[Lori Burton]

That worked perfectly. Now, how can I add the CCur to work with the Nz?
--
Lori

Ah, that's because you can't use NULL in an expression. We don't know what
the value of NULL is, so we can't use it in a calculation. To make this
work, use the Nz function (NulltoZero) to convert the Null value to zero.

Expr1: Nz([Total Proposal Value])+Nz([Total Awarded Value])

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

One more snag,

The expression I used to get the total is

Expr1: [Total Proposal Value]+[Total Awarded Value]

However, where one of the two fields has an empty value the calculation does
not occur.
--
Lori

You can use either queries, forms or reports, depending on what you want to
do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so I'll have
to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two
subtotals.
I'm used to working in excel and am not sure whether to use queries, forms
or
reports for these types of actions.

Thanks for your help.
-- Lori

 

[John Vinson]

I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two subtotals.
I'm used to working in excel and am not sure whether to use queries, forms or
reports for these types of actions.

A Query would be appropriate. I'm not at all sure how your tables and
queries are constructed, but I would expect that you should be able to
get both totals in one query; if not, you should be able to create a
third query joining the two queries (by some appropriate field, which
again I can't see from this vantage point).

You can also do the total on a Form (suitable for onscreen) or on a
Report (for printing). Any of the three would work, which is best
depends on just what you want to do with the data. The main reason to
do it in a Query is that you can then base a Form, a Report, or both
on the query - and your calculation is already done for you.

John W. Vinson[MVP]

 

[Lori Burton]

Ignore this question, I figure this out. I'm so proud of myself?

Thanks for all your help!!
--
Lori

That worked perfectly. Now, how can I add the CCur to work with the Nz?
--
Lori

Ah, that's because you can't use NULL in an expression. We don't know what
the value of NULL is, so we can't use it in a calculation. To make this
work, use the Nz function (NulltoZero) to convert the Null value to zero.

Expr1: Nz([Total Proposal Value])+Nz([Total Awarded Value])

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

One more snag,

The expression I used to get the total is

Expr1: [Total Proposal Value]+[Total Awarded Value]

However, where one of the two fields has an empty value the calculation does
not occur.
--
Lori


:

You can use either queries, forms or reports, depending on what you want to
do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so I'll have
to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the query to
include a third column to calculate the the results of these two
subtotals.
I'm used to working in excel and am not sure whether to use queries, forms
or
reports for these types of actions.

Thanks for your help.
-- Lori

 

[Roger Carlson]

Um, where?

Expr1: CCur(Nz([Total Proposal Value]))+CCur(Nz([Total Awarded Value]))

Like that?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

That worked perfectly. Now, how can I add the CCur to work with the Nz?
--
Lori

Ah, that's because you can't use NULL in an expression. We don't know what
the value of NULL is, so we can't use it in a calculation. To make this
work, use the Nz function (NulltoZero) to convert the Null value to zero.

Expr1: Nz([Total Proposal Value])+Nz([Total Awarded Value])

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

One more snag,

The expression I used to get the total is

Expr1: [Total Proposal Value]+[Total Awarded Value]

However, where one of the two fields has an empty value the

calculation
does

not occur.
--
Lori


:

You can use either queries, forms or reports, depending on what you

want
to

do. I usually use a query.

You don't give us the details of your Totals (aggregate) query, so

I'll
have

to make one up. Suppose you have three fields (Field1, Field2 and Field3).
Assume further that you are Grouping on Field1 and summing fields 2 & 3.
Lastly, you want to create a field [Average] that is the average of the
summed fields. You would do something like this:

SELECT Field1, Sum(Field2) AS SumOfField2, Sum(Field3) AS SumOfField3,
Sum([field2])/Sum([field3]) AS Average FROM Table1
GROUP BY Field1;

Alternately, in the Query Builder you would put:
Average: Sum([field2])/Sum([field3])
in the Field Row and
Expression
in the Total: Row of you Totals query.

BTW, it's refreshing to note that you didn't ask how to store this value.
You should never (ie rarely) store calculated values.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have a query that groups data by the week showing the total of all
proposals written and a total of all jobs awarded. I want the

query
to

include a third column to calculate the the results of these two
subtotals.
I'm used to working in excel and am not sure whether to use

queries,
forms

or
reports for these types of actions.

Thanks for your help.
-- Lori