Report with Crosstab query

  • Thread starter Thread starter Matthew James
  • Start date Start date
M

Matthew James

I have created a report using a crosstab query. The
report has time periods grouped down the page and days of
the week across the top of the report. Name and phone
numbers of individuals are listed under the respective
day. The problem is that the report creates blank spaces
and forces eg: Sundays details down 1 row to accomodate
the information for Monday. Is there any way I can get
the areas to fill without taking up all this extra space
on printing
Thank you for any help
 
I am having trouble picturing your results. Is there any way that you could
type in some sample records to show us how this is displaying and then how
you would like it to display?
 
Thanks for your response. The first grouping list down
the left and examples as follows:
Sunday Monday etc
1am to 2am Bill Smith
John James
Jill Jones
Ken Johns
Brian Kenneths
2am to 3am Phil Smithe

When i run the crosstab query, the infomation shows in
seperate rows and not side by side under the day. This
appears the reason why the report has the blanks. But I
do not know the solution to remove the blanks.
My crosstab query looks like this:

TRANSFORM Trim([firstname] & " " & [Surname] & " " &
[Home Phone] & " " & [Captain]) AS Expr1
SELECT [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain,
[Main Table].[Day of Adoration]
FROM [Main Table]
WHERE ((([Main Table].[Time of Adoration])="(a) Midnight
to 1 am" Or ([Main Table].[Time of Adoration])="(b) 1 am
to 2 am") AND (([Main Table].[Day of Adoration])="(1)
Sunday" Or ([Main Table].[Day of Adoration])="(2)
Monday"))
GROUP BY [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain,
[Main Table].[Day of Adoration]
PIVOT [Main Table].[Day of Adoration] In ("(1)
Sunday","(2) Monday");
Any help thanks.
 
You could possibly use the generic concatenate function to combine the Names
into a single expression for each date and time increment. The function can
be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

There are some crosstab reports and calendar reports that might help at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP


Thanks for your response. The first grouping list down
the left and examples as follows:
Sunday Monday etc
1am to 2am Bill Smith
John James
Jill Jones
Ken Johns
Brian Kenneths
2am to 3am Phil Smithe

When i run the crosstab query, the infomation shows in
seperate rows and not side by side under the day. This
appears the reason why the report has the blanks. But I
do not know the solution to remove the blanks.
My crosstab query looks like this:

TRANSFORM Trim([firstname] & " " & [Surname] & " " &
[Home Phone] & " " & [Captain]) AS Expr1
SELECT [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain,
[Main Table].[Day of Adoration]
FROM [Main Table]
WHERE ((([Main Table].[Time of Adoration])="(a) Midnight
to 1 am" Or ([Main Table].[Time of Adoration])="(b) 1 am
to 2 am") AND (([Main Table].[Day of Adoration])="(1)
Sunday" Or ([Main Table].[Day of Adoration])="(2)
Monday"))
GROUP BY [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain,
[Main Table].[Day of Adoration]
PIVOT [Main Table].[Day of Adoration] In ("(1)
Sunday","(2) Monday");
Any help thanks.
-----Original Message-----
I am having trouble picturing your results. Is there any way that you could
type in some sample records to show us how this is displaying and then how
you would like it to display?

--
Duane Hookom
MS Access MVP
--




.
Click to expand...
Click to expand...
 
Back
Top