Calendar Report

[Guest]

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

 

[Duane Hookom]

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.

 

[Guest]

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.

 

[Duane Hookom]

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]

 

[PC Datasheet]

I have a scheduling calendar module that I developed that will do what you
need. Send me your email address to my email address below and I will send
you a screen shot of what I have.

 

[Guest]

Yes but that still doesn't achieve what I want, I used Count as an example
sorry - I know you can use Min/Max etc but I don't want to evaluate text I
just want the value stored in a field displayed. If I use something similar
to your example I get a single record per specialist showing the first (or
last if Max) date
that the specialist carried out each activity. Whereas I want a record
displayed for every date showing what each specialist was doing that day as
per my illustrative example ie I want the Dates as column headings (showing
one weeks worth Mon - Frid) and the Specialist as Row Headings and the values
displayed being what activity they were doing (plus values from other fields
if they were on an audit).

Hope this makes sense more?
Thanks

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
--

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.

 

[Guest]

Thanks I know, we have emailed before. I want help/advice on how to do it
myself though so I can learn more... thanks anyway

 

[Guest]

sorry... just done some more work on this and it DOES achieve what I want
. The reason I thought it didn't was because the crosstab query was based
on another query (which had linked tables to produce the specialists name and
activity desc instead of the short codes/IDs). When I created a new crosstab
query from scratch based directly on the table it produced the results I
wanted using the Min function. Now I just have to make it display descriptive
text instead of primary key values eg audit instead of AU ...etc...
Thanks

Yes but that still doesn't achieve what I want, I used Count as an example
sorry - I know you can use Min/Max etc but I don't want to evaluate text I
just want the value stored in a field displayed. If I use something similar
to your example I get a single record per specialist showing the first (or
last if Max) date
that the specialist carried out each activity. Whereas I want a record
displayed for every date showing what each specialist was doing that day as
per my illustrative example ie I want the Dates as column headings (showing
one weeks worth Mon - Frid) and the Specialist as Row Headings and the values
displayed being what activity they were doing (plus values from other fields
if they were on an audit).

Hope this makes sense more?
Thanks

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
--

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
--


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

 

[Guest]

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
--

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.

 

[Duane Hookom]

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

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
--

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
--


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

 

[Guest]

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

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
--

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
--


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

 

[Duane Hookom]

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

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


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
--

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
--


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

 

[Guest]

I have defined [Sales - Y1] as a number (Long Integer) and it is right
aligned in the original source table. However, in the crosstab query for the
report, it is left aligned just like the other two variables.

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

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


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
--

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
--


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

 

[Guest]

Duane --

I tried it again and I got it to work this time. I am unsure of why the
initial idea didn't work -- I may have typed (and retyped) it incorrectly
last night.

I rearranged the order of the fields (for layout reasons only) and converted
the [Sales - Y1] to millions and placed characters in front and back.The
expression I used is

xpr1: First([Description] & Chr(13) & Chr(10) & [Project Name] & Chr(13) &
Chr(10) & "$ " & ([Sales - Y1]/1000000) & "MM")

And I get what I want. So, thanks your great.

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

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


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
--

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
--


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

 

[Guest]

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

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


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
--

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
--


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

 

[Duane Hookom]

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
--

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


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
--

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
--


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

 

[Guest]

I have looked at your crosstab and calendar reports and now at the
concatenate function. They help -- but then I need lots of help. Thanks and
I will try the concetanate functions.

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
--

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


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
--

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
--


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

 

[Guest]

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
--

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


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
--

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
--


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

 

[Duane Hookom]

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
--

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


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
--

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

 

[Guest]

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.

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
--

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


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
--

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