J
javablood
Is this what you are looking for:
Table = V-Trench_Influent
STATION_ID SAMPLE_DATE PARAMETER RESULT VALUE FLAG UNITS QC DETECTED SELECT MEDIUM LAB_ID Dilution_Factor LAB_NAME ANALYTICAL_METHOD
APT-INF 11/15/2007 Acetone 50U 50 U µg/l O No Yes GW Trinity EPA 8260B
APT-INF 11/15/2007 Benzene 5U 5 U µg/l O No Yes GW Trinity EPA 8260B
APT-INF 11/15/2007 Bromodichloromethane 5U 5 U µg/l O No Yes GW Trinity EPA 8260B
Table = Client_PAR
PARAM_ID PARAMETER PARAM_ABBR PABBR_NAME CAS_NUMBER F1_GROUP F1_GRPORDR Flag_1
12E 1,2-Dichloroethene (total) VH Volatile Halocarbons 540-59-0 VOA No
13B 1,3-Dichlorobenzene SB Base/Neutral Semivolatiles 541-73-1 VOA No
12B 1,2-Dichlorobenzene SB Base/Neutral Semivolatiles 95-50-1 VOA No
ACN Acrylonitrile VB Volatiles 107-13-1 VOA No
The pertinent data types are in previous response
--
javablood
Table = V-Trench_Influent
STATION_ID SAMPLE_DATE PARAMETER RESULT VALUE FLAG UNITS QC DETECTED SELECT MEDIUM LAB_ID Dilution_Factor LAB_NAME ANALYTICAL_METHOD
APT-INF 11/15/2007 Acetone 50U 50 U µg/l O No Yes GW Trinity EPA 8260B
APT-INF 11/15/2007 Benzene 5U 5 U µg/l O No Yes GW Trinity EPA 8260B
APT-INF 11/15/2007 Bromodichloromethane 5U 5 U µg/l O No Yes GW Trinity EPA 8260B
Table = Client_PAR
PARAM_ID PARAMETER PARAM_ABBR PABBR_NAME CAS_NUMBER F1_GROUP F1_GRPORDR Flag_1
12E 1,2-Dichloroethene (total) VH Volatile Halocarbons 540-59-0 VOA No
13B 1,3-Dichlorobenzene SB Base/Neutral Semivolatiles 541-73-1 VOA No
12B 1,2-Dichlorobenzene SB Base/Neutral Semivolatiles 95-50-1 VOA No
ACN Acrylonitrile VB Volatiles 107-13-1 VOA No
The pertinent data types are in previous response
--
javablood
KARL DEWEY said: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.
:
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!
