If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Multiple fields into one
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 . |
#3
|
|||
|
|||
Multiple fields into one
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. |
#4
|
|||
|
|||
Multiple fields into one
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 |
#5
|
|||
|
|||
Multiple fields into one
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 |
#6
|
|||
|
|||
Multiple fields into one
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. |
Thread Tools | |
Display Modes | |
|
|