Thanks for your help Duane. I have tried your suggestion and several
others
you've posted in the Newgroup, but I'm still having problems and am
very
frustrated.
Here is my latest SQL:
PARAMETERS [Forms]![Pick_Ind_Report]![Begin] DateTime,
[Forms]![Pick_Ind_Report]![End] DateTime;
TRANSFORM Sum(IndividualD1TotsQuery1.[Day 1 Supplier Performance]) AS
[SumOfDay 1 Supplier Performance]
SELECT IndividualD1TotsQuery1.RCDLOC
FROM IndividualD1TotsQuery1
GROUP BY IndividualD1TotsQuery1.RCDLOC
PIVOT "Mth" & DateDiff("m",[NewDate],Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth10","Mth11","Mth12");
The reason I declared paramenter in the crosstab is because the
underlying
query that the crosstab is using contains Between
[Forms]![Pick_Ind_Report]![Begin] And [Forms]![Pick_Ind_Report]![End]
in
the
NewDate field.
The current SQL of the crosstab query shows the 12 months, even if a
user
has typed begin and end date of a 3 month span. If I delete the column
heading property, the query returns only the months between begin and
end
date, which is what I'd like, but then I can't create a report based of
the
crosstab query. What I'd like to happen is for a user to open a form,
type a
begin and end date, like 1/1/05 - 3/1/05 and click a button to run a
report
that shows only those fields like Jan/05, Feb/05, Mar/05. I'm going
nuts
trying to figure this out. I'll keep pluggin' away, but if you can
tell
me
what I'm doing wrong I'd apprecate it. Thanks.
:
You will not create columns like Jan, Feb, Mar,... As I suggested
earlier,
set your column headings property to:
Column Headings:"Mth0", "Mth1", "Mth2",...
You might not need your parameters if your columns will always be
relative
to the current date. There is no place in your crosstab where you
reference
the controls on your form.
--
Duane Hookom
MS Access MVP
--
Thanks Duane -
PARAMETERS [Forms]![Pick_Ind_Report]![Begin] DateTime,
[Forms]![Pick_Ind_Report]![End] DateTime;
TRANSFORM Sum(IndividualD1TotsQuery1.[Day 1 Supplier Performance])
AS
[SumOfDay 1 Supplier Performance]
SELECT IndividualD1TotsQuery1.RCDLOC
FROM IndividualD1TotsQuery1
GROUP BY IndividualD1TotsQuery1.RCDLOC
PIVOT "Mth" & DateDiff("m",[NewDate],Date()) In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
:
Please provide your current SQL view.
--
Duane Hookom
MS Access MVP
Thanks Duane,
I've got a good start to my crosstab query now. I've declared my
parameters
and tried using your suggestion for ColHead:, but when I use that
expression
or something similiar, the data in all of the months comes up
null.
I
have
RL as my RowHeading (groupedby), Perform as my value (sum) and
I'm
trying
your expression as my column heading (groupedby). Any idea what
I'm
doing
wrong? Thanks much -
:
I would create a crosstab query and base your report on it.
Rather
than
placing the "raw" dates as column headings, I would use an
expression
like:
ColHead: "Mth" & DateDiff("m", OnDate, Date())
This would create columns with names like Mth0, Mth1, Mth2,...
where
Mth0
is
the current month.
You will need to figure out how to get the most recent Stretch
and
Normal
after you have your crosstab created.
--
Duane Hookom
MS Access MVP
I am having problems setting up a report. I've used the
instructions
in
article Q210044 to Print Labels on the Left Margin of a
Report,
which
worked
great for a slightly different report. My new report is
enough
different
that I'm having problems. I've tried to vary the instructions
above,
but
with no luck. Below is list of my fields and a description of
how
my
report
needs to look.
query fields:
RL, OnDate, BT, Normal, Stretch, Perform
CH, 1/1/05, 97, 98, 97, 87%
CH, 2/1/05, 99, 98, 100, 92%
CH, 3/1/05, 100, 98, 99, 97%
MC, 1/1/05, 100 98, 100, 99%
MC, 2/1/05, 99, 97, 100, 98%
MC, 3/1/05, 100, 98, 99, 98%
Report Needs:
RL, BT, Stretch, Normal, Jan/05, Feb/05, Mar/05
CH, 100, 98, 99, 87%, 92%, 97%
MC, 100, 98, 99, 99%, 98%, 98%
(BT, Stretch, Normal will be the most recent)
Thank you so much for your help,
Alex
