one field into several

[atledreier]

Hello!

I need a solution to a query.

I have three relevant tables

tblTag:
Tag - key
desc
....
....

tblDoc
DocID - key
Category
descr
....
....

tblDocRef
DocID - key
Tag -key
category
....
....

Ok, I have my data structured like this:
My tblTag contain information about equipment, with Tag field as the
primary key. My tblDoc has information about the documentation for the
equipment, and what category the document belongs to.
The tblDocRef is a table that link the document to the tag. I have
several tags per document, but only one documetn in each category for
each tag.

What I need to do is have a query that return all tags, with one field
for each document category.

Example:
I have Tag1 and Tag2
I have Doc1 - category1, Doc2 - category 2 and Doc3 - category 1

So my tables would look like:

tblTag
Tag1 - description - more fields.....

tblDoc
Doc1 - cat1 - more fields....
Doc2 - cat2 - more fields
Doc3 - cat3 - more fields.....

tblDocRef
Doc1 -Tag1
Doc1 - Tag2
Doc2 - Tag1
Doc3 - Tag1


I would like a query that return fields like this:

Tag
doccat1
doccat2
doccat3

And return all tag fields even if they are filled in or not.

So for the above example, the result would be:

Tag - cat1 - cat2 - cat3
-----------------------------------------------------
Tag1 - Doc1 - Doc 2 . doc3
tag2 - doc1


Does that make sense at all?

 

[Gary Walter]

I need a solution to a query.

I have three relevant tables

tblTag:
Tag - key
desc
...
...

tblDoc
DocID - key
Category
descr
...
...

tblDocRef
DocID - key
Tag -key
category
...
...

Ok, I have my data structured like this:
My tblTag contain information about equipment, with Tag field as the
primary key. My tblDoc has information about the documentation for the
equipment, and what category the document belongs to.
The tblDocRef is a table that link the document to the tag. I have
several tags per document, but only one documetn in each category for
each tag.

What I need to do is have a query that return all tags, with one field
for each document category.

Example:
I have Tag1 and Tag2
I have Doc1 - category1, Doc2 - category 2 and Doc3 - category 1

So my tables would look like:

tblTag
Tag1 - description - more fields.....

tblDoc
Doc1 - cat1 - more fields....
Doc2 - cat2 - more fields
Doc3 - cat3 - more fields.....

tblDocRef
Doc1 -Tag1
Doc1 - Tag2
Doc2 - Tag1
Doc3 - Tag1


I would like a query that return fields like this:

Tag
doccat1
doccat2
doccat3

And return all tag fields even if they are filled in or not.

So for the above example, the result would be:

Tag - cat1 - cat2 - cat3
-----------------------------------------------------
Tag1 - Doc1 - Doc 2 . doc3
tag2 - doc1


Does that make sense at all?

hi atledreier,

sounds like want a simple crosstab..

TRANSFORM Min(D.DESCR)
SELECT
T.DESC
FROM
(tblTag AS T
LEFT JOIN
tblDocRef AS DR
ON
T.Tag = DR.Tag)
LEFT JOIN
tblDoc AS D
ON
DR.DocID = D.DocID
GROUP BY T.DESC
PIVOT D.Category;

good luck,

gary