About filtering the records if the shift *control* (the control belongs
to
the form, a field belongs to the table, even if both have the same name,
it
is important, in some cases, to differentiate the control from the field)
is
left empty, you can test the field against the control with something
like:
WHERE field LIKE FORMS!formName!control & "*"
About DMin. Try in the Debug Immediate Window:
? DMin( "Week", "my_query_Name" )
that should work, as long as the query is really a saved query (not an
SQL
statement) and does not ask for a parameter. Note that the first argument
of
DMin is also a string (ie, it is NOT: DMin( week, "my_query_name" ). )
You also have to use the right field name/alias as returned by the query,
NOT the caption. In other words, if your saved query is:
SELECT ... someExpression AS weekAAA, ...
then, use DMIN("weekAAA", ... )
If you don't supply an alias, explicitly, Jet supplies one for you, like
Expr0001, as example. (Not a very good idea).
Hoping it may help,
Vanderghast, Access MVP
Alex said:
I've tried everything I can to get this to work. My datasource is a
query,
not a table, if that makes a difference. The field 'week' is an
expression
in the data source query. So I've tried the following, amongst others
and
keep getting error messages. What am I doing wrong?
PIVOT ( Week - DMIN("[Week]", "my_query_Name") + 1) & "_" & Shft
Can I ask you one more question too, then I promise not to bother you
again;
you've been so much help. I use a form to popluate my parameters in my
data
source query, I'm using Like to return all values in the Shft field if
a
user
leaves the shft control on my pick form blank. It works fine in my
data
source query, but if the pick form shift field is left blank, the
crosstab
query returns no records. Is there a way I can add that parameter in
my
crosstab parameter window to return all records if shift is left blank?
Thanks again for your help.
:
Subtract DMin("[Week]", "yourTableHere") as:
PIVOT ( Week - DMIN("[Week]", "yourTableHere") + 1) & "_" & Shft
Vanderghast, Access MVP
Yes, thank you so much. I'm hoping you can help me with something
else
too.
My chart needs to capture week 1, week 2, week 3, week 4, regardless
of
the
'real' week. For example, although my query shows weeks 24, 25, 26
&
27,
I
need the chart to show weeks 1, 2, 3 & 4. Do you know how I can
write
a
query expression to number the 'real' weeks? Thanks again.
:
Change the PIVOT clause to make a string with concatenation of the
week
and
the shift, maybe something such as:
PIVOT Week & "_" & Shft
(instead of PIVOT Shft )
Hoping it may help,
Vanderghast, Access MVP
I have the following crosstab query that is populating a chart.
However,
the
chart is not showing the data correctly. The chart is a stacked
column
chart
showing the Def Qnty sum for each defect, which is correct.
There
are
four
shifts so I am currently getting four bars; one for each shift.
What I
need
is four bars for each week, so if there are 4 weeks of data in my
query
and I
have four shifts, I should get 16 bars. Is there a way to revise
my
query
to
show the data correctly? Thanks so much.
TRANSFORM Sum(qry_Defects_by_Oper_Shift_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT qry_Defects_by_Oper_Shift_Chart.Defect,
qry_Defects_by_Oper_Shift_Chart.Week AS [Bi-week]
FROM qry_Defects_by_Oper_Shift_Chart
GROUP BY qry_Defects_by_Oper_Shift_Chart.Defect,
qry_Defects_by_Oper_Shift_Chart.Week
PIVOT qry_Defects_by_Oper_Shift_Chart.Shft;
