calculate new column in a crosstab

W

water

Hi

I have a database that hold water quality data in the following format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg mg/L
I added a field and used the expression builder to enter the calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
 
D

Duane Hookom

Not sure if this will work but try create a row heading with the expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
 
W

water

Thank you. Well I did not get any error message but nothing happened and the
column did not show up when I tried to view the data.
If I try the same thing with the Group By option I get the following
error:Cannot have aggregate function in GROUP BY clause <clause>.

Here is the SQL view of the query in case it helps:
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date
FROM [FINAL DATABASE]
WHERE ((([FINAL DATABASE].Parameter)="Ca mg/l" Or ([FINAL
DATABASE].Parameter)="Mg mg/l"))
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter;



Duane Hookom said:
Not sure if this will work but try create a row heading with the expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
--
Duane Hookom
MS Access MVP
--

water said:
Hi

I have a database that hold water quality data in the following format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg mg/L
I added a field and used the expression builder to enter the calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
Click to expand...
Click to expand...
 
D

Duane Hookom

That wasn't what I expected you to create

TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l" Or "Mg mg/l");

--
Duane Hookom
MS Access MVP


water said:
Thank you. Well I did not get any error message but nothing happened and
the
column did not show up when I tried to view the data.
If I try the same thing with the Group By option I get the following
error:Cannot have aggregate function in GROUP BY clause <clause>.

Here is the SQL view of the query in case it helps:
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date
FROM [FINAL DATABASE]
WHERE ((([FINAL DATABASE].Parameter)="Ca mg/l" Or ([FINAL
DATABASE].Parameter)="Mg mg/l"))
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter;



Duane Hookom said:
Not sure if this will work but try create a row heading with the
expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
--
Duane Hookom
MS Access MVP
--

water said:
Hi

I have a database that hold water quality data in the following format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the
hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg mg/L
I added a field and used the expression builder to enter the
calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
W

water

Ok, I tried the query and it definitely produced a result but not the ones I
need. This is what I got when I ran the query you suggested:
ID Date Hardness "Ca mg/l"" Or ""Mg mg/l"
BC-3 6/14/1994 1.22
BC-3 8/3/1994 0.74
BC-3 9/21/1994 1.05
BC-3 8/24/1995 0.95

There is nothing in the "Ca mg/L" or "Mg mg/L" column which needs to be two
columns anyway and not one. The Hardness value does not correspond to the sum
of the Ca and Mg for the given date for the given station when I looked it up
individually. What I want is the table to show the Ca mg/L values for each
station and each date and the Mg mg/L for the same station and date and then
perform the computation for that date and station. So it should only involve
two values per station and date. Does that make sense?

Duane Hookom said:
That wasn't what I expected you to create

TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l" Or "Mg mg/l");

--
Duane Hookom
MS Access MVP


water said:
Thank you. Well I did not get any error message but nothing happened and
the
column did not show up when I tried to view the data.
If I try the same thing with the Group By option I get the following
error:Cannot have aggregate function in GROUP BY clause <clause>.

Here is the SQL view of the query in case it helps:
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date
FROM [FINAL DATABASE]
WHERE ((([FINAL DATABASE].Parameter)="Ca mg/l" Or ([FINAL
DATABASE].Parameter)="Mg mg/l"))
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter;



Duane Hookom said:
Not sure if this will work but try create a row heading with the
expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
--
Duane Hookom
MS Access MVP
--

Hi

I have a database that hold water quality data in the following format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the
hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg mg/L
I added a field and used the expression builder to enter the
calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Water,
Please try this SQL. I replaced an "or" with a comma. If it doesn't work
then paste your SQL view into all replies.


TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l", "Mg mg/l");

--
Duane Hookom
MS Access MVP


water said:
Ok, I tried the query and it definitely produced a result but not the ones
I
need. This is what I got when I ran the query you suggested:
ID Date Hardness "Ca mg/l"" Or ""Mg mg/l"
BC-3 6/14/1994 1.22
BC-3 8/3/1994 0.74
BC-3 9/21/1994 1.05
BC-3 8/24/1995 0.95

There is nothing in the "Ca mg/L" or "Mg mg/L" column which needs to be
two
columns anyway and not one. The Hardness value does not correspond to the
sum
of the Ca and Mg for the given date for the given station when I looked it
up
individually. What I want is the table to show the Ca mg/L values for each
station and each date and the Mg mg/L for the same station and date and
then
perform the computation for that date and station. So it should only
involve
two values per station and date. Does that make sense?

Duane Hookom said:
That wasn't what I expected you to create

TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l" Or "Mg mg/l");

--
Duane Hookom
MS Access MVP


water said:
Thank you. Well I did not get any error message but nothing happened
and
the
column did not show up when I tried to view the data.
If I try the same thing with the Group By option I get the following
error:Cannot have aggregate function in GROUP BY clause <clause>.

Here is the SQL view of the query in case it helps:
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date
FROM [FINAL DATABASE]
WHERE ((([FINAL DATABASE].Parameter)="Ca mg/l" Or ([FINAL
DATABASE].Parameter)="Mg mg/l"))
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter;



:

Not sure if this will work but try create a row heading with the
expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
--
Duane Hookom
MS Access MVP
--

Hi

I have a database that hold water quality data in the following
format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the
hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg
mg/L
I added a field and used the expression builder to enter the
calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
W

water

Hi Duane

we're getting there:
ID Date Hardness Ca mg/l Mg mg/l
BC-3 8/3/1994 0.74 17 4.59999990463257
BC-3 9/21/1994 1.05 23 7.40000009536743
BC-3 8/24/1995 0.95 20 6.80000019073486
BFC 4/28/1997 0.55 9 1.29999995231628
BFC 6/30/1997 0.88 14 2.5

The format is good but the values in Hardness are not computed correctly. I
don't quite understand what actually goes into them. For 8/3/1994 the value
should be 61.4 instead of 0.74.

Any thoughts ?

Thanks
Duane Hookom said:
Water,
Please try this SQL. I replaced an "or" with a comma. If it doesn't work
then paste your SQL view into all replies.


TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l", "Mg mg/l");

--
Duane Hookom
MS Access MVP


water said:
Ok, I tried the query and it definitely produced a result but not the ones
I
need. This is what I got when I ran the query you suggested:
ID Date Hardness "Ca mg/l"" Or ""Mg mg/l"
BC-3 6/14/1994 1.22
BC-3 8/3/1994 0.74
BC-3 9/21/1994 1.05
BC-3 8/24/1995 0.95

There is nothing in the "Ca mg/L" or "Mg mg/L" column which needs to be
two
columns anyway and not one. The Hardness value does not correspond to the
sum
of the Ca and Mg for the given date for the given station when I looked it
up
individually. What I want is the table to show the Ca mg/L values for each
station and each date and the Mg mg/L for the same station and date and
then
perform the computation for that date and station. So it should only
involve
two values per station and date. Does that make sense?

Duane Hookom said:
That wasn't what I expected you to create

TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l" Or "Mg mg/l");

--
Duane Hookom
MS Access MVP


Thank you. Well I did not get any error message but nothing happened
and
the
column did not show up when I tried to view the data.
If I try the same thing with the Group By option I get the following
error:Cannot have aggregate function in GROUP BY clause <clause>.

Here is the SQL view of the query in case it helps:
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date
FROM [FINAL DATABASE]
WHERE ((([FINAL DATABASE].Parameter)="Ca mg/l" Or ([FINAL
DATABASE].Parameter)="Mg mg/l"))
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter;



:

Not sure if this will work but try create a row heading with the
expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
--
Duane Hookom
MS Access MVP
--

Hi

I have a database that hold water quality data in the following
format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the
hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg
mg/L
I added a field and used the expression builder to enter the
calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
W

water

Hi Duane,

Ignore my last post. I modified the SQL to
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date, Sum(Abs([Parameter]="Ca
mg/L")*[Value]*2.497)+Sum(Abs([Parameter]="Mg mg/L")*[Value]*4.118) AS
Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter In ("Ca mg/l", "Mg mg/l");


and it works :)

Thanks a lot for your help

Duane Hookom said:
Water,
Please try this SQL. I replaced an "or" with a comma. If it doesn't work
then paste your SQL view into all replies.


TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l", "Mg mg/l");

--
Duane Hookom
MS Access MVP


water said:
Ok, I tried the query and it definitely produced a result but not the ones
I
need. This is what I got when I ran the query you suggested:
ID Date Hardness "Ca mg/l"" Or ""Mg mg/l"
BC-3 6/14/1994 1.22
BC-3 8/3/1994 0.74
BC-3 9/21/1994 1.05
BC-3 8/24/1995 0.95

There is nothing in the "Ca mg/L" or "Mg mg/L" column which needs to be
two
columns anyway and not one. The Hardness value does not correspond to the
sum
of the Ca and Mg for the given date for the given station when I looked it
up
individually. What I want is the table to show the Ca mg/L values for each
station and each date and the Mg mg/L for the same station and date and
then
perform the computation for that date and station. So it should only
involve
two values per station and date. Does that make sense?

Duane Hookom said:
That wasn't what I expected you to create

TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date,
Avg(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Avg(Abs(Parameter="Mg mg/L") * Value *4.118 ) as Hardness
FROM [FINAL DATABASE]
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter IN ("Ca mg/l" Or "Mg mg/l");

--
Duane Hookom
MS Access MVP


Thank you. Well I did not get any error message but nothing happened
and
the
column did not show up when I tried to view the data.
If I try the same thing with the Group By option I get the following
error:Cannot have aggregate function in GROUP BY clause <clause>.

Here is the SQL view of the query in case it helps:
TRANSFORM Avg([FINAL DATABASE].Value) AS AvgOfValue
SELECT [FINAL DATABASE].ID, [FINAL DATABASE].Date
FROM [FINAL DATABASE]
WHERE ((([FINAL DATABASE].Parameter)="Ca mg/l" Or ([FINAL
DATABASE].Parameter)="Mg mg/l"))
GROUP BY [FINAL DATABASE].ID, [FINAL DATABASE].Date
PIVOT [FINAL DATABASE].Parameter;



:

Not sure if this will work but try create a row heading with the
expression:
Hardness: Sum(Abs(Parameter="Ca mg/L") * Value * 2.497 ) +
Sum(Abs(Parameter="Mg mg/L") * Value *4.118 )

Change the Totals: from Group By or Sum to "Expression"
--
Duane Hookom
MS Access MVP
--

Hi

I have a database that hold water quality data in the following
format:
ID Date Parameter Value
CR1 01/01/2000 Ca mg/L 0.005

CR1 01/01/2000 Mg mg/L 0.001

I created a crosstab that shows the data in the following format
ID Date Ca mg/L Mg mg/L
CR1 01/01/2000 0.005 0.001

I want to create a new column in my query that will compute the
hardness
based upon the following expression 2.497 * Ca mg/L + 4.118 * Mg
mg/L
I added a field and used the expression builder to enter the
calculation:
Field: Hardness: 2.497 * [Ca mg/L] + 4.118 * [Mg mg/L]
Table:
Total: Expression
Crosstab: Row Heading
Sort:
Criteria:

The query won't work it says that "Ca mg/L" is not a valid field...

Can anyone help ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

crosstab query 2
Crosstab column data count 6
Crosstab Query and Specific Date Ranges 6
Problem with Query Calculation 5
Crosstab Query based on Ordinal Values 6
Crosstab: Sum of Columns over Row 2
Calculated fields 2
update crosstab 0

Top