Multiple fields into one

Hello!

This is concatenating with a twist.

I have three tables:

Tag:
*tag

Doc_ref:
*DocID
*GenID

Document;
*DocID
Doc_Type

GenID linked to Tag with a one to many relationship
Document!DocID to Doc_ref!DocID with a one to many

Now, what I need is to get one field with all the DocID values with a certain
type for each tag

For Tag1 i need a field with "Doc1 - Doc2" where doc_type is "type1" and so
on.

How can this be done?
It is not very often I need this done, so it could be a maketable query or
code.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

I did not follow your process. Can you try explaining it another way?
Can you post sample data and example of how ypou would like the results to
look?
--
Build a little, test a little.


"AtleDreier via AccessMonster.com" wrote:

Hello!

This is concatenating with a twist.

I have three tables:

Tag:
*tag

Doc_ref:
*DocID
*GenID

Document;
*DocID
Doc_Type

GenID linked to Tag with a one to many relationship
Document!DocID to Doc_ref!DocID with a one to many

Now, what I need is to get one field with all the DocID values with a certain
type for each tag

For Tag1 i need a field with "Doc1 - Doc2" where doc_type is "type1" and so
on.

How can this be done?
It is not very often I need this done, so it could be a maketable query or
code.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

.

I've been pondering your request and could not come up with anything more
efficient than the following.

Step 1: Create a Saved Base Query with all the necessary information

SELECT Tag.Tag, Document.DocID, Doc_Type
FROM (Tag Inner Join Doc_Ref
ON Tag.Tag = Doc_Ref.GenID)
INNER JOIN Document
ON Doc_Ref.DocId = Document.DocID

Step2: Now using that with one of the VBA concatenation functions (see
references below) you can build a query like the following. I used Duane
Hookom's function.

SELECT DISTINCT Tag.Tag, Doc_Type
, Concatenate("Select DocId FROM qSavedQuery as Q WHERE Q.Doc_Type='" &
qSavedQuery.Doc_Type & "' AND Q.Tag ='" & qSavedQuery.Tag & "'", " : ") as TheList
FROM qSavedQuery

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AtleDreier via AccessMonster.com wrote:
Hello!

This is concatenating with a twist.

I have three tables:

Tag:
*tag

Doc_ref:
*DocID
*GenID

Document;
*DocID
Doc_Type

GenID linked to Tag with a one to many relationship
Document!DocID to Doc_ref!DocID with a one to many

Now, what I need is to get one field with all the DocID values with a certain
type for each tag

For Tag1 i need a field with "Doc1 - Doc2" where doc_type is "type1" and so
on.

How can this be done?
It is not very often I need this done, so it could be a maketable query or
code.

Thank you, I'm running the query now. Will report back, it's very slow! :-)

I've got around 12500 references, and I get about 5 references per second, so
I estimate around 40 minutes to run the query... :-)



John Spencer wrote:
I've been pondering your request and could not come up with anything more
efficient than the following.

Step 1: Create a Saved Base Query with all the necessary information

SELECT Tag.Tag, Document.DocID, Doc_Type
FROM (Tag Inner Join Doc_Ref
ON Tag.Tag = Doc_Ref.GenID)
INNER JOIN Document
ON Doc_Ref.DocId = Document.DocID

Step2: Now using that with one of the VBA concatenation functions (see
references below) you can build a query like the following. I used Duane
Hookom's function.

SELECT DISTINCT Tag.Tag, Doc_Type
, Concatenate("Select DocId FROM qSavedQuery as Q WHERE Q.Doc_Type='" &
qSavedQuery.Doc_Type & "' AND Q.Tag ='" & qSavedQuery.Tag & "'", " : ") as TheList
FROM qSavedQuery

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hello!

[quoted text clipped - 25 lines]
It is not very often I need this done, so it could be a maketable query or
code.

--
Message posted via http://www.accessmonster.com

It works, although it's pretty slow.
I'll try some different concatenate functions and see if there are faster
ones. It's not a big problem, it gives me an exuse to get coffee and a donut!
:-D

Thank you!


AtleDreier wrote:
Thank you, I'm running the query now. Will report back, it's very slow! :-)

I've got around 12500 references, and I get about 5 references per second, so
I estimate around 40 minutes to run the query... :-)

I've been pondering your request and could not come up with anything more
efficient than the following.
[quoted text clipped - 37 lines]
It is not very often I need this done, so it could be a maketable query or
code.

--
Message posted via http://www.accessmonster.com

Make sure you have indexes on the fields involved. For instance, the Doc_type
field should be indexed.

There may be other ways to increase the speed. For instance, try creating
another query (qTagDocType).

SELECT Distinct Tag.Tag, Doc_Type
FROM (Tag Inner Join Doc_Ref
ON Tag.Tag = Doc_Ref.GenID)
INNER JOIN Document
ON Doc_Ref.DocId = Document.DocID

And then change the final query to

SELECT qTagDocType.Tag, qTagDocType.Doc_Type
, Concatenate("Select DocId FROM qSavedQuery as Q WHERE Q.Doc_Type='" &
qTagDocType.Doc_Type & "' AND Q.Tag ='" & qTagDocType.Tag & "'", " : ") as TheList
FROM qTagDocType




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AtleDreier via AccessMonster.com wrote:
It works, although it's pretty slow.
I'll try some different concatenate functions and see if there are faster
ones. It's not a big problem, it gives me an exuse to get coffee and a donut!
:-D

Thank you!


AtleDreier wrote:
Thank you, I'm running the query now. Will report back, it's very slow! :-)

I've got around 12500 references, and I get about 5 references per second, so
I estimate around 40 minutes to run the query... :-)

I've been pondering your request and could not come up with anything more
efficient than the following.
[quoted text clipped - 37 lines]
It is not very often I need this done, so it could be a maketable query or
code.