Prev: Microsoft Responds to the Evolution of Online Communities
Next: How to Analyze Attributes in a Query
From: John Spencer on 10 May 2010 08:45
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.
>
First  |  Prev  | 
Pages: 1 2
Prev: Microsoft Responds to the Evolution of Online Communities
Next: How to Analyze Attributes in a Query