CrossTab or PivotTable Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a problem creating a report that looks nice with pivot (or
crosstab) information. I have looked at the crosstab.mdb sample that Duane
has recomended to several people, which is a very elegant solution I must
say. However, I continue to have a little trouble. My trouble may really be
trouble with figuring out how to get a cross-tab query to give me what I am
looking for.

To date I have been calling a report with a form embedded in it. To see a
sample, look at: http://128.2.49.121/ptm/HeadGrid.pdf . I call it from a
print dialog form that allows me to specify many different filter oprions for
creating this report. Since the report is really just some header and footer
information with a pivot table form squeezed in between, I cannot do some of
the formatting things I would like to do to this printed table of data.

So I tried going the route of a report based on a crosstab query, but I run
into two problems with this:
1) Crosstab queries want to do some calculations (sum, count, etc) with
my value information. The Pivot table allows me to print multiple values
where the collumn and row data intersect.
2) I want to be able to have a heading that looks like a production
name in the sample, but I want them sorted by opening date as in the sample.
I can't seem to figure out how to do this in the crosstab query, since it
only allows one category as a Collumn Category option.

Any suggestions?
 
Duane,

Thanks for your thoughts here. I was thinking of runing a concatinating sub
query to achieve my results. I know I can use a character return in the
concatination to acheive the results in my pivot table sample. I resisited
goign this route, because I wouldn't be able to filter based on specific
individuals in the report table, but I guess I really shouldn't be worrying
about that anyway. I will give it a go.

I know I could also concatenate to acheive the coluumn heading with a
combined header. My problem here is that I would be concatenation a date
field to a text field, which
1) I can't really sort by date if it is all text the way that I want
to, and
2) it just doesn't look the way I want it to. (title being the more
important information here, so I would rather have that information first
with Date below it - though it is not this way in my pivot table sample)

Any further thoughts here?

Thanks again for your help.
 
I don't know where or why you need to sort by date. When creating crosstabs
with date or time headers, I always use 'relative' dates. For instance
rather than use [ClassDate] as a column heading, I would create a date text
box on a form and use it to compare to [ClassDate]:
ColHead:"D" & DateDiff("d", [ClassDate], Forms!frmRpt!txtEndDate)

There are a number of crosstab and calendar report samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
 
Duh. That makes sense. Don't know why I didn't think of it. Plus the
collumn heading names come from someplace else anyway, and I can have that
query name them correclty for my report. Phew!

Okay, now I am running aground on a more preliminary problem, namely that of
concatenation. I have been working with your basConcatenate module (slick by
the way). My problem is that I want to concatenate fiels in in table that
relate not directly to another, but rather through another to two more
tables. I am not sure if that made sense. I have a table of people which is
related to what I call a "linking table." This linking table is also related
to two other tables. One that contains information on a production that a
person could be working on, and one that contains a position that a person
might hold on that production. This creates a virtual many-to-many
relatinoship, which I need. What I am trying to find here is for a given
position on a production, what is the name of the person who holds that
position. In some cases it may be multiple people, hence the need to
concatenate. So my problem here is my lack of sophistication in dealing with
joins and writing queries.

My sql looks like this:
SELECT [Production Staffing].Position, Productions.Title,
Concatenate("SELECT [first] &chr(32)& [Last] FROM People WHERE [Id] =" &
[Production Staffing]!Person,Chr(10)) AS name
FROM Positions INNER JOIN (Productions INNER JOIN (People INNER JOIN
[Production Staffing] ON People.ID = [Production Staffing].Person) ON
Productions.ID = [Production Staffing].Production) ON Positions.ID =
[Production Staffing].Position;

this part:
FROM People WHERE [Id] =" & [Production Staffing]!Person

is clearly not giving me what I want here.

I tried this:
FROM People
WHERE ((([Production Staffing].Production) In (SELECT [Production] FROM
[Production Staffing] As Tmp GROUP BY [Production],[Position] HAVING
Count(*)>1 And [Position] = [Production Staffing].[Position])))

But that wasn't quite it either.

Got any suggestions there?

Thanks again.

David Holcomb

Duane Hookom said:
I don't know where or why you need to sort by date. When creating crosstabs
with date or time headers, I always use 'relative' dates. For instance
rather than use [ClassDate] as a column heading, I would create a date text
box on a form and use it to compare to [ClassDate]:
ColHead:"D" & DateDiff("d", [ClassDate], Forms!frmRpt!txtEndDate)

There are a number of crosstab and calendar report samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

--
Duane Hookom
MS Access MVP


David Holcomb said:
Duane,

Thanks for your thoughts here. I was thinking of runing a concatinating
sub
query to achieve my results. I know I can use a character return in the
concatination to acheive the results in my pivot table sample. I
resisited
goign this route, because I wouldn't be able to filter based on specific
individuals in the report table, but I guess I really shouldn't be
worrying
about that anyway. I will give it a go.

I know I could also concatenate to acheive the coluumn heading with a
combined header. My problem here is that I would be concatenation a date
field to a text field, which
1) I can't really sort by date if it is all text the way that I want
to, and
2) it just doesn't look the way I want it to. (title being the more
important information here, so I would rather have that information first
with Date below it - though it is not this way in my pivot table sample)

Any further thoughts here?

Thanks again for your help.
Click to expand...
Click to expand...
 
You can select from a query in the Concatenate() function. I think you are
looking for multiple people so you might want to leave the People table out
of the main table and send the Productions.ID in to the Concatenate()
function. I believe you want the function to return all of the peoples names
that are associated with a production.

--
Duane Hookom
MS Access MVP


David Holcomb said:
Duh. That makes sense. Don't know why I didn't think of it. Plus the
collumn heading names come from someplace else anyway, and I can have that
query name them correclty for my report. Phew!

Okay, now I am running aground on a more preliminary problem, namely that
of
concatenation. I have been working with your basConcatenate module (slick
by
the way). My problem is that I want to concatenate fiels in in table that
relate not directly to another, but rather through another to two more
tables. I am not sure if that made sense. I have a table of people which
is
related to what I call a "linking table." This linking table is also
related
to two other tables. One that contains information on a production that a
person could be working on, and one that contains a position that a person
might hold on that production. This creates a virtual many-to-many
relatinoship, which I need. What I am trying to find here is for a given
position on a production, what is the name of the person who holds that
position. In some cases it may be multiple people, hence the need to
concatenate. So my problem here is my lack of sophistication in dealing
with
joins and writing queries.

My sql looks like this:
SELECT [Production Staffing].Position, Productions.Title,
Concatenate("SELECT [first] &chr(32)& [Last] FROM People WHERE [Id] =" &
[Production Staffing]!Person,Chr(10)) AS name
FROM Positions INNER JOIN (Productions INNER JOIN (People INNER JOIN
[Production Staffing] ON People.ID = [Production Staffing].Person) ON
Productions.ID = [Production Staffing].Production) ON Positions.ID =
[Production Staffing].Position;

this part:
FROM People WHERE [Id] =" & [Production Staffing]!Person

is clearly not giving me what I want here.

I tried this:
FROM People
WHERE ((([Production Staffing].Production) In (SELECT [Production] FROM
[Production Staffing] As Tmp GROUP BY [Production],[Position] HAVING
Count(*)>1 And [Position] = [Production Staffing].[Position])))

But that wasn't quite it either.

Got any suggestions there?

Thanks again.

David Holcomb

Duane Hookom said:
I don't know where or why you need to sort by date. When creating
crosstabs
with date or time headers, I always use 'relative' dates. For instance
rather than use [ClassDate] as a column heading, I would create a date
text
box on a form and use it to compare to [ClassDate]:
ColHead:"D" & DateDiff("d", [ClassDate], Forms!frmRpt!txtEndDate)

There are a number of crosstab and calendar report samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

--
Duane Hookom
MS Access MVP


David Holcomb said:
Duane,

Thanks for your thoughts here. I was thinking of runing a
concatinating
sub
query to achieve my results. I know I can use a character return in
the
concatination to acheive the results in my pivot table sample. I
resisited
goign this route, because I wouldn't be able to filter based on
specific
individuals in the report table, but I guess I really shouldn't be
worrying
about that anyway. I will give it a go.

I know I could also concatenate to acheive the coluumn heading with a
combined header. My problem here is that I would be concatenation a
date
field to a text field, which
1) I can't really sort by date if it is all text the way that I
want
to, and
2) it just doesn't look the way I want it to. (title being the more
important information here, so I would rather have that information
first
with Date below it - though it is not this way in my pivot table
sample)

Any further thoughts here?

Thanks again for your help.

:

1) to get multiple values in a crosstab, you can use my generic
concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
can
try this function as your value with First.
2) I'm not sure exactly what you want but you can concatenate two
values/fields together to create a column heading value.


--
Duane Hookom
MS Access MVP


message
I am having a problem creating a report that looks nice with pivot
(or
crosstab) information. I have looked at the crosstab.mdb sample
that
Duane
has recomended to several people, which is a very elegant solution I
must
say. However, I continue to have a little trouble. My trouble may
really
be
trouble with figuring out how to get a cross-tab query to give me
what
I
am
looking for.

To date I have been calling a report with a form embedded in it. To
see a
sample, look at: http://128.2.49.121/ptm/HeadGrid.pdf . I call it
from a
print dialog form that allows me to specify many different filter
oprions
for
creating this report. Since the report is really just some header
and
footer
information with a pivot table form squeezed in between, I cannot do
some
of
the formatting things I would like to do to this printed table of
data.

So I tried going the route of a report based on a crosstab query,
but I
run
into two problems with this:
1) Crosstab queries want to do some calculations (sum, count,
etc)
with
my value information. The Pivot table allows me to print multiple
values
where the collumn and row data intersect.
2) I want to be able to have a heading that looks like a
production
name in the sample, but I want them sorted by opening date as in the
sample.
I can't seem to figure out how to do this in the crosstab query,
since
it
only allows one category as a Collumn Category option.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top