D
Duane Hookom
Is the field ActionTeam ever Null? If so, try:
TRANSFORM Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & Nz(ActionTeam,-1)) AS Entry
--
Duane Hookom
MS Access MVP
TRANSFORM Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & Nz(ActionTeam,-1)) AS Entry
--
Duane Hookom
MS Access MVP
tom at arundel said:Is ActionTeam a numeric field in both your crosstab query and in
qryGateReviewSchedule?
-- I think it is -- but I am not sure how to check. I only dimension it
once in the original table then don't change it anywhere else that I am
aware
of.
What do you get if you open the debug window (press ctrl+g) and enter
? Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3")
I get just what I expect, a list from ProjectName for ActionTeam 3
separated
by commas -- Bulk Belts, Project 25, test project 5a
What is your complete SQL view of the crosstab query?
TRANSFORM Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3") AS Entry
SELECT Format([Date:NextGate],"yyyy") AS [Year],
qryGateReviewSchedule.ActionTeam
FROM qryGateReviewSchedule
GROUP BY Format([Date:NextGate],"yyyy"), qryGateReviewSchedule.ActionTeam
PIVOT Format([Date:NextGate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
and, just in case it helps, the SQL view of qryGateReviewSchedule
SELECT tblActionTeams.ActionTeamName, tblProjects.ProjectName,
tblSalesProjections.Y1Sales, tblProjects.NextGate,
tblProjects.[Date:NextGate], tblGates.Description,
tblProjects.ProjectType,
tblProjects.ActionTeam
FROM (tblGates RIGHT JOIN (tblActionTeams RIGHT JOIN tblProjects ON
tblActionTeams.ActionTeamID = tblProjects.ActionTeam) ON tblGates.GatesID
=
tblProjects.NextGate) INNER JOIN tblSalesProjections ON
tblProjects.ProjectNumber = tblSalesProjections.SalesID
WHERE (((tblProjects.ProjectType)=2 Or (tblProjects.ProjectType)=3 Or
(tblProjects.ProjectType)=4 Or (tblProjects.ProjectType)=7));
Duane -- thanks a lot.
Duane Hookom said:Is ActionTeam a numeric field in both your crosstab query and in
qryGateReviewSchedule?
What do you get if you open the debug window (press ctrl+g) and enter
? Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3")
What is your complete SQL view of the crosstab query?
--
Duane Hookom
MS Access MVP
--
message
Duane --
Well, I am still trying to make this work to create the calendar
report.
After reading your Crosstab and Calendar reports, I went back and stuck
my
nose in more books to learn some more. I still come up with an error I
can't
figure out.
In my crosstab query, I have the following entry for the field of the
value.
Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & [ActionTeam])
The fields for the rows are
Year: Format([Date:NextGate],"yyyy")
and
ActionTeam
The column heading is
Format([Date:NextGate],"mmm")
Each time I run it, I get a run-time error with this message: Syntax
error
(missing operator) in query expression 'ActionTeam='.
The expression:
Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & 3)
Runs just fine. 3 is a value for ActionTeam. (ActionTeam is declared
as
a
long integer.)
So, I feel like this is probably an "Oh Dah" problem, but I can't seem
to
spot anything.
Any ideas?
:
You can find a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Did you ever look at my Crosstab or Calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4?
--
Duane Hookom
MS Access MVP
--
message
Now I discovered another wrinkle -- some of the crosstabs actually
can
contain 4 to 5 items -- not a single item. Obviously using the
First
or
Last
functions will only get one. I am not even sure how to approach
pulling
multiple items in a cross tab. How do you even think about this
type
of
issue?
:
Is [Sales - Y1] numeric? In the datasheet view of the report's
record
source, is the field right or left aligned?
--
Duane Hookom
MS Access MVP
message
Thank Duane. This works in placing the formating in the query,
but
I
can't
seem to get it to work to format in a report based upon that
query.
I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.
Any other ideas? I am always game to learn and try something.
:
You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))
--
Duane Hookom
MS Access MVP
in
message
This is great and it works for me also. Just one more
question --
can
you
format the values within the crosstab? For my crosstab query,
I
use
the
expression
Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1]
&
Chr(13)
&
Chr(10) & Left([Project Name],20))
to find the value for each row/column intersection. Now, when
I
create
the
report, I want to format the [Sales - Y1] fied to currency
with
no
decimal
places.
Any ideas?
thanks in advance
:
You don't have to use count or sum. You can use First or Max
or
Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]
--
Duane Hookom
MS Access MVP
--
message
As detailed in my previous message I don't think a crosstab
query
will
work
and wanted clarification of this and any advice on how I
could
maybe
achieve
it? A crosstab query does produce the format of names in
rows
and
dates
as
column headings but the trouble is the text to be displayed
is
not a
'count'
or 'sum' of anything. I literally just want to display the
contents
of
a
few
fields as per my previous example, showing what activity
each
person
is
doing
and a dealer number if they were on an audit, on a daily
basis
per
week
range.
:
If the calendar reports don't work for you then try look
at
the
crosstab
reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
--
Duane Hookom
MS Access MVP
--
message
Hi, I would like a report which prints out a weekly
itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but
they
don't
quite
achieve what I want and not too sure how to replicate
something
similar. I
want a layout like below:
Mon Tues Wed Thurs
Frid
3 4 5
6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234
4567
Jane Doe Audit Audit Audit Audit
Audit
9876 9876 9876 9876
9876
Bilbo Baggins Holiday Holiday Holiday Holiday
Holiday
I am new to crosstab queries and tried to do one of
these
to
diplay
the
info
but couldn't get it to work as the details I want
displayed
(audit,
dealer
no, etc) are the values of fields rather than a
calculation.
Can
anyone
advise me how to produce a simple report displaying the
data
in
this
format?
I will add a start and end date filter to a form to
display
which
week
I
want
printed, I am just not sure how to get a report in this
layout?
Thanks in advance for any help.
Sue