Grouping similar items from crosstab query

  • Thread starter Thread starter javablood
  • Start date Start date
J

javablood

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
 
Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.
 
Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
 
Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


javablood said:
Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


KARL DEWEY said:
Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.
Click to expand...
Click to expand...
 
Thanks Karl. Any idea about the text and values questions?
--
javablood


KARL DEWEY said:
Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


javablood said:
Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


KARL DEWEY said:
Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

javablood said:
Thanks Karl. Any idea about the text and values questions?
--
javablood


KARL DEWEY said:
Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


javablood said:
Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


KARL DEWEY said:
What makes you think they become text?

You should still sum in a footer. Did you try it?

javablood said:
Thanks Karl. Any idea about the text and values questions?
--
javablood


KARL DEWEY said:
Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

javablood said:
Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


KARL DEWEY said:
I noticed that when I put this query into the report the values become text.
Click to expand...

What makes you think they become text?

You should still sum in a footer. Did you try it?

javablood said:
Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
I typed it correctly (as you showed it) and it did not work.
--
javablood


KARL DEWEY said:
If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

javablood said:
Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


KARL DEWEY said:
I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

javablood said:
I typed it correctly (as you showed it) and it did not work.
--
javablood


KARL DEWEY said:
If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

javablood said:
Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


KARL DEWEY said:
Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

javablood said:
I typed it correctly (as you showed it) and it did not work.
--
javablood


KARL DEWEY said:
If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Would there be any reason that one cannot use sum in the report header?
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

javablood said:
I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


KARL DEWEY said:
Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

javablood said:
I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
No go on that either. I was looking for something like that yesterday but
could not find CDbl. Oh, I meant report footer not header if that makes a
different.

I changed the query to have zeros if the "U" was present and the value if it
was not and ran the report. The sum worked in the footer (page and report).
But not with the combined text and values. Then when I was messing with the
report and ran it, the report was showing numbers not even in the query!!
Messed up! :-(

javablood


KARL DEWEY said:
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

javablood said:
I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


KARL DEWEY said:
Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
No go on that either. I was looking for something like CDbl but could not
find it.

Oh, I meant report footer not header if that makes a differnece.

I changed the query to show zeros when "U" was present and values if it
wasn't. I ran the report and sum worked in the footers (page and report).
Then when i was adjusting something in the report, it starte showing values
that are not even in the query!? Messed up! :-(
--
javablood


KARL DEWEY said:
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

javablood said:
I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


KARL DEWEY said:
Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Karl,

Sorry about that double reply but the system said my first one did not go
through. Anyway, I got the sum to work by =sum(VAL([1/8/2008])) and even in
the report footer. However, it sums all the records not just the ones
without the "U". I need an iff statement to ignore the fields that contain
"U". Any ideas.
--
javablood


KARL DEWEY said:
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

javablood said:
I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


KARL DEWEY said:
Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Try this --
=Sum(Val(IIf([Flag]="U",[1/8/2008], 0)))

javablood said:
Karl,

Sorry about that double reply but the system said my first one did not go
through. Anyway, I got the sum to work by =sum(VAL([1/8/2008])) and even in
the report footer. However, it sums all the records not just the ones
without the "U". I need an iff statement to ignore the fields that contain
"U". Any ideas.
--
javablood


KARL DEWEY said:
Would there be any reason that one cannot use sum in the report header?
Click to expand...
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

javablood said:
I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


:

Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
System said it was bust so I am sending reply again.

I tried: =Sum(Val(IIf([1/8/2008] Like "U",[1/8/2008],0))) because [Flag] is
not in the resultant query (it is only in orignal table). The result was
zero (0)! Not sure why that is happening. I am wondering if I need a
crosstab query that will have two columns for the same date (one wiht the
flagged data and one with only the values) that can be combinded in the
report. Is that possible? I did not think I would have som much trouble
with this.


javablood


KARL DEWEY said:
Try this --
=Sum(Val(IIf([Flag]="U",[1/8/2008], 0)))

javablood said:
Karl,

Sorry about that double reply but the system said my first one did not go
through. Anyway, I got the sum to work by =sum(VAL([1/8/2008])) and even in
the report footer. However, it sums all the records not just the ones
without the "U". I need an iff statement to ignore the fields that contain
"U". Any ideas.
--
javablood


KARL DEWEY said:
Would there be any reason that one cannot use sum in the report header?
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

:

I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


:

Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Am I just confused or do you not know how to use the IIF function?
I was under the impression that your [Flag] field may have value of "U" and
that field [1/8/2008] contained number you wanted to sum.

Post sample of your orignal data with field field names and datatype. Post
what you want the crosstab query resoulst to look like based on your sample
data.

javablood said:
System said it was bust so I am sending reply again.

I tried: =Sum(Val(IIf([1/8/2008] Like "U",[1/8/2008],0))) because [Flag] is
not in the resultant query (it is only in orignal table). The result was
zero (0)! Not sure why that is happening. I am wondering if I need a
crosstab query that will have two columns for the same date (one wiht the
flagged data and one with only the values) that can be combinded in the
report. Is that possible? I did not think I would have som much trouble
with this.


javablood


KARL DEWEY said:
Try this --
=Sum(Val(IIf([Flag]="U",[1/8/2008], 0)))

javablood said:
Karl,

Sorry about that double reply but the system said my first one did not go
through. Anyway, I got the sum to work by =sum(VAL([1/8/2008])) and even in
the report footer. However, it sums all the records not just the ones
without the "U". I need an iff statement to ignore the fields that contain
"U". Any ideas.
--
javablood


:

Would there be any reason that one cannot use sum in the report header?
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

:

I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


:

Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Easy! I told you I am NOT sophisticated with Access and am trying to learn
more form the likes of you. I DO know the basics of how to use the IIF
function!

I have a crosstab query (see response to you first response) that pulls:

Parameter (Text)
F1_GRPORDR (Number)
Sample_Date (Date/Time)
Value (Number) (the result in this field depends on whether a "U" exists in
the [Flag] (Text) field such that either a number or a number &"U" are in the
final query)

from two tables and creates this:
Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U 10 20 30


The report is based on this query result and I am trying to sum the values
per date for the chemicals (35 total) for those chemicals that do not have
the "U" in the resultant query. Thus, as you see the [Flag] field is not in
the resultant query and I would have to use "Like" to differentiate between
those values with a "U" and those without. I think the result ends up being
a text and hence have to use the VAL to convert text to value.

Maybe I should just include the Sum in the query and use that in the report!?

I do appreciate your help and understand that while I am more familiar with
my tables and what I want the report to do, you are more familiar with the
intricacies of Access.

--
javablood


KARL DEWEY said:
Am I just confused or do you not know how to use the IIF function?
I was under the impression that your [Flag] field may have value of "U" and
that field [1/8/2008] contained number you wanted to sum.

Post sample of your orignal data with field field names and datatype. Post
what you want the crosstab query resoulst to look like based on your sample
data.

javablood said:
System said it was bust so I am sending reply again.

I tried: =Sum(Val(IIf([1/8/2008] Like "U",[1/8/2008],0))) because [Flag] is
not in the resultant query (it is only in orignal table). The result was
zero (0)! Not sure why that is happening. I am wondering if I need a
crosstab query that will have two columns for the same date (one wiht the
flagged data and one with only the values) that can be combinded in the
report. Is that possible? I did not think I would have som much trouble
with this.


javablood


KARL DEWEY said:
Try this --
=Sum(Val(IIf([Flag]="U",[1/8/2008], 0)))

:

Karl,

Sorry about that double reply but the system said my first one did not go
through. Anyway, I got the sum to work by =sum(VAL([1/8/2008])) and even in
the report footer. However, it sums all the records not just the ones
without the "U". I need an iff statement to ignore the fields that contain
"U". Any ideas.
--
javablood


:

Would there be any reason that one cannot use sum in the report header?
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

:

I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


:

Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Post sample of your orignal data (before crosstab) with field field names
and datatype.

javablood said:
Easy! I told you I am NOT sophisticated with Access and am trying to learn
more form the likes of you. I DO know the basics of how to use the IIF
function!

I have a crosstab query (see response to you first response) that pulls:

Parameter (Text)
F1_GRPORDR (Number)
Sample_Date (Date/Time)
Value (Number) (the result in this field depends on whether a "U" exists in
the [Flag] (Text) field such that either a number or a number &"U" are in the
final query)

from two tables and creates this:
Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U 10 20 30


The report is based on this query result and I am trying to sum the values
per date for the chemicals (35 total) for those chemicals that do not have
the "U" in the resultant query. Thus, as you see the [Flag] field is not in
the resultant query and I would have to use "Like" to differentiate between
those values with a "U" and those without. I think the result ends up being
a text and hence have to use the VAL to convert text to value.

Maybe I should just include the Sum in the query and use that in the report!?

I do appreciate your help and understand that while I am more familiar with
my tables and what I want the report to do, you are more familiar with the
intricacies of Access.

--
javablood


KARL DEWEY said:
Am I just confused or do you not know how to use the IIF function?
I was under the impression that your [Flag] field may have value of "U" and
that field [1/8/2008] contained number you wanted to sum.

Post sample of your orignal data with field field names and datatype. Post
what you want the crosstab query resoulst to look like based on your sample
data.

javablood said:
System said it was bust so I am sending reply again.

I tried: =Sum(Val(IIf([1/8/2008] Like "U",[1/8/2008],0))) because [Flag] is
not in the resultant query (it is only in orignal table). The result was
zero (0)! Not sure why that is happening. I am wondering if I need a
crosstab query that will have two columns for the same date (one wiht the
flagged data and one with only the values) that can be combinded in the
report. Is that possible? I did not think I would have som much trouble
with this.


javablood


:

Try this --
=Sum(Val(IIf([Flag]="U",[1/8/2008], 0)))

:

Karl,

Sorry about that double reply but the system said my first one did not go
through. Anyway, I got the sum to work by =sum(VAL([1/8/2008])) and even in
the report footer. However, it sums all the records not just the ones
without the "U". I need an iff statement to ignore the fields that contain
"U". Any ideas.
--
javablood


:

Would there be any reason that one cannot use sum in the report header?
I do not know about that.


Try =sum(CDbl([1/2/2008]))

This converts text to a number.

:

I run into the error "Data type mismatch in criteria expression" when I try
to run the query.

I also got this error when I tried to use the sum in the report header and
not sure why. Because my list of chemicals takes up more than one page it is
better if the sum is at the bottom of the list. Would there be any reason
that one cannot use sum in the report header?

Always something!
--
javablood


:

Ok, back up a little. Use you crosstab query in a totals query just to sum
the columns and not group on any other fields.

See if it will sum.

:

I typed it correctly (as you showed it) and it did not work.
--
javablood


:

If you typed it as posted --- =sum[(1/2/2008]) then it will not work.
Try =sum([1/2/2008])

:

Yes I have. I created a box in the footer with the control source:
=sum[(1/2/2008]) for each respective date and I get an "#error" in the
resulting report. I am not very sophisticated in Access so thank you for
bearing with me. But I learn much from you all. :-)
--
javablood


:

I noticed that when I put this query into the report the values become text.

What makes you think they become text?

You should still sum in a footer. Did you try it?

:

Thanks Karl. Any idea about the text and values questions?
--
javablood


:

Drop the fields that are not needed from the GROUP BY to look like this --
TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;


:

Karl,

Thank you for your response. Here is the SQL:

TRANSFORM First(IIf([Flag]="U",[Value] & " " & [Flag],[Value])) AS RptValue
SELECT [V-Trench_Influent].PARAMETER, Client_PAR.F1_GRPORDR
FROM [V-Trench_Influent] INNER JOIN Client_PAR ON
[V-Trench_Influent].PARAMETER = Client_PAR.PARAMETER
WHERE ((([V-Trench_Influent].SAMPLE_DATE)>#1/1/2008#) AND
((Client_PAR.F1_GROUP)="VOA"))
GROUP BY [V-Trench_Influent].STATION_ID, [V-Trench_Influent].PARAMETER,
[V-Trench_Influent].RESULT, [V-Trench_Influent].VALUE,
[V-Trench_Influent].FLAG, [V-Trench_Influent].DETECTED,
Client_PAR.F1_GRPORDR, Client_PAR.F1_GROUP, [V-Trench_Influent].FLAG
ORDER BY Client_PAR.F1_GRPORDR
PIVOT [V-Trench_Influent].SAMPLE_DATE;

I pull the data from either text or value fields depending on whether a
chemical was detected because I want to be able to sum the values in the
report per sampling event (date). However, I noticed that when I put this
query into the report the values become text. Is there a way to keep the
values as values so I may sum the values?

TIA!
--
javablood


:

Yes, post the SQL of your crosstab query.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

:

I have a crosstab query that has chemicals in the rows, dates in the columns,
and concentrations (value). When I create a report I am able to line up my
chemicals with my values (thanks to previous discussion group
questions/answers) with the exception of chemicals with values that are
different according to date. It would look something like this:

Date1 Date2 Date3 Date4
Chem1 5U 5U 5U 5U
Chem2 5U
10
20
30

Is there a way to get the values for Chem2 on the same line?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top