Crosstab voes

[atledreier]

Hello!

I have to present some data in a certain way, and I can't figure out
how. I hope you can help me with this.

I have data for tags, documents and document references, like this:

tblTag:
Tagno (key)
Description
Area
....

tblDocument:
DocID (key)
Description
Category
Area

tblDoc_Ref:
DocID (key, linked to tblDocument.DocID, 1 to m)
GenID (key, linked to tblTag.tagno, 1 to m)
Type

I have a crosstab that display a datasheet view with TagNo as row
heading and document categories as column headings. The problem is
that I can have several documents in each document category per tag,
but the crosstab will only display the first, the last or a count.

How can I get a nice list of documents in each category on a per-tag
basis? I would like to keep the crosstab look, and have one row for
each tag, but several documents grouped by category.

Any tips?

 

[vanderghast]

Rank the document (their name, or otherwise) by document category, by tag
number, and GROUP on that rank (no need to SHOW the rank, though).


To rank the document by category by genID:


SELECT a.docID, a.genID, a.category, COUNT(*) as rank
FROM query AS a INNER JOIN query As b
ON a.genID = b.genID AND a.category=b.category AND a.docID <= b.docID
GROUP BY a.docID, a.genID, a.category




where "query" is a standard saved query getting the docID, category and its
associated genID (tagno, as I understand). You can use >= instead fo the
proposed <=. Save this query, supplying the rank, as qrank (for
illustration).



Finally, make the crostab on the query with rank, qrank:


TRANSFORM LAST(docID)
SELECT category, rank
FROM qrank
GROUP BY category, rank
PIVOT genID


(or you can try to remove rank in the SELECT clause, if you don't want to
see the "line number" for each category)




Vanderghast, Access MVP

 

[KARL DEWEY]

Try this --
TRANSFORM First(tblDocument.Description) AS FirstOfDescription
SELECT tblTag.Tagno, tblTag.Description, tblDocument.Category
FROM tblTag INNER JOIN (tblDoc_Ref INNER JOIN tblDocument ON
tblDoc_Ref.DocID = tblDocument.DocID) ON tblTag.Tagno = tblDoc_Ref.GenID
GROUP BY tblTag.Tagno, tblTag.Description, tblDocument.DocID,
tblDocument.Category
PIVOT tblDocument.Category;

 

[atledreier]

Try this --
TRANSFORM First(tblDocument.Description) AS FirstOfDescription
SELECT tblTag.Tagno, tblTag.Description, tblDocument.Category
FROM tblTag INNER JOIN (tblDoc_Ref INNER JOIN tblDocument ON
tblDoc_Ref.DocID = tblDocument.DocID) ON tblTag.Tagno = tblDoc_Ref.GenID
GROUP BY tblTag.Tagno, tblTag.Description, tblDocument.DocID,
tblDocument.Category
PIVOT tblDocument.Category;

Vanderghast, you suggestion returned an error, 'too many crosstab
column headers (3118)'

The actual SQL queries I've used (The names differ slightly from my
example, these are my actual names):


qryDoc
SELECT Doc_ref.Docid, Doc_ref.Genid_1, Document.Doc_type
FROM Tag INNER JOIN (Document INNER JOIN Doc_ref ON Document.Docid =
Doc_ref.Docid) ON Tag.Tag = Doc_ref.Genid_1;

qryRank
SELECT a.Docid, a.Genid_1, a.Doc_type, Count(*) AS rank
FROM qryDoc AS a INNER JOIN qryDoc AS b ON (a.Docid <= b.Docid) AND
(a.Doc_type = b.Doc_type) AND (a.Genid_1 = b.Genid_1)
GROUP BY a.Docid, a.Genid_1, a.Doc_type;

qryCrosstab:
TRANSFORM Last(qryRank.docID) AS LastOfdocID
SELECT qryRank.doc_type, qryRank.rank
FROM qryRank
GROUP BY qryRank.doc_type, qryRank.rank
PIVOT qryRank.genID_1;


Karl Dewey:
Your suggestion didn't work as intended, I may have done a small error
in correctiong the names. I'll try it again.

 

[vanderghast]

If the PIVOT expression generates too much different values, you may get
that kind of error. You still have the limit of 255 columns for a query (or
for a table). So you may have too many different categories. Maybe you can
try to limit the categories with a where clause (after the FROM and before
the GROUP BY), to, say:

WHERE Category <= "M"


and, for a second crosstab:


WHERE Category > "M"


and if that still produces too many columns, restrict further more the
Categories:


WHERE Category IN( "alpha", "beta", "gamma" )

or, in this particular case of a list:


PIVOT Category IN( "alpha", "beta", "gamma" )


which will produce those new columns (even if there is not single record
having one of these categories), but only these.



Vanderghast, Access MVP

Try this --
TRANSFORM First(tblDocument.Description) AS FirstOfDescription
SELECT tblTag.Tagno, tblTag.Description, tblDocument.Category
FROM tblTag INNER JOIN (tblDoc_Ref INNER JOIN tblDocument ON
tblDoc_Ref.DocID = tblDocument.DocID) ON tblTag.Tagno = tblDoc_Ref.GenID
GROUP BY tblTag.Tagno, tblTag.Description, tblDocument.DocID,
tblDocument.Category
PIVOT tblDocument.Category;

Vanderghast, you suggestion returned an error, 'too many crosstab
column headers (3118)'

The actual SQL queries I've used (The names differ slightly from my
example, these are my actual names):


qryDoc
SELECT Doc_ref.Docid, Doc_ref.Genid_1, Document.Doc_type
FROM Tag INNER JOIN (Document INNER JOIN Doc_ref ON Document.Docid =
Doc_ref.Docid) ON Tag.Tag = Doc_ref.Genid_1;

qryRank
SELECT a.Docid, a.Genid_1, a.Doc_type, Count(*) AS rank
FROM qryDoc AS a INNER JOIN qryDoc AS b ON (a.Docid <= b.Docid) AND
(a.Doc_type = b.Doc_type) AND (a.Genid_1 = b.Genid_1)
GROUP BY a.Docid, a.Genid_1, a.Doc_type;

qryCrosstab:
TRANSFORM Last(qryRank.docID) AS LastOfdocID
SELECT qryRank.doc_type, qryRank.rank
FROM qryRank
GROUP BY qryRank.doc_type, qryRank.rank
PIVOT qryRank.genID_1;


Karl Dewey:
Your suggestion didn't work as intended, I may have done a small error
in correctiong the names. I'll try it again.

 

[atledreier]

Thank you, but this is not the case. I have 4 categories of document,
so something else must be evading me. SQL is still pretty new to me,
so I have a hard time spotting logic errors. It's like it wants to put
each tag in a column header. I have 8 possible Doc_types, of which 4
is currently used.